Message-ID: <8be53622f7c7d152d550eed59ccc065421df8a0e.camel@cybertec.at>
Subject: Re: checkpoint starting: time (insert statement hangs
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Yannick Collette <yannickcollette@gmail.com>,
	pgsql-admin@lists.postgresql.org
Date: Tue, 21 Apr 2020 21:39:05 +0200
In-Reply-To: 
 <CADTm7M97VqCXhB5Ku+OPGH=yVe6F7J0m4DPW_nHSiDUdwN0qWg@mail.gmail.com>
References: 
	<CADTm7M97VqCXhB5Ku+OPGH=yVe6F7J0m4DPW_nHSiDUdwN0qWg@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
User-Agent: Evolution 3.32.5 (3.32.5-1.fc30) 
MIME-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

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