Re: Ghost data from failed FDW transactions?

[Rob Sargent <robjsargent@xxxxxxxxx>]

On Aug 28, 2024, at 10:18 AM, Jacob Biesinger <jake.biesinger@xxxxxxxxx> 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" }'

)