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

I guess the preceding posts leave us with these guarantees about read-only transactions which we might want to make explicit in the documentation: (1) A serialization failure cannot be initially thrown on a COMMIT attempt for a read-only transaction; however, if a subtransaction catches a

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

On Fri, Dec 16, 2016 at 9:39 AM, Kevin Grittner wrote: > Good catch! Thanks! -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your

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

On Fri, Dec 16, 2016 at 8:24 AM, Robert Haas wrote: > On Thu, Dec 15, 2016 at 9:01 AM, Kevin Grittner wrote: >> I also realized some other properties of read-only transactions >> that might interest you (and that I should probably document). >> Since the

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

On Thu, Dec 15, 2016 at 9:01 AM, Kevin Grittner wrote: > I also realized some other properties of read-only transactions > that might interest you (and that I should probably document). > Since the only way for a read-only transaction to be the on > experiencing a serialization

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

On Thu, Dec 15, 2016 at 9:53 AM, Ian Jackson wrote: > I don't think "set max_pred_locks_per_transaction generously" is a > practical thing to write in the documentation, because the application > programmer, or admin, has no sensible way to calculate what a >

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

Kevin Grittner writes ("Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more messages] [and 1 more messages]"): > On Thu, Dec 15, 2016 at 6:09 AM, Ian Jackson <ian.jack...@eu.citrix.com> > wrote: > > [...] Are there other rea

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

On Thu, Dec 15, 2016 at 6:09 AM, Ian Jackson wrote: > However, in that example, as you seem to allude to, there is still a > complete serialisation of all the transactions, even the failed T3: > T1,T2,T3. The database has detected the problem before returning data >

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

Kevin Grittner writes ("Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more messages] [and 1 more messages]"): > As Robert pointed out, a read-only transaction cannot normally be > aborted by a serialization failure on COMMIT. Under exceptio

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

On Wed, Dec 14, 2016 at 11:12 AM, Ian Jackson <ian.jack...@eu.citrix.com> wrote: > Kevin Grittner writes ("Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: > Retry on constraint violation [and 2 more messages] [and 1 more messages]"): >> On Wed, Dec 14, 2016 at 10:

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

On Wed, Dec 14, 2016 at 11:20 AM, Ian Jackson wrote: >> I'm not sure Ian is intentionally taking that position. Not all of us >> are as familiar with the ramifications of every serializability >> behavior we may want as you are. > > Indeed. I think it's fair to say

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

Kevin Grittner writes ("Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more messages] [and 1 more messages]"): > On Wed, Dec 14, 2016 at 10:20 AM, Ian Jackson <ian.jack...@eu.citrix.com> > wrote: > > > Let me try to sum

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

On Wed, Dec 14, 2016 at 10:20 AM, Ian Jackson wrote: > Let me try to summarise my understanding of what the developers think > they can and intend to promise, about SERIALIZABLE transactions: > > There is a consistent serialisation of all transactions which >

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

Robert Haas writes ("Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more messages]"): > On Wed, Dec 14, 2016 at 9:26 AM, Kevin Grittner <kgri...@gmail.com> wrote: > > considered. Essentially, the position Ian has been taking is t

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

On Wed, Dec 14, 2016 at 8:38 AM, Robert Haas wrote: > On Wed, Dec 14, 2016 at 9:26 AM, Kevin Grittner wrote: >> Predicate locks >> from reads within subtransactions are not discarded, even if the >> work of the subtransaction is otherwise discarded. > >

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

On Wed, Dec 14, 2016 at 9:40 AM, Kevin Grittner wrote: > On Wed, Dec 14, 2016 at 8:27 AM, Robert Haas wrote: >> But even after that fix, at the least, you'll still be able to >> demonstrate the same problem by trapping serialization_failure rather >>

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

On Wed, Dec 14, 2016 at 8:27 AM, Robert Haas wrote: > 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. I hope not; the "doomed" flag associated with a

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

Thanks for the reply. On Wed, Dec 14, 2016 at 9:26 AM, Kevin Grittner wrote: > considered. Essentially, the position Ian has been taking is that > PostgreSQL should provide the guarantee of (2) above. As far as I > can see, that would require using S2PL -- something the

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

