Re: Ghost data from failed FDW transactions?

Any updates on this?

A few replies from me inline:

On Wed, Aug 28, 2024 at 12:18 PM Jacob Biesinger <jake(dot)biesinger(at)gmail(dot)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 attempted
> (and rejected by postgres). But I think that's all client-side details that
> 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