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>
Cc: 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 06:36:55
Message-ID: YpW3h6byY004R3el@c720-r368166
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

El día miércoles, mayo 25, 2022 a las 10:38:24a. m. -0400, Tom Lane escribió:

> Ravi Krishna <srkrishna(at)vivaldi(dot)net> writes:
> >> No. PostgreSQL may remove a dead row, but a dead row is by definition
> >> no longer visible, so it wouldn't be found by a query.
> > I am wondering whether it is a good practice to use CTID in a where
> > clause.
> It's fine if part of your business logic is that you don't want to allow
> concurrent updates. In this case, the OP seems to want to prevent rather
> than tolerate the concurrent update, so I don't think he needs to revisit
> the app's use of CTID.
> If you do need to support concurrent updates, then yeah relying on CTID
> is likely to be problematic.

Tom, we detected another issue of missing a row by its CTID in another table
where fees are stored which one(!) process cumulates in the night. The time
window between creating the CURSOR and missing the CTID is only 42
seconds and I can not imagine that any other concurrent process is updating
such fee rows at midnight. Could exist any other reason why a row changes
its CTID? Full VACUUM is not used either.


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 Shaheed Haque 2022-05-31 09:16:51 Re: Is it possible to index "deep" into a JSONB column?
Previous Message Michael Paquier 2022-05-31 04:30:11 Re: Extension pg_trgm, permissions and pg_dump order