MIME-Version: 1.0
References: 
 <CAD9Bb3s3MN-iebRetMhNGs=sjnDwK4jw4BYG8+W9LeBbhi6HyA@mail.gmail.com>
 <CAKFQuwZ4=c7J-g5=t2veA2-w5tTi3oER34HJvYQ3QsaqSNqEig@mail.gmail.com>
In-Reply-To: 
 <CAKFQuwZ4=c7J-g5=t2veA2-w5tTi3oER34HJvYQ3QsaqSNqEig@mail.gmail.com>
From: Joe <symphony.red+pg@gmail.com>
Date: Wed, 23 Mar 2022 22:31:47 -0700
Message-ID: 
 <CAD9Bb3s9acbwQ5FuMZ8nKEv8Ys__OBXpdk_5i9WMXpEsdcyjfg@mail.gmail.com>
Subject: Re: SELECT FOR UPDATE on rows that don't exist
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: pgsql-novice <pgsql-novice@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000032fdb105daf02aec"
Archived-At: 
 <https://www.postgresql.org/message-id/CAD9Bb3s9acbwQ5FuMZ8nKEv8Ys__OBXpdk_5i9WMXpEsdcyjfg%40mail.gmail.com>
Precedence: bulk

--00000000000032fdb105daf02aec
Content-Type: text/plain; charset="UTF-8"

David,
Thank you very much. I missed the advisory locks feature. That feels like
the perfect thing; the BIGINT keys are a bit clunky but that's minor.

Your description of the sequence of events for the two SELECT FOR UPDATE
transactions makes sense. To apply it to my pseudo code, the second
transaction would unblock when the row was deleted in the first rather than
when the first transaction commits or rolls back.

One question though:

You've informed the system you are going to be updating rows on the table
> but as yet have not given it specific rows to protect.
>
>>
>> Without #*1, a simple experiment shows that two processes can be in the
>> critical section at the same time. Add #*1 seems to achieve the desired
>> behavior, but is it really?
>>
>
> The index will not allow duplicates to be inserted and the first one to
> try forces all other potential insertions to wait until the first one
> commits;
>

Is this true in all situations? That is, will an index insertion in one
transaction block the index insertion in another transaction? Presumably
only only if the index entries match, but how does this work in practice?
Does the second transaction block when it sees the conflict? Or does it
proceed on its own version of the index proceeding until it tries to commit?

Thanks again,
Joe

--00000000000032fdb105daf02aec
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>David,</div><div>Thank you very much. I missed the ad=
visory locks feature. That feels like the perfect thing; the BIGINT keys ar=
e a bit clunky but that&#39;s minor.</div><div><br></div><div>Your descript=
ion of the sequence of events for the two SELECT FOR UPDATE transactions ma=
kes sense. To apply it to my pseudo code, the second transaction would unbl=
ock when the row was deleted in the first rather than when the first transa=
ction commits or rolls back. <br></div><div><br></div><div>One question tho=
ugh:</div><div><br></div><div class=3D"gmail_quote"><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex"><div dir=3D"ltr"><div class=3D"gmail_quote"><div=
 style=3D"font-family:arial,helvetica,sans-serif">You&#39;ve informed the s=
ystem you are going to be updating rows on the table but as yet have not gi=
ven it specific rows to protect.</div><div style=3D"font-family:arial,helve=
tica,sans-serif"></div><blockquote class=3D"gmail_quote" style=3D"margin:0p=
x 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><d=
iv dir=3D"ltr"><div></div><div><br></div><div>Without #*1, a simple experim=
ent shows that two processes can be in the critical section at the same tim=
e. Add #*1 seems to achieve the desired behavior, but is it really?</div></=
div></blockquote><div><br></div><div style=3D"font-family:arial,helvetica,s=
ans-serif">The index will not allow duplicates to be inserted and the first=
 one to try forces all other potential insertions to wait until the first o=
ne commits;</div></div></div></blockquote><div><br></div><div>Is this true =
in all situations? That is, will an index insertion in one transaction bloc=
k the index insertion in another transaction? Presumably only only if the i=
ndex entries match, but how does this work in practice? Does the second tra=
nsaction block when it sees the conflict? Or does it proceed on its own ver=
sion of the index proceeding until it tries to commit?<br></div><div><br></=
div><div>Thanks again,</div><div>Joe <br></div></div></div>

--00000000000032fdb105daf02aec--