On Tue, 2020-04-21 at 12:10 -0400, Yannick Collette wrote:
> I'm having an issue with Insert statement (through oracle_fdw) that hangs after checkpoint starting is "time" instead
of"xlog". see end of log below.
> Migrating data from Oracle (Windows 2008 R2) to Postgresql (Windows 2012 R2). Both systems are virtual machines
residingon same host.
> Over 200 migrations have been done without any issues. Trying to understand what's happening here...
>
> 2020-04-20 14:02:36.103 EDT,5e9dbe0e.990,126,,,,00000,LOG: checkpoint complete: wrote 112333 buffers (28.6%); 0
trans
> 2020-04-20 14:07:37.793 EDT,5e9dbe0e.990,131,,,,00000,LOG: checkpoint starting: xlog
> 2020-04-20 14:09:50.222 EDT,5e9dbe0e.990,132,,,,00000,LOG: checkpoint complete: wrote 112903 buffers (28.7%); 0
transactionlog file(s) added, 0 removed, 44 recycled; write=132.240 s, sync=0.078 s,
> total=132.428 s; sync files=8, longest=0.034 s, average=0.010 s; distance=720594 kB, estimate=722102 kB
> 2020-04-20 14:10:03.574 EDT,5e9dbe0e.990,133,,,,00000,LOG: checkpoint starting: xlog
> 2020-04-20 14:19:03.160 EDT,5e9dbe0e.990,134,,,,00000,LOG: checkpoint complete: wrote 113066 buffers (28.8%); 0
transactionlog file(s) added, 0 removed, 44 recycled; write=539.492 s, sync=0.000 s,
> total=539.586 s; sync files=8, longest=0.004 s, average=0.001 s; distance=721542 kB, estimate=722046 kB
> 2020-04-20 14:20:03.172 EDT,5e9dbe0e.990,135,,,,00000,LOG: checkpoint starting: time
> 2020-04-20 14:29:03.255 EDT,5e9dbe0e.990,136,,,,00000,LOG: checkpoint complete: wrote 97752 buffers (24.9%); 0
transactionlog file(s) added, 0 removed, 44 recycled; write=539.911 s, sync=0.000 s,
> total=540.083 s; sync files=7, longest=0.004 s, average=0.001 s; distance=623883 kB, estimate=712230 kB
>
> The transaction still active in pg_stat_activity but not writing on disk since last checkpoint complete. Non errors
afterlast checkpoint. Also no errors in logs on Oracle side.
> Any help or hints would be greatly appreciated.
How does pg_stat_activity look? Is there an entry with "granted = FALSE" in pg_locks?
What is the PostgreSQL query and the Oracle query?
What is the state of the statement in Oracle? Is it running, blocked or waiting for the client?
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com