From: Ian Jackson <ian.jackson@eu.citrix.com>
To: Kevin Grittner <kgrittn@gmail.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: xen-devel@lists.xenproject.org
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more messages] [and 1 more messages]
Date: Thu, 15 Dec 2016 15:53:53 +0000
Message-ID: <22610.48273.860663.838783@mariner.uk.xensource.com> (raw)
In-Reply-To: <CACjxUsOtSbArs+tnx2rgK37BUANBaCb4oqvdnPaFxU4Oc7FSKw@mail.gmail.com>
References: <1481307991-16971-1-git-send-email-ian.jackson@eu.citrix.com>
<CACjxUsMsHxZ_SrWw7bdCT1Kn8E9mtA1TvhAEbTt-L9AWKKZLCg@mail.gmail.com>
<22606.47105.549023.301281@mariner.uk.xensource.com>
<CACjxUsM3SbYPwPi8K5YJ+w016-GV2RT3mDFHqKAz8Ris+x2vCw@mail.gmail.com>
<CACjxUsMSyhmUStWshxMeAGN7r2YHp1cTSMVPSwDCeVJD-aR6ag@mail.gmail.com>
<CACjxUsNKtNVhX6EoxY9FjS7N1aJ=inopGT0EqV_yKF6oup0p_g@mail.gmail.com>
<CAEepm=3Su08X4tTCUMjpxWAbDWbRhKrdw-hQqSmuhTuzYrytew@mail.gmail.com>
<22607.56276.807567.924144@mariner.uk.xensource.com>
<CACjxUsO+ztVVwxLQ9gdAapn3bqz+1x7m-D8-T8d1Z2aNs0qVfQ@mail.gmail.com>
<22608.6345.97849.783099@mariner.uk.xensource.com>
<CACjxUsNaAdSDKc_p4S52L3p6r6_7F1Nb+viKaCLFVCY=tYdCTg@mail.gmail.com>
<22608.14118.291149.821376@mariner.uk.xensource.com>
<CA+TgmoZ4MH1tSLGZy7gQFpVh3udsucxqvqc0rF8zHqX8aXmRwQ@mail.gmail.com>
<CACjxUsNum7KFyAoe0THB2_qGUMsW9AhwRZ8zANnj_Q8spD8=SA@mail.gmail.com>
<20161214140539.hotxjeoshju2m7zw@alvherre.pgsql>
<CA+TgmoZO+N=ATeWD1fZ4rKngPSOu+EQrTGt_7vtVFMAWVogLOA@mail.gmail.com>
<CA+TgmoaZ2CHBVMWRUd=-M6n9iX_4xezNSxHTHGRRmsABVGi4Aw@mail.gmail.com>
<22609.29020.794032.389420@mariner.uk.xensource.com>
<CACjxUsNRtRSiEcuz5mCsYzNH+62=ZH5j-j=a-iRm6ri-mnMETQ@mail.gmail.com>
<22609.32147.935746.463570@mariner.uk.xensource.com>
<CACjxUsO2cO70FV2xueUd-cP20XeYotWAxbMBUGTRFsCtO7JHGg@mail.gmail.com>
<22610.34816.412309.196179@mariner.uk.xensource.com>
<CACjxUsOtSbArs+tnx2rgK37BUANBaCb4oqvdnPaFxU4Oc7FSKw@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
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.jackson@eu.citrix.com> wrote:
> > [...] Are there other reasons,
> > besides previously suppressed serialisation failures, why commit of a
> > transaction that did only reads[1] might fail ?
>
> I'm pretty confident that if you're not using prepared transactions
> the answer is "no". [...] I fear that [for now], if "pre-crash"
> prepared transactions are still open, some of the deductions above
> may not hold.
I think it is reasonable to write in the documentation "if you use
prepared transactions, even read only serialisable transctions might
throw a serialisation failure during commit, and they might do so
after returning data which is not consistent with any global
serialisation".
Prepared transactions are a special purpose feature intended for use
by external transaction management software which I hope could cope
with a requirement to not trust data from a read only transaction
until it had been committed. (Also, frankly, the promise that a
prepared transaction is can be committed successfully with "very high
probability" is not sufficiently precise to be of use when building
robust software at the next layer up.)
> One other situation in which I'm not entirely sure, and it would
> take me some time to review code to be sure, is if
> max_pred_locks_per_transaction is not set high enough to
> accommodate tracking all serializable transactions in allocated RAM
> (recognizing that they must often be tracked after commit, until
> overlapping serializable transactions commit), we have a mechanism
> to summarize some of the committed transactions and spill them to
> disk (using an internal SLRU module). The summarized data might
> not be able to determine all of the above as precisely as the
> "normal" data tracked in RAM. To avoid this, be generous when
> setting max_pred_locks_per_transaction; not only will it avoid this
> summarization, but it will reduce the amount of summarization of
> multiple page locks in the predicate locking system to relation
> locks. Coarser locks increase the "false positive" rate of
> serialization failures, reducing performance.
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
sufficiently generous value is.
You seem to be implying that code relying on the summarised data might
make over-optimistic decisions. That seems dangerous to me, but (with
my very dim view of database innards) I can't immediately see how to
demonstrate that it must in any case be excluded.
But, I think this can only be a problem (that is, it can only cause a
return of un-serialisable results within such a transaction) if, after
such a spill, COMMIT would recalculate the proper answers, in full,
and thus be able to belatedly report the serialisation failure. Is
that the case ?
> > If so presumably it always throws a serialisation failure at that
> > point. I think that is then sufficient. There is no need to tell the
> > application programmer they have to commit even transactions which
> > only read.
>
> Well, if they don't explicitly start a transaction there is no need
> to explicitly commit it, period. [...]
Err, yes, I meant multi-statement transactions. (Or alternatively by
"have to commit" I meant to include the implicit commit of an implicit
transaction.)
> If you can put together a patch to improve the documentation, that
> is always welcome!
Thanks. I hope I will be able to do that. Right now I am still
trying to figure out what guarantees the application programmer can be
offered.
Regards,
Ian.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: ian.jackson@eu.citrix.com, kgrittn@gmail.com, robertmhaas@gmail.com, alvherre@2ndquadrant.com, thomas.munro@enterprisedb.com, xen-devel@lists.xenproject.org
Subject: Re: [OSSTEST PATCH 0/1] PostgreSQL db: Retry on constraint violation [and 2 more messages] [and 1 more messages]
In-Reply-To: <22610.48273.860663.838783@mariner.uk.xensource.com>
* 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