pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Keyerror Smart <smartkeyerror@gmail.com>
Cc: Julien Rouhaud <rjuju123@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17812: LOCK TABLE IN ACCESS EXCLUSIVE MODE with a view returns an empty tuple set
Date: Tue, 28 Feb 2023 23:13:17 -0500
Message-ID: <1311639.1677643997@sss.pgh.pa.us> (raw)
In-Reply-To: <CAKFQuwb5U84yZGRRiGsRtLm3D3_jtc=TtXE=7y-z0ORg+Jy1bQ@mail.gmail.com>
References: <17812-206eaec1d133a94a@postgresql.org>
	<20230228135252.agaj42dik5qfjjxi@jrouhaud>
	<CAD=-kXaPVQtO3ivEY-Wvz4BkFCntnshPfcV2DCG7ujqwCNF3pA@mail.gmail.com>
	<20230301025229.rxmmjxfvg45w2cj4@jrouhaud>
	<CAD=-kXb1UUGqrHjVs47m6sS=tD0VQz3y=1y6d7nfTWYckNDrsw@mail.gmail.com>
	<CAKFQuwb5U84yZGRRiGsRtLm3D3_jtc=TtXE=7y-z0ORg+Jy1bQ@mail.gmail.com>

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> You may find the following to be informative as well.

A general comment on these examples is that we don't intend MVCC
guarantees to hold with respect to tables that are created/dropped
by other sessions midway through your transaction.  The reason can
be understood from this example:

Session 1:
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM table1;  -- establishes session1's snapshot

Session 2:
DROP TABLE table2;
CREATE TABLE table2 ...

Session 1:
SELECT * FROM table2;

Strict MVCC semantics would require that session 1 now see
the old contents of table2, which implies basically that we can't
ever drop tables --- at the point of the DROP, there was absolutely
nothing indicating that session 1 would take any interest in table2,
but after the fact it wants to know about it.  That's not how it
works; if DROP is able to obtain exclusive lock on table2, that
table is toast, immediately.

(I think that if you use SERIALIZABLE level, you might get a serialization
failure from the "SELECT * FROM table2", or if you don't then perhaps
it'd be reasonable to fix it so you do.  But under no circumstances
are we going to sit on the data in table2 on the off chance that
some transaction might want it later.)

Like Julien, I'm not entirely sure how well this is documented.
There is plenty of detail in [1] though.

			regards, tom lane

[1] https://www.postgresql.org/docs/current/mvcc.html





view thread (13+ messages)  latest in thread

Message-ID: <1311639.1677643997@sss.pgh.pa.us>
Permalink:  ../1311639.1677643997@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/1311639.1677643997@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, david.g.johnston@gmail.com, smartkeyerror@gmail.com, rjuju123@gmail.com
  Subject: Re: BUG #17812: LOCK TABLE IN ACCESS EXCLUSIVE MODE with a view returns an empty tuple set
  In-Reply-To: <1311639.1677643997@sss.pgh.pa.us>
  References: <17812-206eaec1d133a94a@postgresql.org> <20230228135252.agaj42dik5qfjjxi@jrouhaud> <CAD=-kXaPVQtO3ivEY-Wvz4BkFCntnshPfcV2DCG7ujqwCNF3pA@mail.gmail.com> <20230301025229.rxmmjxfvg45w2cj4@jrouhaud> <CAD=-kXb1UUGqrHjVs47m6sS=tD0VQz3y=1y6d7nfTWYckNDrsw@mail.gmail.com> <CAKFQuwb5U84yZGRRiGsRtLm3D3_jtc=TtXE=7y-z0ORg+Jy1bQ@mail.gmail.com> <1311639.1677643997@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox