From: Ian Jackson <ian.jackson@eu.citrix.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-ID: <22610.48273.860663.838783@mariner.uk.xensource.com>
Date: Thu, 15 Dec 2016 15:53:53 +0000
To: Kevin Grittner <kgrittn@gmail.com>
CC: Robert Haas <robertmhaas@gmail.com>, Alvaro Herrera
	<alvherre@2ndquadrant.com>, Thomas Munro <thomas.munro@enterprisedb.com>,
	<xen-devel@lists.xenproject.org>, "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]
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>
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

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