MIME-Version: 1.0
References: <CAHYXj6eJiX0jQpq4u7xYc1qy3=zBN+sPhGMTJETAF0fdoSyNUA@mail.gmail.com>
 <CAKAnmmJfKZejX0yE8dMHRZiQApq-PO_+aetGCHkkjK3VdOjD3w@mail.gmail.com> <CAHYXj6etiV1pTBvBiQFZ=jqxvNHczZio9cSSb7gMy+RrWwZTTQ@mail.gmail.com>
In-Reply-To: <CAHYXj6etiV1pTBvBiQFZ=jqxvNHczZio9cSSb7gMy+RrWwZTTQ@mail.gmail.com>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Wed, 11 Sep 2024 08:57:17 -0400
Message-ID: <CAKAnmmKmgOgo2yXfpgseFQXJy1__D__+Oo-1Dizcz9zUh-MLJQ@mail.gmail.com>
Subject: Re: Ghost data from failed FDW transactions?
To: Jacob Biesinger <jake.biesinger@gmail.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000eeaf770621d78906"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmmKmgOgo2yXfpgseFQXJy1__D__%2BOo-1Dizcz9zUh-MLJQ%40mail.gmail.com>
Precedence: bulk

--000000000000eeaf770621d78906
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Any updates on this?

A few replies from me inline:

On Wed, Aug 28, 2024 at 12:18=E2=80=AFPM Jacob Biesinger <jake.biesinger@gm=
ail.com>
wrote:

> There aren't many details in the docs around failure modes... is there
> anything there that could cause this issue?
>

Nothing that I know of, but it's possible there is some sort of weird race
condition/bug. But frankly, it's far more likely to be some sort of
application bug  / missed behavior. If you could trigger it at will by
developing a self-contained test, that would be ideal.


> The ordering shouldn't matter, but this also means that error handling
> (e.g., when there is a conflict with the `EXCLUDE "serialNumber"`
> constraint) may not stop the second `INSERT` statement from being attempt=
ed
> (and rejected by postgres). But I think that's all client-side details th=
at
> shouldn't affect the FDW txn semantics, right?
>

Correct, but it's hard to say for sure without seeing the code or knowing
exactly how the serialization errors are being handled. I'm not clear on
how the inserts are run "in parallel" if your flow is:

BEGIN;
> INSERT INTO "devices" ("orgId", "patientId", "deviceId", "data") ...
> INSERT INTO "devices" ("orgId", "patientId", "deviceId", "data") ...

SELECT * FROM "rootDb"."assets";

-- execute some logic client-side, nothing touching the DB

UPDATE "rootDb"."assets" WHERE ...;

COMMIT;


Perhaps expand on that, because it almost sounds like you have two
processes trying to talk to the same backend at once?

Cheers,
Greg

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

<div dir=3D"ltr"><div>Any updates on this?</div><div><br></div><div>A few r=
eplies from me inline:</div><div><br></div><div class=3D"gmail_quote"><div =
dir=3D"ltr" class=3D"gmail_attr">On Wed, Aug 28, 2024 at 12:18=E2=80=AFPM J=
acob Biesinger &lt;<a href=3D"mailto:jake.biesinger@gmail.com">jake.biesing=
er@gmail.com</a>&gt; wrote:</div><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex"><div dir=3D"ltr"><div class=3D"gmail_quote"><div>There aren&#39;t m=
any details in the docs around failure modes... is there anything there tha=
t could cause this issue?</div></div></div></blockquote><div><br></div><div=
>Nothing that I know of, but it&#39;s possible there is some sort of weird =
race condition/bug. But frankly, it&#39;s far more likely to be some sort o=
f application bug=C2=A0 / missed behavior. If you could trigger it at will =
by developing a self-contained test, that would be ideal.=C2=A0</div><div>=
=C2=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0=
.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"l=
tr"><div class=3D"gmail_quote"><div>The ordering shouldn&#39;t matter, but =
this also means that=C2=A0error handling (e.g., when there is a conflict wi=
th the `EXCLUDE &quot;serialNumber&quot;` constraint) may not stop the seco=
nd `INSERT` statement from being attempted (and rejected by postgres). But =
I think that&#39;s all client-side details that shouldn&#39;t affect the FD=
W txn semantics, right?</div></div></div></blockquote><div><br></div><div>C=
orrect, but it&#39;s hard to say for sure without seeing the code or knowin=
g exactly how the serialization errors are being handled. I&#39;m not clear=
 on how the inserts are run &quot;in parallel&quot; if your flow is:</div><=
div><br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px=
 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">BEGIN;<br>I=
NSERT INTO &quot;devices&quot; (&quot;orgId&quot;, &quot;patientId&quot;, &=
quot;deviceId&quot;, &quot;data&quot;) ...<br>INSERT INTO &quot;devices&quo=
t; (&quot;orgId&quot;, &quot;patientId&quot;, &quot;deviceId&quot;, &quot;d=
ata&quot;) ...=C2=A0</blockquote><blockquote class=3D"gmail_quote" style=3D=
"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-le=
ft:1ex">SELECT * FROM &quot;rootDb&quot;.&quot;assets&quot;;=C2=A0</blockqu=
ote><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bor=
der-left:1px solid rgb(204,204,204);padding-left:1ex">-- execute some logic=
 client-side, nothing touching the DB=C2=A0</blockquote><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex">UPDATE &quot;rootDb&quot;.&quot;assets&quo=
t; WHERE ...;=C2=A0</blockquote><blockquote class=3D"gmail_quote" style=3D"=
margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-lef=
t:1ex">COMMIT;</blockquote><div>=C2=A0<br></div><div>Perhaps expand on that=
, because it almost sounds like you have two processes trying to talk to th=
e same backend at once?</div><div><br></div><div>Cheers,</div><div>Greg</di=
v><div><br></div></div></div>

--000000000000eeaf770621d78906--