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 1rhToG-00Bfzi-Tb for pgsql-general@arkaria.postgresql.org; Tue, 05 Mar 2024 12:22:17 +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 1rhTnH-00GWCA-54 for pgsql-general@arkaria.postgresql.org; Tue, 05 Mar 2024 12:21:15 +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 1rhTnG-00GWC2-O5 for pgsql-general@lists.postgresql.org; Tue, 05 Mar 2024 12:21:15 +0000 Received: from mail-wm1-x335.google.com ([2a00:1450:4864:20::335]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rhTnB-002tKO-5B for pgsql-general@postgresql.org; Tue, 05 Mar 2024 12:21:14 +0000 Received: by mail-wm1-x335.google.com with SMTP id 5b1f17b1804b1-412eeb018ffso2964775e9.1 for ; Tue, 05 Mar 2024 04:21:09 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1709641268; x=1710246068; darn=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=S4Uc+vaGF2c3Xpb/35E/ArPaHHRlUvkO+ng0+oZu0AI=; b=IUjseeUDQaiR+kjTDgrcNplq/MZVmG5hERLtAeqmXd8OQtlzAOTLFG6KnIxYvdKk8o 5mLFXwT8qS+sGyluPiKtdtf7XhsXi/gLnj/1TbVclyapxJikOJJBcymJJkaToG/hODT6 xRtJP21k5mfS4h+nD5N7Qd0tVs8/G8oh5hw5pFiQjxvZU19PI7tOWBi2dhs6y2eB0bgZ BY5uzaLeUjxUFxkg0YhBzzgXAV6Jx8ug972sYs6tRb3Y3llHlvN3TRryFoFYxP1N8gds YuJ73ufhXRyNFxFUkNULPQeX8BTS14eTHMZdb67B+WXh6/TwtdHe/3X5yIaBsjSY0x6+ IPhg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1709641268; x=1710246068; 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=S4Uc+vaGF2c3Xpb/35E/ArPaHHRlUvkO+ng0+oZu0AI=; b=d+VFlXW46Ocw9M0vU9zLXVRX9+aUbkRracSiDgkZusVUuaXgl9UCaT9CWV+zpo+bMk hoxVUwWdZwl6itWQl/OWmQHtYy5BaEydjJ0fB0uUT08rIk4sJZKrxAtDplbfm1/J2Wiy xuJtJdNZWpvQUdQ2au12yctELubX8nM1IWY5gKFmjH84X2WNDkUr9oj1NOZWOoh0cLL+ stTKz5ujInrqa3iDGjsKBcsBdy0TvA8PKUghgm5kG5LCCb54rWTc2whi0IK1tbuvWiCJ 7q2ZNgOD8KqddvdACFJD7MwgUWDP1KmRzwDdfMtAEk6f/r7F5TJx4YNcS5u+RRiXrRyJ 383Q== X-Gm-Message-State: AOJu0YwEMZXPMv+6DUXPX6PIJ0J6UfU4K1P2PSlRw1+1qwrdc1e299X9 cbCjXTgq7Pf2SiPyEW9gbEr3/W/+SpG4/6HjLsZLcpyfOFZGKbQM X-Google-Smtp-Source: AGHT+IFJZkhxvpNKEMNq3qke0e1p7s9/UyVNLsRdPiCPRGD0XCC7nH6yMGSMXJn/8+64vga/xNyt1Q== X-Received: by 2002:a05:600c:5191:b0:412:f02c:cdb2 with SMTP id fa17-20020a05600c519100b00412f02ccdb2mr209019wmb.20.1709641267637; Tue, 05 Mar 2024 04:21:07 -0800 (PST) Received: from smtpclient.apple (ptr-9zrqi3yw7ykj6go9ffl.18120a2.ip6.access.telenet.be. [2a02:1811:4d10:8500:adb0:6c2e:365d:2e61]) by smtp.gmail.com with ESMTPSA id n6-20020a05600c500600b00412ee8e2f2asm1293105wmr.9.2024.03.05.04.21.07 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Tue, 05 Mar 2024 04:21:07 -0800 (PST) From: Brecht De Rooms Message-Id: <869429E2-3D8F-4F25-957E-877113152643@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_8823811B-A3C0-4D90-9B86-E450B15D3BE9" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.100.2.1.4\)) Subject: Re: CTEs and concurrency Date: Tue, 5 Mar 2024 13:21:01 +0100 In-Reply-To: Cc: pgsql-general@postgresql.org To: Brecht De Rooms References: X-Mailer: Apple Mail (2.3774.100.2.1.4) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_8823811B-A3C0-4D90-9B86-E450B15D3BE9 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii My apologies, when I said:=20 "When logging the input values, we can see that there are indeed two = times the same value sets (exactly the same)" It seems I was wrong, the UUIDs differed on 1-2 characters which means = that the problem lies elsewhere in the system. It does seem that my = assumptions of how this query should behave are correct and that the = problem lies elsewhere. > On Mar 4, 2024, at 22:44, Brecht De Rooms = wrote: >=20 > Dear,=20 >=20 > I am currently running a chaos test on a system (essentially starting = nodes that process something and randomly knockign them out). It = appeared to work fine with regular tests but I am seeing occasional = duplicate key value violattions of a uniqueness constraint on one of the = complexer CTE-based queries. Something that only happens with = concurrency where nodes restart and ample load. > I can not reproduce it by taking out the query and running it manually = in PG Admin, it behaves fine if I do so and does exactly what I expect.=20= >=20 > The query looks like this (it uses Rust SQLX which is why there is = some unnesting happening on the parameters).=20 >=20 > WITH unnested_inputs AS ( > SELECT * FROM ( > SELECT > unnest($1::uuid[]) AS event_id, > unnest($2::varchar[]) AS type, > unnest($3::int[]) AS version, > unnest($4::uuid[]) AS causation_id, > unnest($5::uuid[]) AS correlation_id, > unnest($6::text[]) AS idempotency_key, > unnest($7::jsonb[]) AS data, > unnest($8::jsonb[]) AS metadata, > unnest($9::text[]) AS subscription_id, > unnest($10::text[]) AS subscription_instance_identifier, > unnest($11::bigint[]) AS applied_order_id > ) AS inputs > ), > to_update_subscription_logs AS ( > SELECT sl.id as subscription_log_id, sl.node_id, = sl.status, ui.* > FROM subscription_log sl > JOIN unnested_inputs ui > ON sl.event_id =3D ui.causation_id > AND sl.node_id =3D $12 > AND sl.status =3D 'assigned' > AND sl.subscription_id =3D ui.subscription_id > AND sl.subscription_instance_identifier =3D = ui.subscription_instance_identifier > FOR UPDATE NOWAIT -- if something is updating it, we probably = shouldn't touch it anymore. > ), > updated_logs AS ( > UPDATE subscription_log sl > SET status =3D 'processed', > updated_at =3D CURRENT_TIMESTAMP > FROM to_update_subscription_logs usl > WHERE sl.id =3D usl.subscription_log_id > AND usl.node_id =3D $12 > ), > inserted_event_log AS ( > INSERT INTO event_log ( > event_id, type, version, causation_id, correlation_id, > idempotency_key, data, metadata, created_at > ) > SELECT > event_id, type, version, usl.causation_id, correlation_id, > idempotency_key, data, metadata, CURRENT_TIMESTAMP > FROM to_update_subscription_logs usl > ), > inserted_output_routing_info AS ( > INSERT INTO output_event_routing (event_id, subscription_id, = subscription_instance_identifier, applied_order_id) > SELECT event_id, subscription_id, = subscription_instance_identifier, applied_order_id > FROM to_update_subscription_logs usl > ), >=20 > SELECT * FROM to_update_subscription_logs >=20 > The tables look as follows: >=20 > CREATE TABLE event_log ( > event_id UUID PRIMARY KEY, > event_order_id BIGINT REFERENCES event(order_id), > type varchar NOT NULL, > version int NOT NULL, > causation_id UUID, > correlation_id UUID, > idempotency_key TEXT NOT NULL, > data JSONB NOT NULL, > metadata JSONB, > created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP, > CONSTRAINT constraint_event_log_unique_idempotency_key = UNIQUE(idempotency_key) -- idempotent writes. > ); >=20 > CREATE TABLE output_event_routing ( > event_id UUID REFERENCES event_log(event_id), > subscription_id TEXT NOT NULL, =20 > subscription_instance_identifier TEXT, =20 > applied_order_id BIGINT, =20 > CONSTRAINT constraint_output_event_routing_uniqueness = UNIQUE(subscription_id, subscription_instance_identifier, = applied_order_id) > ); >=20 > CREATE TABLE subscription_log ( > id UUID NOT NULL PRIMARY KEY, > event_id UUID NOT NULL, =20 > event_order_id BIGINT NOT NULL, =20 > event_correlation_id UUID NOT NULL, =20 > subscription_instance_identifier TEXT NOT NULL, > subscription_id TEXT NOT NULL REFERENCES subscription(name), > status processing_status NOT NULL DEFAULT 'enqueued', > node_id UUID references node(id), -- is null until assigned. > ); >=20 > Since I'm trying to avoid using PL/pgSQL upon request I tried to = achieve the following behaviour in CTEs:=20 > - For given events, update the subscription log to 'processed' only if = we still are the node that is processing these and the status is still = 'assigned'. > - Only for the events where the previous succeeded, continue = processing by inserting in the event_log and inserting in the = inserted_output_routing_info.=20 > The mechanism aims to make sure we don't insert results of event = processing twice.=20 >=20 > When logging the input values, we can see that there are indeed two = times the same value sets (exactly the same) passed for different nodes, = that's to be expected and exactly what has to be caught by this logic. = Same values, but another node. What we see is that one node succeeds and = the other node fails due to the uniqueness violation. Which is actually = fine from a business perspective since rolling back has the same effect, = albeit with an error that I didn't expect. However, I would love to = understand this, how can one node succeed and set the status of the log = to 'processed' and continue to insert the values while the other, = apparently also is able to continue inserting (which means that both = nodes saw 'assigned' in the select when it locked it for update). Is = there something I do not fully understand about how CTEs work in = combination with locks? >=20 > Things I tried:=20 > 1. Whether I go for regular FOR UPDATE, SKIP LOCK or NOWAIT makes no = difference.=20 > 2. I do return the to_update_subscription_logs at the end to be sure = the lock is held (I'm aware of that CTE behaviour for selects) even if = it's used by the next CTE.=20 >=20 > 3. Changing it to UPDATE/RETURN (which was my original logic)=20 > updated_subscription AS ( > UPDATE subscription_log sl > SET status =3D 'processed', > updated_at =3D CURRENT_TIMESTAMP > FROM unnested_inputs ui > WHERE sl.event_id =3D ui.causation_id > AND sl.node_id =3D $12 > AND sl.status =3D 'assigned' -- Assuming you're updating from = 'assigned' > RETURNING ui.causation_id > ) >=20 > then doing a distinct on the causation ID and only allow inserts for = values that have a causation ID that was successfully updated to = processed yields exactly the same behaviour, everything goes fine but = once in a while things go wrong when a node dies and another takes over.=20= >=20 > 4. I also tried with both approaches to do more explicit checks in the = joins when we insert to make sure that in the insert CTEs the node is = still the same etc, things that shouldn't be necessary, they also didn't = change anything. >=20 > 5. After the error we can see that the succeeding node successfully = set the status to process and that it 'owns' the subscription_log entry = for that processed event. Which means the other node should not have = been able to get passed the lock.=20 >=20 > If anyone could provide some insight of what knowledge I'm missing = about how CTEs work that would be amazing. Sorry that it's a rather = complex case which makes it hard to come up with something 'smaller' = with the same tests that reproduce it.=20 --Apple-Mail=_8823811B-A3C0-4D90-9B86-E450B15D3BE9 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii My = apologies, when I said: 
"When logging the input = values, we can see that there are indeed two times
the = same value sets (exactly the same)"

It = seems I was wrong, the UUIDs differed on 1-2 characters which means that = the problem lies elsewhere in the system. It does seem that my = assumptions of how this query should behave are correct and that the = problem lies elsewhere.


On Mar 4, 2024, at 22:44, Brecht De Rooms = <databrecht@gmail.com> wrote:

Dear, 

I am currently running a = chaos test on a system (essentially starting nodes that process = something and randomly knockign them out). It appeared to work fine with = regular tests but I am seeing occasional duplicate key value violattions = of a uniqueness constraint on one of the complexer CTE-based queries. = Something that  only happens with concurrency where nodes restart = and ample load.
I can not reproduce it by taking out the query = and running it manually in PG Admin, it behaves fine if I do so and does = exactly what I expect. 

The query looks like this (it uses = Rust SQLX which is why there is some unnesting happening on the = parameters). 

WITH unnested_inputs AS (
    SELECT * = FROM (
        SELECT
      =       unnest($1::uuid[]) AS event_id,
    =         unnest($2::varchar[]) AS type,
  =           unnest($3::int[]) AS = version,
            unnest($4::uuid[]) = AS causation_id,
            = unnest($5::uuid[]) AS correlation_id,
        =     unnest($6::text[]) AS idempotency_key,
    =         unnest($7::jsonb[]) AS data,
  =           unnest($8::jsonb[]) AS = metadata,
            = unnest($9::text[]) AS subscription_id,
        =     unnest($10::text[]) AS = subscription_instance_identifier,
          =   unnest($11::bigint[]) AS applied_order_id
    ) AS = inputs
),
to_update_subscription_logs AS (
    SELECT = sl.id as subscription_log_id, sl.node_id, = sl.status, ui.*
    FROM subscription_log sl
  =   JOIN unnested_inputs ui
    ON sl.event_id =3D = ui.causation_id
    AND sl.node_id =3D $12
    = AND sl.status =3D 'assigned'
    AND sl.subscription_id =3D = ui.subscription_id
    AND = sl.subscription_instance_identifier =3D = ui.subscription_instance_identifier
    FOR UPDATE NOWAIT = -- if something is updating it, we probably shouldn't touch it = anymore.
),
updated_logs AS (
    UPDATE = subscription_log sl
    SET status =3D = 'processed',
    updated_at =3D CURRENT_TIMESTAMP
  =   FROM to_update_subscription_logs usl
    WHERE sl.id =3D usl.subscription_log_id
  =       AND usl.node_id =3D $12
),
inserted_event_log = AS (
    INSERT INTO event_log (
      =   event_id, type, version, causation_id, correlation_id,
  =       idempotency_key, data, metadata, = created_at
    )
    SELECT
    =     event_id, type, version, usl.causation_id, = correlation_id,
        idempotency_key, data, = metadata, CURRENT_TIMESTAMP
    FROM = to_update_subscription_logs usl
),
inserted_output_routing_info AS = (
    INSERT INTO output_event_routing (event_id, = subscription_id, subscription_instance_identifier, = applied_order_id)
    SELECT event_id, subscription_id, = subscription_instance_identifier, applied_order_id
    FROM = to_update_subscription_logs usl
),

SELECT * FROM = to_update_subscription_logs

The tables look as = follows:

CREATE TABLE event_log = (
    event_id UUID PRIMARY KEY,
    = event_order_id BIGINT REFERENCES event(order_id),
    type = varchar NOT NULL,
    version int NOT NULL,
  =   causation_id UUID,
    correlation_id = UUID,
    idempotency_key TEXT NOT NULL,
    = data JSONB NOT NULL,
    metadata JSONB,
    = created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    = CONSTRAINT constraint_event_log_unique_idempotency_key = UNIQUE(idempotency_key) -- idempotent writes.
);

CREATE TABLE output_event_routing (
    = event_id UUID REFERENCES event_log(event_id),
    = subscription_id TEXT NOT NULL,        =   
    subscription_instance_identifier = TEXT,  
    applied_order_id BIGINT,    =                   =           
    CONSTRAINT = constraint_output_event_routing_uniqueness UNIQUE(subscription_id, = subscription_instance_identifier, = applied_order_id)
);

CREATE = TABLE subscription_log (
    id UUID NOT NULL PRIMARY = KEY,
    event_id UUID NOT NULL,        =       
    event_order_id BIGINT NOT = NULL,     
    event_correlation_id UUID NOT = NULL,  
    subscription_instance_identifier TEXT = NOT NULL,
    subscription_id TEXT NOT NULL REFERENCES = subscription(name),
    status processing_status NOT NULL = DEFAULT 'enqueued',
    node_id UUID references node(id), =     -- is null until = assigned.
);


Since I'm trying to avoid = using PL/pgSQL upon request I tried to achieve the following = behaviour in CTEs: 
- For given events, update the subscription = log to 'processed' only if we still are the node that is processing = these and the status is still 'assigned'.
- Only for the events where = the previous succeeded, continue processing by inserting in the = event_log and inserting in = the inserted_output_routing_info. 
The mechanism aims to = make sure we don't insert results of event processing = twice. 

When logging the input values, we can see that there = are indeed two times the same value sets (exactly the same) passed for = different nodes, that's to be expected and exactly what has to be caught = by this logic. Same values, but another node. What we see is that one = node succeeds and the other node fails due to the uniqueness violation. = Which is actually fine from a business perspective since rolling back = has the same effect, albeit with an error that I didn't expect. However, = I would love to understand this, how can one node succeed and set the = status of the log to 'processed' and continue to insert the values while = the other, apparently also is able to continue inserting (which means = that both nodes saw 'assigned' in the select when it locked it for = update). Is there something I do not fully understand about how = CTEs work in combination with locks?

Things I tried: 
1. = Whether I go for regular FOR UPDATE, SKIP LOCK or NOWAIT makes no = difference. 
2. I do return the to_update_subscription_logs at = the end to be sure the lock is held (I'm aware of that CTE behaviour for = selects) even if it's used by the next CTE. 

3. = Changing it to UPDATE/RETURN (which was my original = logic) 
updated_subscription AS (
     UPDATE = subscription_log sl
     SET status =3D = 'processed',
            =  updated_at =3D CURRENT_TIMESTAMP
     FROM = unnested_inputs ui
     WHERE sl.event_id =3D = ui.causation_id
     AND sl.node_id =3D $12
  =    AND sl.status =3D 'assigned' -- Assuming you're updating = from 'assigned'
     RETURNING = ui.causation_id
)

then doing a distinct on the causation ID = and only allow inserts for values that have a causation ID that was = successfully updated to processed yields exactly the same behaviour, = everything goes fine but once in a while things go wrong when a node = dies and another takes over. 

4. I also tried with both = approaches to do more explicit checks in the joins when we insert to = make sure that in the insert CTEs the node is still the same etc, things = that shouldn't be necessary, they also didn't change anything.

5. = After the error we can see that the succeeding node successfully set the = status to process and that it 'owns' the subscription_log entry for that = processed event. Which means the other node should not have been able to = get passed the lock. 

If anyone could provide some insight = of what knowledge I'm missing about how CTEs work that would be amazing. = Sorry that it's a rather complex case which makes it hard to come up = with something 'smaller' with the same tests that reproduce = it. 

= --Apple-Mail=_8823811B-A3C0-4D90-9B86-E450B15D3BE9--