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

[Kevin Grittner <kgrittn@xxxxxxxxx>]

On Tue, Dec 13, 2016 at 12:00 PM, Ian Jackson <ian.jackson@xxxxxxxxxxxxx> wrote:
> Kevin Grittner writes:

> I still hope to be able to convince you that the definition of
> SERIALIZABLE (in the pgsql docs) ought to be a stronger version, which
> covers even non-committed transactions.

That doesn't seem likely.  The stronger definition would perform so
poorly that even if you managed to convince me, the PostgreSQL
community as a whole has already rejected it.

>   "you must discard any results from a transaction which later throws
>    a serialization failure"

You argue that saying "If A happens, you must not use the results"
means that "If A doesn't happen you can use the results."  That
does not logically follow; your argument based on it has no merit.
Basically, a serializable transaction must successfully commit in
order to consider any results from it to be valid.  The quote above
is discussing one way that can happen, which does not preclude
other ways.

>>> 1. Declare that all spurious failures, in SERIALIZABLE transactions,
>>> are bugs.
>>
>> It's not clear to me what you mean here.
>
> I mean that the pgsql documentation should simply say that for
> SERIALIZABLE transactions, there is always a coherent serialisation.
> Even if the transaction failed due to a constraint violation, or
> serialisation failure, or database disconnection, or whatever.
>
> I think there is no "useful" weaker guarantee.

That's wrong on the face of it, and certainly at odds with all
research papers and standards in the area.

> By which I mean that for any reasonable weaker guarantee: any scenario
> in which pgsql violates the stronger guarantee, can be converted into
> a scenario where pgsql violates the weaker guarantee.

You have failed to provide any evidence of that.  Twisting
statements and the principles of logical inference don't advance
your position.

> The fact that pgsql might generate "spurious" constraint violations,
> even in SERIALIZABLE transactions, has been known for many years.

Yes, it was clearly discussed during development.

> See the URLs etc. in my patch, which include a reference from 2009.

This paper from 2007 was among those cited during development of
the PostgreSQL serializable implementation:

Automating the Detection of Snapshot Isolation Anomalies.
Sudhir Jorwekar, Alan Fekete, Krithi Ramamritham, S. Sudarshan.
VLDB ‘07, September 23-28, 2007, Vienna, Austria.
https://www.cse.iitb.ac.in/~sudarsha/Pubs-dir/VLDB07-snapshot.pdf

Among relevant statements in that paper:

| The database system ensures the preservation of some integrity
| constraints which are explicitly declared to the system in the
| schema definition, such as uniqueness of primary key and
| referential integrity. Some of the SI anomalies are avoided due
| to the dbms enforcement of these constraints.

Of course, both pre-date development of the feature itself -- which
was released as part of PostgreSQL 9.1, in September, 2011.

> Many people have always regarded this as a bug.  The pgsql
> authors have not agreed.

Right.  For some people, a system has a bug if it does not behave
as they would most like it to.  In my world, it is not a bug if it
is functioning as defined, intended, and documented.  The change
requested is a feature request, and a feature I would also like to
see.  It is not one that anyone has offered to pay to have
developed, which is a significant barrier to implementation.

>> The behavior we have been providing, modulus the bug you just found
>> and any related bugs of the same ilk, is "we don't allow
>> serialization anomalies into the database or in results from
>> serializable transactions which commit."  That is enough to be very
>> useful to many.
>
> As I explain, I think that if you make that promise, you will have to
> do all the work necessary to make the stronger promise anyway.

That is absolutely *not* true of your suggestion that results
returned from a serializable transaction which does not
subsequently commit must be free from serialization anomalies.
You are just factually wrong there.

> I think all scenarios of this kind can be shown to allow serialisation
> anomalies.

Which kind?  Catching an exception from a declarative constraint?
Not all of them can, especially after the patch that went into 9.6
and that I'm intending to back-patch to other supported branches
shortly.  Other exceptions?  I have seen no evidence that any
others can, and I have a lot of reasons to believe that the special
properties of the accesses from constraint implementations which
allow those exceptions to be problematic are not present for other
exceptions.

Now, that is not an assertion that it is impossible for the code to
have some other bug, but if there is such a bug we would need to
find it to be able to fix it.

> I'm afraid I have no idea.  But if that is a complete description of
> the bug (if it doesn't affect "INSERT ... ON CONFLICT" for example)
> then that is good.

A bug was found in the initial release of INSERT ... ON CONFLICT,
and fixed in the next minor release after it was found.  Will that
be the last bug ever found?  I don't know.  We still occasionally
find bugs in other areas of PostgreSQL which have been around for
decades without being noticed; if it's hard to hit, any bug could
lurk undetected for a while.

I appreciate your pointing out that catching a constraint error in
a subtransaction and throwing away the work of the subtransaction
without re-throwing that exception or throwing a new exception, in
combination with other factors, can cause a serialization anomaly.
That was helpful.  If you find any other way to create a
serialization anomaly in the database or in query results from a
transaction which does not fit this pattern, I would be interested
in hearing about it.

I won't respond to any further repetition of the assertion that it
is a bug that a serializable transaction which does not
successfully commit can return results that represent a
serialization anomaly.  That is a design choice which allows much
higher concurrency and throughput than what you suggest as an
alternative, and the community has already vigorously rejected
taking the slow approach on that -- even if I was somehow convinced
I'm quite sure I couldn't move the community to a consensus on that
position.  Calling it a bug requires a rather tortured definition
of what constitutes a bug (basically, designed differently than you
think you would prefer).  There's no benefit to you, me, or the
thousands who read this list to keep repeating that.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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