Re: checkpoint starting: time (insert statement hangs

On Tue, 21 Apr 2020 at 15:39, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> 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 residing on 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 transaction log 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 transaction log 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 transaction log 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 after last 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
>
>
Transaction active in pg_stat_activity. Nothing in pg_locks.
Transaction was killed and relaunched. Will check statement in Oracle DB
next time around to see if anything was blocking tx.

Thanks for your help.
Yannick