Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sjQxl-000fHN-FJ for pgsql-general@arkaria.postgresql.org; Wed, 28 Aug 2024 22:16:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sjQxj-00BD2l-Cl for pgsql-general@arkaria.postgresql.org; Wed, 28 Aug 2024 22:16:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sjQxj-00BD1U-03 for pgsql-general@lists.postgresql.org; Wed, 28 Aug 2024 22:16:23 +0000 Received: from mail-il1-x12f.google.com ([2607:f8b0:4864:20::12f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sjQxc-001z5A-7F for pgsql-general@lists.postgresql.org; Wed, 28 Aug 2024 22:16:22 +0000 Received: by mail-il1-x12f.google.com with SMTP id e9e14a558f8ab-39d2ceca81cso27382625ab.3 for ; Wed, 28 Aug 2024 15:16:17 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1724883375; x=1725488175; darn=lists.postgresql.org; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:from:to:cc:subject:date:message-id:reply-to; bh=Hi4mLQl/rB5PT1emI+OnGh8VkPP9tnT+iVCOY0x29tc=; b=hUgZtMTdpYVtTpjO0uJa+cwTI9TEvr0lHbud5peebneu94ghZHSwu5l7RPwqfQxhM9 pxnRwVZ43EESUl5rL1psQxwkqdin+b+HxTPWd/yrw2e5LoqxV/Cguu0o2r2nkvDxOdZk 1rnQBdu8BePznPXo53ifO6/4WxmSsyjbVOaC6e+FubdW+YLjZbD0dZSz8jz9u0KgPMv5 sOMrSd3ZEp4xcoQWCzV9xGYY737dbvBKIBLDCuL0aI49kmTB8Y/Xh4y9DXnnIhbMp3Ue m82fEX4xoTZP4Ggdvxn10JPORvAEnBTitEK+JBf3Kq5zW80GjDKdSZF5QnsnJyrcwZJs N48A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724883375; x=1725488175; h=references:to:cc:in-reply-to:date:subject:mime-version:message-id :from:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=Hi4mLQl/rB5PT1emI+OnGh8VkPP9tnT+iVCOY0x29tc=; b=I9zInE/bEntloJQKJ16LSzeiDJAHpIY1cPe301foxsemhkL59AhfF/qkvniqNSmtfS 6kuOUkNyKRyHvD4BNGGIwXnplPKY3IHYRb0n3fsAEiRtXLEZ7QsRxxbQtiGVii4GheXx s8iXd6FOSQe6qPYU/sA7PP0BNsWPMK7RM5YluiIiaIEBmqPt0sRgwEzzwgW6USYZtMsX x80tsSF4S49t4aTwGdmyXNd7bu9DIPNODPlhjEGqepXNUtSrGviGYw095Q4bBDfxrO1P bp5+6u/fWbXcMqqlFzw9shFN6bLiwmWbOopC7/1G416ZMV2jMi2Lq52iAQ+IsXaoCiZi aQLA== X-Forwarded-Encrypted: i=1; AJvYcCWhwyz1Rzr5fgIUAKFbrf9AirMrbX+XCc9/345fYisx7317+YKvDFosw9+QuIWNICyYsuuMM4JfsWAlkzU1@lists.postgresql.org X-Gm-Message-State: AOJu0YwPb5oPNbvceOjhcICT/u/KpzJGcBJkagdfaxkMzhtg2D8YTx2v lDyAF3KVg700PeKmRNHo5iNz7NyZkUlTXFONziNb2/eQDv1YQ4FxysKWOQ== X-Google-Smtp-Source: AGHT+IGoKAGcJ8DOe/Mjl97w1A3prc9DOO+Xp5XFmJUBHubUCSjYwQYqgmdYkcMyL9h3yi/M6i9s+g== X-Received: by 2002:a05:6e02:1c41:b0:39b:3380:e5b2 with SMTP id e9e14a558f8ab-39f378ea9e1mr12721715ab.2.1724883374747; Wed, 28 Aug 2024 15:16:14 -0700 (PDT) Received: from smtpclient.apple (c-174-52-48-9.hsd1.ut.comcast.net. [174.52.48.9]) by smtp.gmail.com with ESMTPSA id 41be03b00d2f7-7cd9ad55e39sm11526352a12.58.2024.08.28.15.16.13 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Wed, 28 Aug 2024 15:16:14 -0700 (PDT) From: Rob Sargent Message-Id: <1568BE1C-261C-474A-803F-66B8B757F1B1@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_D349DBB2-B941-4047-8859-DB0099912FCA" Mime-Version: 1.0 (Mac OS X Mail 14.0 \(3654.120.0.1.15\)) Subject: Re: Ghost data from failed FDW transactions? Date: Wed, 28 Aug 2024 16:16:08 -0600 In-Reply-To: Cc: Greg Sabino Mullane , pgsql-general@lists.postgresql.org To: Jacob Biesinger References: X-Mailer: Apple Mail (2.3654.120.0.1.15) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_D349DBB2-B941-4047-8859-DB0099912FCA Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=utf-8 > On Aug 28, 2024, at 10:18 AM, Jacob Biesinger = wrote: >=20 > 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: >=20 > 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"; >=20 > -- execute some logic client-side, nothing touching the DB >=20 > UPDATE "rootDb"."assets" WHERE ...; > COMMIT; >=20 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" }=E2=80=99), ( 'org1', 'patient1', 'device2', '{"id": "device2", "patientId": "patient1", "serialNumber": "67890", = "status": "active" }' ) --Apple-Mail=_D349DBB2-B941-4047-8859-DB0099912FCA Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=utf-8

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" }=E2=80=99),
(
'org1',
'patient1',
'device2',
'{"id": "device2", "patientId": = "patient1", "serialNumber": "67890", "status": "active" = }'
)

= --Apple-Mail=_D349DBB2-B941-4047-8859-DB0099912FCA--