Re: BUG #17368: Assert failed in GetSafeSnapshot() for SERIALIZABLE READ ONLY DEFERRABLE transaction

On Mon, Mar 6, 2023 at 4:02 PM Thomas Munro <thomas(dot)munro(at)gmail(dot)com> wrote:
> Duh, it's this same ancient bug that we already figured out, which
> dates from 2011. I plan to do some more testing and then proceed with
> the idea mentioned above[1].

Scratch that. I like the more ambitious idea better. I originally
thought it would be less back-patchable, but that's nonsense -- this
was a straight up thinko in ancient commit bdaabb9b and we should just
fix it properly. That is, we should treat this case as a
generalisation of the the PredXact->WritableSxactCount == 0 case, it's
just that we had to do more work to figure that out. Here's a patch
like that.

As mentioned, this approach allows for more improvements in later
patches. There is absolutely no reason for transactions in separate
databases to be in each others' possibleUnsafeConflicts lists. At
least, as long as PostgreSQL maintains its strict
no-cross-database-access policy, which I don't see changing any time
soon. So here is a draft patch like that, for the next commitfest.
It's a little easier to exercise than the "everything-is-doomed" case.
When I have more time I'll try to make proper tests, but basically you
just need two databases:

s1: \c db1
s1: CREATE TABLE t (i int);
s1: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
s1: INSERT INTO t VALUES (42);

s2: \c db2
s2: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
s2: SELECT * FROM x;

s2 shouldn't have to wait, and doesn't with this patch. But it does
if it's in db1 instead.