On Wed, Dec 14, 2016 at 12:44 AM, Robert Haas wrote: > On Tue, Dec 13, 2016 at 1:00 PM, Ian Jackson > wrote: > Saying that a set of transactions are serializable is equivalent to > the statement that there is some serial order of execution

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

On Wed, Dec 14, 2016 at 9:05 AM, Alvaro Herrera 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

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

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

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

On Tue, Dec 13, 2016 at 1:00 PM, Ian Jackson wrote: > The conversion is as follows: if a scenario is affected by the caveat, > in there must be at least one transaction T which firstly produces > "impossible" results I, and in which some later statement S produces a >

Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation

On Mon, Dec 12, 2016 at 4:46 PM, Thomas Munro wrote: > On Tue, Dec 13, 2016 at 10:47 AM, Kevin Grittner wrote: >> Barring objections I will back-patch to 9.2 through 9.5 tomorrow. >> (9.1 is out of support and the fix is already in 9.6 and

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

On Tue, Dec 13, 2016 at 12:00 PM, Ian Jackson 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

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

Kevin Grittner writes ("Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more messages]"): > [various things] I get the feeling from your message that I have irritated you. I'm sorry if I have. In particular, I wanted to say that I certainly

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

On Tue, Dec 13, 2016 at 9:50 AM, Ian Jackson <ian.jack...@eu.citrix.com> wrote: > Kevin Grittner writes ("Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: > Retry on constraint violation [and 2 more messages]"): >> On Tue, Dec 13, 2016 at 5:30 AM, Ian Jackson <ian.j

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

Kevin Grittner writes ("Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more messages]"): > On Tue, Dec 13, 2016 at 5:30 AM, Ian Jackson <ian.jack...@eu.citrix.com> > wrote: > > Are all of these cases fixed by fcff8a57519847 &quo

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

On Tue, Dec 13, 2016 at 5:30 AM, Ian Jackson wrote: > I am concerned that there are other possible bugs of this form. > In earlier messages on this topic, it has been suggested that the > "impossible" unique constraint violation is only one example of a > possible

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

Thanks to everyone for your attention. Kevin Grittner writes ("Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation"): > On Mon, Dec 12, 2016 at 8:45 AM, Ian Jackson <ian.jack...@eu.citrix.com> > wrote: > > AIUI the documented behavour is t

Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation

On Tue, Dec 13, 2016 at 10:47 AM, Kevin Grittner wrote: > On Mon, Dec 12, 2016 at 1:06 PM, Kevin Grittner wrote: >> On Mon, Dec 12, 2016 at 12:32 PM, Kevin Grittner wrote: >> >>> As you can see, this generated a serialization failure. >>

Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation

On Mon, Dec 12, 2016 at 1:06 PM, Kevin Grittner wrote: > On Mon, Dec 12, 2016 at 12:32 PM, Kevin Grittner wrote: > >> As you can see, this generated a serialization failure. > > That was on 9.6. On earlier versions it does indeed allow the > transaction on

Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation

On Mon, Dec 12, 2016 at 12:32 PM, Kevin Grittner wrote: > As you can see, this generated a serialization failure. That was on 9.6. On earlier versions it does indeed allow the transaction on connection 2 to commit, yielding a non-serializable result. This makes a pretty

Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation

On Mon, Dec 12, 2016 at 8:45 AM, Ian Jackson wrote: > AIUI the documented behavour is that "every set of successful > transactions is serialisable". Well, in context that is referring to serializable transactions. No such guarantee is provided for other isolation

Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation

Kevin Grittner writes ("Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation"): > If I recall correctly, the constraints for which there can be > errors appearing due to concurrent transactions are primary key, > unique, and foreign key constraints

Re: [HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation

On Fri, Dec 9, 2016 at 12:26 PM, Ian Jackson wrote: > In summary: PostgreSQL only provides transaction serialisability for > successful transactions. Even with SERIALIZABLE, transactions may > fail due to spurious and "impossible" constraint violations. > > As a

[HACKERS] [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation

Hi. This message is going to xen-devel (because that's where the osstest project is) and to pgsql-hackers (because I hope they may be able to advise about the scope of the PostgreSQL SERIALIZABLE constraint problem). In summary: PostgreSQL only provides transaction serialisability for successful