Re: Re: How to reproduce serialization failure for a read only transaction.

On 1/6/14, 5:27 PM, Florian Pflug wrote:> On Jan6, 2014, at 23:28 , AK <alkuzo(at)gmail(dot)com> wrote:
> First, dependency tracking can produce false positives, i.e. assume that
> dependencies exist between transactions which are actually independent.
> In my example, postgres fails to realize that W2 can be executed after W1,
> unless it uses an index scan for the UPDATE in W2. You can avoid that either
> by creating an index on the id column, and forcing W2 to use that by setting
> enable_seqscan to off, or by creating two tables t1 and t2 instead of one
> table t with two records (You'll have to modify the SELECT to scan both tables
> too).
>
> Second, since R executes it's SELECT before W1 commits, postgres is already
> aware that R poses a problem when W1 commits, and it chooses to cancel W1
> instead of R. To avoid that, R needs to do the SELECT after W1 committed.
> Yet still force R to acquire a snapshot *before* that commit (without that,
> there's no serialization failure since R than simply executes after W1 and
> W2), you'll need to do e.g. SELECT 1 after R's START TRANSACTION command.
>
> I think the following should work (or, rather, fail)

This email and the previous one are an awesome bit of information, can we add it to the docs somehow? Even if it's just dumping the emails into a wiki page and referencing it?
--
Jim C. Nasby, Data Architect jim(at)nasby(dot)net
512.569.9461 (cell) http://jim.nasby.net