Re: existing row not found by SELECT ... WHERE CTID = ?
El día Wednesday, May 25, 2022 a las 11:21:44AM +0200, Matthias Apitz escribió: > El día martes, mayo 24, 2022 a las 12:11:49 -0400, Tom Lane escribió: > > > Laurenz Albe writes: > > > It may well be that somebody deleted or updated a few rows between the > > > time > > > the cursor was materialized and the time the 5th row was fetched. > > > > Even without HOLD, a cursor will return a view of the data as it stood > > when the cursor was opened, just as a plain SELECT does. There is > > *plenty* of time for another session to get in there if you've been > > groveling through 50K records one at a time. > > Tom, Thanks for pointing us in the right direction where to look for a > solution. The CURSOR was opened around 23:11 pm and the CTID not found > at 23:21 pm, i.e. ten minutes later. This piece of software does every > night some housekeeping work in the circulation area of our LMS (Library > Management System) and is meant to run as a standalone job (only one > process after the other). We're trying to figure out with the customer if > something > else was started/running at this time between 23:11 and 23:21, to shut this > off in the future. ... Is there any way to get with the old CTID to the row, for example with the old CTID to the new one which the row now has after the update of the row? matthias -- Matthias Apitz, ✉ g...@unixarea.de, http://www.unixarea.de/ +49-176-38902045 Public GnuPG key: http://www.unixarea.de/key.pub
Re: existing row not found by SELECT ... WHERE CTID = ?
On Thursday, May 26, 2022, Matthias Apitz wrote: > > Is there any way to get with the old CTID to the row, for example with > the old CTID to the new one which the row now has after the update of the > row? > > No, there is no link between old and new in the main table. David J.
Re: existing row not found by SELECT ... WHERE CTID = ?
"David G. Johnston" writes: > On Thursday, May 26, 2022, Matthias Apitz wrote: >> Is there any way to get with the old CTID to the row, for example with >> the old CTID to the new one which the row now has after the update of the >> row? > No, there is no link between old and new in the main table. There is a forward link from the old CTID to the new, but we don't provide any user-accessible way to use it. I wonder though if the OP should be considering using SELECT FOR UPDATE in his cursor, so that it'd automatically chain up to the newest row version. regards, tom lane
Re: pg_create_logical_replication_slot in DB1 is blocked by a session in DB2
At Wed, 25 May 2022 11:01:43 -0400, Fred Habash wrote in
> I'm running this command while connected to pg cluster DB1:
>
> SELECT * FROM pg_create_logical_replication_slot('test_slot_99',
> 'test_decoding');
>
> When I examine pg_locks, I see the session is waiting on virtualxid and
> blocked and blocking sessions are on two different DBs.
>
> After doing some research, it looks like locks across DB can happen in
> postgres if the session queries rely on 'shared tables'. Not sure if this
> applies here.
>
> How can this be explained?
The "blocked_pid" you showed is of PID=14305 but the reportedly
"blocked" session is of PID=13405. So the 8602 doesn't seem to be
involved the "trouble". You might need to reinspect the situation.
> This is the session issuing the create slot command
>
> datid|datname |pid |leader_pid|usesysid|usename
> |application_name|client_addr |client_hostname|client_port|backend_start
> |xact_start |query_start|state_change
> |wait_event_type|wait_event|state |backend_xid|backend_xmin|query
>
>
>
> |backend_type |
> -|-|-|--||-|||---|---|---|---|---|---|---|--|--|---||---|--|
> 16408|db1 |13405| | 16394|test99 |
> |xx.xxx.xxx.x| | 53398|2022-05-25 09:12:41|2022-05-25
> 09:12:42|2022-05-25 09:12:42|2022-05-25 09:12:42|Lock
> |virtualxid|active| |171577399 |BEGIN;declare
> "SQL_CUR0x14680c0bace0" cursor with hold for SELECT lsn FROM
> pg_create_logical_replication_slot('qitx6iolfhy5zfkl_00016408_66eb6ba3_1fe1_4ccd_95ed_fd3d2d5d4ad8',
> 'test_decoding');fetch 1 in "SQL_CUR0x14680c0bace0"|client backend|
>
>
> Session above is blocked by pid 8602
>
> blocked_pid|blocked_user|blocking_pid|blocking_user |blocked_statement
>
>
>
> |current_statement_in_blocking_process
>
>
> |
> ---|||---|---|---|
> 14305|pq_devops |8602|service_con|BEGIN;declare
> "SQL_CUR0x1464680d6a60" cursor with hold for SELECT lsn FROM
> pg_create_logical_replication_slot('4iipu5a2hnuyfp3u_00016408_036cac77_3854_4320_b329_e7209b4cccf9',
> 'test_decoding');fetch 1 in "SQL_CUR0x1464680d6a60"|¶ SELECT **
> |
>
>
> The blocked and blocking sessions are on two different DBs
>
> datid|datname |pid |leader_pid|usesysid|usename|application_name
> |cl
> -|-|-|--||---|--|--
> 16408|db1 |13405| | 16394|test99 |
> |10
> 16407|db2 | 8602| |29429933|service_con_9 |PostgreSQL JDBC
> Driver|10
>
> Thank you
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
