Re: [Xen-devel] [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more messages]

[Robert Haas <robertmhaas@xxxxxxxxx>]

On Wed, Dec 14, 2016 at 9:05 AM, Alvaro Herrera
<alvherre@xxxxxxxxxxxxxxx> wrote:
> Robert Haas wrote:
>> I have not read any database literature on the interaction of
>> serializability with subtransactions.  This seems very thorny.
>> Suppose T1 reads A and B and updates A -> A' while concurrently T2
>> reads A and B and updates B -> B'.  This is obviously not
>> serializable; if either transaction had executed before the other in a
>> serial schedule, the second transaction in the schedule would have had
>> to have seen (A, B') or (A', B) rather than (A, B), but that's not
>> what happened.  But what if each of T1 and T2 did the reads in a
>> subtransaction, rolled it back, and then did the write in the main
>> transaction and committed?  The database system has two options.
>> First, it could assume that the toplevel transaction may have relied
>> on the results of the aborted subtransaction.  But if it does that,
>> then any serialization failure which afflicts a subtransaction must
>> necessarily also kill the main transaction, which seems pedantic and
>> unhelpful.  If you'd wanted the toplevel transaction to be killled,
>> you wouldn't have used a subtransaction, right?  Second, it could
>> assume that the toplevel transaction in no way relied on or used the
>> values obtained from the aborted subtransaction.  However, that
>> defeats the whole purpose of having subtransactions in the first
>> place.  What's the point of being able to start subtransactions if you
>> can't roll them back and then decide to do something else instead?  It
>> seems to me that what the database system should do is make that
>> second assumption, and what the user should do is understand that to
>> the degree that transactions depend on the results of aborted
>> subtransactions, there may be serialization anomalies that go
>> undetected.
>
> Actually, Ian's sample transaction is even more malicious, because the
> problem is not caused by reads within the aborted subtransaction -- the
> cached-in-app reads occured *before* the subtransaction started in the
> first place.  I think saving a count(*) across a possibly-failed
> insertion on the same table is wrong.  I can't blame the database for
> the behavior.

I don't see why it's wrong to cache a COUNT(*) across a
possibly-failed substransaction; instead, I would argue that the
problem is that by relying on the knowledge that the subtransaction
failed while inserting A as a justification for inserting B, it's
intrinsically doing something that it sensitive to concurrency.  I
understand that Ian --- and Kevin, and Thomas Munro --- would like
that subtransaction to fail with serialization_failure rather than a
unique_violation, and I previously argued that the change was fine but
shouldn't be back-patched since it might break things for existing
users.  But people are continuing to show up and say "hey, this is
broken", and now Kevin's chosen to back-patch, and I'm not going to
kick up a fuss about that.  After all, there's a growing number of
people complaining about the same thing and saying it's a bug, so I
guess it's a bug!

But even after that fix, at the least, you'll still be able to
demonstrate the same problem by trapping serialization_failure rather
than unique_constraint.  And even if you decree that trapping
serialization_failure is off the table, I bet there are other ways for
a supposedly-serializable transaction to sort of cheat, find out
enough information about what's going on in the system to adjust its
behavior, and then do something different based on that.  And if
that's possible and if a transaction finds a way to do it, then it's
going to allow results not equivalent to any serial schedule.  For
example, imagine a transaction that queries pg_stat_activity or
pg_locks and then makes decisions based on the contents thereof.  That
transaction is determined to behave different under concurrency than
it does on an idle system, and even the ineluctable triumvirate of
Kevin Grittner, Dan Ports, and Michael Cahill will not be able to
prevent it from doing so.  That's not a bug.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

_______________________________________________
Xen-devel mailing list
Xen-devel@xxxxxxxxxxxxx
https://lists.xen.org/xen-devel