Content-Type: text/plain;
	charset=us-ascii
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51\))
Subject: Re: PostgreSQL and a Catch-22 Issue related to dead rows
From: Christophe Pettus <xof@thebuild.com>
In-Reply-To: 
 <AM7P189MB10281DCA1FD196A5610484B29D3C2@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
Date: Mon, 9 Dec 2024 06:32:59 -0800
Cc: "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <5EE4CC0C-E624-4E82-A2E8-B2EBA44D3521@thebuild.com>
References: 
 <AM7P189MB10281DCA1FD196A5610484B29D3C2@AM7P189MB1028.EURP189.PROD.OUTLOOK.COM>
To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
Archived-At: 
 <https://www.postgresql.org/message-id/5EE4CC0C-E624-4E82-A2E8-B2EBA44D3521%40thebuild.com>
Precedence: bulk


> On Dec 9, 2024, at 03:02, Lars Aksel Opsahl <Lars.Opsahl@nibio.no> =
wrote:
> If there were a way to remove dead rows without requiring a commit =
from totally unrelated jobs, it would be much easier.

Without seeing into the future, PostgreSQL doesn't know if a particular =
open transaction is "totally unrelated" to any other open transaction.  =
Any open transaction can potentially see rows that have been deleted or =
updated since it began, and without knowing what operations are coming, =
it doesn't know if it is safe to remove them.

(Strictly speaking, the rows you are describing are not "dead," in that =
they are still visible to some transaction.)