Thread

Re: SQL:2011 Application Time Update & Delete

Paul A Jungwirth <pj@illuminatedcomputing.com> — 2025-11-05T16:04:21Z

On Tue, Nov 4, 2025 at 11:12 AM Paul A Jungwirth
<pj@illuminatedcomputing.com> wrote:
> Back to Postgres, you can get "desired" results IN READ COMMITTED by
> explicitly locking rows (with SELECT FOR UPDATE) just before
> updating/deleting them. Since you acquire the lock before the
> update/delete starts, there can be no new leftovers created within
> that span of history, and the update/delete sees everything that is
> there.

I forgot to mention: possibly we'll want to use this approach for
{CASCADE,SET {NULL,DEFAULT}} foreign keys (if the transaction is READ
COMMITTED). I'll explore that more and add it to the patch in this
series if it seems necessary. Also I didn't consider whether the
regular DML's lock could be weaker, like just KEY SHARE.

Yours,

-- 
Paul              ~{:-)
pj@illuminatedcomputing.com