Re: existing row not found by SELECT ... WHERE CTID = ?

From: Matthias Apitz <guru(at)unixarea(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Ravi Krishna <srkrishna(at)vivaldi(dot)net>, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: existing row not found by SELECT ... WHERE CTID = ?
Date: 2022-05-31 18:03:25
Message-ID: YpZYbcZCkKvbXRuU@c720-r368166
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

Analizing our logs in more detail, we got to know that there are cases
where, after updating a row with something like:

d01gsi =:d01gsi,
d01ex =:d01ex,
d01vldate =:d01vldate
WHERE CURRENT OF hc_d01buch;

we look-up the same row later again with its old CTID in :rowid; one solution
of it could be right after the UPDATE look-up the new CTID with:

EXEC SQL SELECT ctid INTO :rowid FROM d01buch WHERE CURRENT OF hc_d01buch;

i.e. using the same CURSOR which was used for the UPDATE again for a
SELECT for the CTID. Can this work?

Matthias Apitz, ✉ guru(at)unixarea(dot)de, +49-176-38902045
Public GnuPG key:

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tim Kelly 2022-05-31 18:22:32 unoptimized nested loops
Previous Message Jeff Ross 2022-05-31 17:46:52 Logically replicated table has no visible rows