Re: Ghost data from failed FDW transactions?

> On Aug 28, 2024, at 10:18 AM, Jacob Biesinger <jake.biesinger@gmail.com> wrote:
> 
> But to go deeper, we use the javascript knex adapter and some application-level transaction management that automatically retries a transaction N times when it encounters serialization errors. On this particular endpoint, the emitted SQL for the full transaction looks something like:
> 
> BEGIN;
> INSERT INTO "devices" ("orgId", "patientId", "deviceId", "data")
> VALUES (
> 'org1',
> 'patient1',
> 'device1',
> '{"id": "device1", "patientId": "patient1", "serialNumber": "12345", "status": "active" }'
> );
> INSERT INTO "devices" ("orgId", "patientId", "deviceId", "data")
> VALUES (
> 'org1',
> 'patient1',
> 'device2',
> '{"id": "device2", "patientId": "patient1", "serialNumber": "67890", "status": "active" }'
> );
> SELECT * FROM "rootDb"."assets";
> 
> -- execute some logic client-side, nothing touching the DB
> 
> UPDATE "rootDb"."assets" WHERE ...;
> COMMIT;
> 
Any value in supplying a single insert statement a la (less back and forth perhaps?):
BEGIN;
INSERT INTO "devices" ("orgId", "patientId", "deviceId", "data")
VALUES (
'org1',
'patient1',
'device1',
'{"id": "device1", "patientId": "patient1", "serialNumber": "12345", "status": "active" }’),
(
'org1',
'patient1',
'device2',
'{"id": "device2", "patientId": "patient1", "serialNumber": "67890", "status": "active" }'
)