From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Yannick Collette <yannickcollette@gmail.com>
To: pgsql-admin@lists.postgresql.org
Subject: Re: checkpoint starting: time (insert statement hangs
Date: Tue, 21 Apr 2020 21:39:05 +0200
Message-ID: <8be53622f7c7d152d550eed59ccc065421df8a0e.camel@cybertec.at> (raw)
In-Reply-To: <CADTm7M97VqCXhB5Ku+OPGH=yVe6F7J0m4DPW_nHSiDUdwN0qWg@mail.gmail.com>
References: <CADTm7M97VqCXhB5Ku+OPGH=yVe6F7J0m4DPW_nHSiDUdwN0qWg@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: laurenz.albe@cybertec.at, yannickcollette@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: checkpoint starting: time (insert statement hangs
In-Reply-To: <8be53622f7c7d152d550eed59ccc065421df8a0e.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox