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 1sj762-00EvQD-2c for pgsql-general@arkaria.postgresql.org; Wed, 28 Aug 2024 01:03:38 +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 1sj75z-00F6jx-P4 for pgsql-general@arkaria.postgresql.org; Wed, 28 Aug 2024 01:03:36 +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 1sj75z-00F6jp-82 for pgsql-general@lists.postgresql.org; Wed, 28 Aug 2024 01:03:35 +0000 Received: from mail-wm1-x330.google.com ([2a00:1450:4864:20::330]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sj75s-001qAc-G0 for pgsql-general@lists.postgresql.org; Wed, 28 Aug 2024 01:03:35 +0000 Received: by mail-wm1-x330.google.com with SMTP id 5b1f17b1804b1-4280ca0791bso54871595e9.1 for ; Tue, 27 Aug 2024 18:03:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1724807008; x=1725411808; darn=lists.postgresql.org; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=e6zkDhg0O9N2H+2eII+zRRfjWBcfDc5WQlMjzRQTpPs=; b=LfKsJ2qFgfmE8G2Ad24nC0JMjikmQmeE85Fx8GEFBfUYJWVRC+jHpYhTofzLpaR1vz n+6hBNhBh5gLAoauYoevPWcRrM6vffUhYayWpC0Cxfip1gTdDu2o6EJ9sVunVViKS4sS 7R+P0byRbnUmtXluQxs22TFJBEybP3r6/31i15XAGNu0I1IJrstmEMdGQqitOG4g1gGi vHFJ3lXel7oM3I/OY5nk/5e9SSSbK+Dtgu5LrC1JRTOJZQRnesqcIihRYgL11X7L2IFH BdYmT4kA122Qytaosauw2QwsbN9HyifuTSaefpitjRL2a+p1wKYDkD8ioVuqa20IoqNW zXoQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1724807008; x=1725411808; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=e6zkDhg0O9N2H+2eII+zRRfjWBcfDc5WQlMjzRQTpPs=; b=ZSLSjjNKJSyYkZxjlhQX3Oul9k78KLs/ZYZnuk11B9/0kTCQR6KexM9H7rCizA5Nx4 TlBKsKBfZER37Aj6oxSZHKLPZG7p2cagbrB3LAITl1bC1RFUFNCtcsDWUP09EMyMYHZi wBNdlw6AL62ixb04nBYwneirB2I8KUL/bPHAfZ0V9c73nwkJYPlRTFo19VF4bGrsEp2G 423ViMVmKo5L/xD7oa1rKDfp79aqa/8ij21BdI0tZkgibIvF2z+48gr1LVUblG2JP+oA 6kPvw+CuUt96+WEIxBlxCVYf06jcCSKoBDiIeeuCKOzw2e/tgYvrCDt9Ih80c6sBoLkH jNsQ== X-Gm-Message-State: AOJu0YznixWFAB48x8i8FMkOA+XiQ6ua6C4SLypAlt0E3gnGzmzKZB+M QknVtGw1Wr7aaUnyqToCqHpyj4OH640WyGgsAVnlmwdG+98ch9gl9mkVjutkFKMHNVhFrMQS2ld jF8DJ9qDBlPlMv/5sdHujMEpYkletHWMj+8k= X-Google-Smtp-Source: AGHT+IHqJlZ0cGsviIsgZyKHwDcSknE2SK78XdmfljeZScZ2f4UH5DVV0qPYe537NSGX3MpUigoc/53mUNooKKnUK9E= X-Received: by 2002:a05:600c:1c13:b0:426:5416:67de with SMTP id 5b1f17b1804b1-42ba66ab824mr1415995e9.30.1724807007342; Tue, 27 Aug 2024 18:03:27 -0700 (PDT) MIME-Version: 1.0 From: Jacob Biesinger Date: Tue, 27 Aug 2024 18:03:16 -0700 Message-ID: Subject: Ghost data from failed FDW transactions? To: pgsql-general@lists.postgresql.org Content-Type: multipart/alternative; boundary="000000000000fa98c90620b3ecaf" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000fa98c90620b3ecaf Content-Type: text/plain; charset="UTF-8" Hi there! We have a setup where, for compliance reasons, we hoist a portion of data from several "tenant" databases into a "root" / common / untenanted DB. Through the magic of postgres_fdw, row triggers, and distributed transactions, we automatically hoist the needed columns into the untenanted DB whenever there are changes in any of the tenant DBs. The root DB is never written to outside of these triggers, and act as a sort of cross-tenant index. I'm scratching my head at a few rows in the root DB, where it seems the corresponding tenant transaction rolled back, but the root DB transaction committed -- there is no row in the tenant but the root DOES have one. I don't have a smoking gun just yet, but this is not the first time we've seen this issue. Before I jump into particulars, does this sound like expected behavior? We run SERIALIZABLE txn level everywhere (set at the cluster level). Thanks so much in advance for any insights here! Here's my setup: ## info + cluster-level flags GCP cloudsql postgres version: 15.7 default_transaction_isolation: serializable ## The tenant DB: CREATE TABLE "devices" ( "orgId" TEXT NOT NULL, "patientId" TEXT NOT NULL, "deviceId" TEXT NOT NULL, "data" JSONB NOT NULL, PRIMARY KEY ("orgId", "patientId", "deviceId") ); ## The root DB: CREATE TABLE IF NOT EXISTS "devices" ( "orgId" TEXT NOT NULL, "patientId" TEXT NOT NULL, "deviceId" TEXT NOT NULL, "data" JSONB NOT NULL, "serialNumber" TEXT NOT NULL GENERATED ALWAYS AS ("data"->>'serialNumber') STORED, "status" TEXT NOT NULL GENERATED ALWAYS AS ("data"->>'status') STORED, PRIMARY KEY ("orgId", "patientId", "deviceId"), CONSTRAINT "deviceIdMatches" CHECK ("data"->>'id' = "deviceId"), CONSTRAINT "patientIdMatches" CHECK ("data"->>'patientId' = "patientId"), -- Prevent duplicate serial numbers that are simultaneously "active" EXCLUDE ( "serialNumber" WITH = ) WHERE ("status" = 'active') ); ## FDW connection from tenant DB to the root: CREATE EXTENSION IF NOT EXISTS postgres_fdw; CREATE SERVER IF NOT EXISTS "fdw_server__root" FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( host '${instance-ip-address}', dbname '${root-db}', updatable 'true', truncatable 'false', keep_connections 'off' ); CREATE USER MAPPING FOR "${remote-user}" SERVER "fdw_server__root" OPTIONS( user '${remote-user}', password '$${PGPASSWORD}' ); CREATE SCHEMA IF NOT EXISTS "rootDb"; GRANT USAGE ON SCHEMA "rootDb" TO "${user-name}"; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA "rootDb" TO "${user-name}"; ALTER DEFAULT PRIVILEGES IN SCHEMA "rootDb" GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO "${user-name}"; IMPORT FOREIGN SCHEMA "public" LIMIT TO (devices) FROM SERVER "fdw_server__root" INTO "rootDb"; ## Trigger setup on the tenant DB, hoisting rows when modified into the root table: -- Set up a trigger which hoists tenant devices into the rootDb CREATE OR REPLACE FUNCTION hoist_devices() RETURNS TRIGGER AS $hoist_devices$ BEGIN IF (TG_OP IN ('UPDATE', 'DELETE')) THEN DELETE FROM "rootDb"."devices" WHERE "orgId" = OLD."orgId" AND "patientId" = OLD."patientId" AND "deviceId" = OLD."deviceId"; END IF; IF (TG_OP IN ('INSERT', 'UPDATE')) THEN INSERT INTO "rootDb"."devices" ("orgId", "patientId", "deviceId", "data") SELECT NEW."orgId", NEW."patientId", NEW."deviceId", NEW."data"; END IF; RETURN NEW; END; $hoist_devices$ LANGUAGE plpgsql; CREATE OR REPLACE TRIGGER hoist_devices_insert_update_delete AFTER INSERT OR UPDATE OR DELETE ON "devices" FOR EACH ROW EXECUTE FUNCTION hoist_devices(); ## A particular endpoint attempts insertions like: BEGIN; INSERT INTO "devices" ("orgId", "patientId", "deviceId", "data") VALUES ( 'org1', 'patient1', 'device1', '{"id": "device1", "patientId": "patient1", "serialNumber": "12345", "status": "active" }' ); COMMIT; -- Jake Biesinger --000000000000fa98c90620b3ecaf Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi there!

We have a setup where, for co= mpliance reasons, we hoist a portion of data from several "tenant"= ; databases into a "root" / common / untenanted DB. Through the m= agic of postgres_fdw, row triggers, and distributed transactions, we automa= tically hoist the needed columns into the untenanted DB whenever there are = changes in any of the tenant DBs. The root DB is never written to outside o= f these triggers, and act as a sort of cross-tenant index.
<= div dir=3D"ltr" class=3D"gmail_signature">

I'm scratching my head at a few = rows in the root DB, where it seems the corresponding tenant transaction ro= lled back, but the root DB transaction committed -- there is no row in the = tenant but the root DOES have one. I don't have a smoking gun just yet,= but this is not the first time we've seen this issue.

Before I jump into particulars, does this sound like expected beha= vior? We run SERIALIZABLE txn level everywhere (set at the cluster level).<= /div>


Thanks so much in advance for = any insights here!


Here's my setup:

## info + cluster-le= vel flags
GCP cloudsql
postgres version: 15.7
default_tr= ansaction_isolation: serializable


<= div>## The tenant DB:

CREATE TABLE
"devices" (
"orgId" TEXT NOT NULL,
<= div> "patientId"= TEXT NOT NULL,
"deviceId" = TEXT NOT NULL,
"data" JSONB = NOT NULL,
PRIMARY KEY ("orgId", "patientId", = "deviceId")
);

## The root DB:

CREATE TABLE IF NOT EXISTS "= ;devices" (
"orgId" TEXT NOT NULL,
"patientId" TEXT NOT NULL,
&qu= ot;deviceId" TEXT = NOT NULL,
"data" JSONB NOT NULL,
"serialNumber" TEXT NOT NULL= GENERATED ALWAYS AS ("data"->>'serialNumber') STORED,
<= span style=3D"color:rgb(206,145,120)">"status" TEXT NOT NULL GENERATED ALWAYS AS ("data&q= uot;->>'status') STORED,
= PRIMARY KEY ("orgId", "patientId", "deviceId"),

CONSTRAINT "deviceIdMatches" C= HECK ("data"= ->>'id' =3D "deviceId"),
CONSTRAINT "patientIdMatches" CHECK ("data"->>'patientId= ' =3D "patientId"),

= -- Prevent duplicate serial numbers= that are simultaneously "active"
EXCLUDE ( = "serialNumber" WITH =3D ) WHERE (= "status" =3D 'active')
);

<= div>
## FDW connection from tenant DB to the root:
=
CREATE EXTENSION IF NOT EXISTS postgres_fdw;

CREATE = SERVER IF NOT EXISTS "fdw_server__roo= t" FOREIGN DATA WR= APPER postgres_fdw OPTIONS (
host '${instance-ip-address}',
dbname <= span style=3D"color:rgb(206,145,120)">'${root-db}',
updatable 'true',
truncatable 'fa= lse',
keep_connections 'off'
);

CREA= TE USER MAPPING FOR "${remote-user}&qu= ot; SERVER "fdw_server__root" OPTIONS(
user '${remote-user}= ', password '$${PGPASSWORD}'
)= ;

CREATE SCHEMA IF NOT EXISTS "rootDb";

GRANT USAGE ON SCHEMA "rootDb" TO "${user-na= me}";
GRANT= SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA "rootDb" TO "${user-name}";
ALTER DEFAULT PRIVILEGES IN SCHEMA "rootDb" GRANT SELECT, INSERT, UP= DATE, DELETE ON TABLES TO "${user-name}= ";


IMPORT FOREIGN SCHEM= A "public" <= span style=3D"color:rgb(86,156,214)">LIMIT TO (devices)
FROM SERVER <= span style=3D"color:rgb(206,145,120)">"fdw_server__root"
INTO "rootDb";


## Trigger setup on= the tenant DB, hoisting rows when modified into the root table:
=
-- Set up a trigger which hoists tenant devices into the rootDb
CREATE OR REPLACE FUNCTION hoist_devices() RETURNS TRIGGER
AS $hoist_devices$
BEGIN
IF (TG_OP IN ('UPDATE', 'DELETE')) THEN
DELETE<= /span> FROM "rootDb"."devices"
WHERE &quo= t;orgId" =3D OLD.= "orgId"
= AND "patientId" =3D OLD."= patientId"
AND "deviceId"= ; =3D OLD."deviceId";
END IF;

= IF (TG_OP IN ('INSERT', 'UPDATE')) THEN
I= NSERT INTO "rootDb"= ."devices" (= "orgId", "patientId", "deviceId", "data")
SELECT
NEW."orgId",
NEW.<= span style=3D"color:rgb(206,145,120)">"patientId",
NEW."deviceId"= ;,
NEW.&q= uot;data";
= END IF;

= RETURN NEW;
END;
$hoist_devices$ LANGUAGE plpgsql;

= CREATE OR REPLACE TRIGGER hoist_devices_insert_update_delete
AFTER I= NSERT OR UPDATE OR DELETE ON "devices"
FOR EACH ROW
=
EXECUTE FUNCTION hoist_devices();

=


## A particular endpoint attempts = insertions like:

BEGIN;

INSERT INTO "devices"<= /span> ("orgId", "patientId", "deviceId", "data")
VALUES (
'org1',
'patient1',
= 'device1',
'{"id": &qu= ot;device1", "patientId": "patient1", "serial= Number": "12345", "status": "active" }&#= 39;
);

COMMIT;




--<= br>Jake Biesinger
--000000000000fa98c90620b3ecaf--