Any updates on this?
A few replies from me inline:
On Wed, Aug 28, 2024 at 12:18 PM Jacob Biesinger <jake.biesinger@xxxxxxxxx> 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
