From: | Jason Kim <git(at)jasonk(dot)me> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Subject: | a row is not inserted in nested INSERT ON CONFLICT |
Date: | 2024-08-13 04:17:51 |
Message-ID: | 20240813041751.5en3xrye6hqn54sh@jasonk.me |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi,
On versions 15.2 and 16.4:
create table a (i int, j int, k int, t timestamp default (clock_timestamp()), unique (i));
insert into a values (1, 1), (2, 2), (3, 3);
with w(i) as (
insert into a values (1, 100), (2, 200), (3, 300) on conflict (i) do update set i = excluded.i + 10, t = clock_timestamp(), k = 0 returning i
) insert into a values (1, 10), (2, 20) on conflict (i) do update set i = (select (i - 10) from w order by i desc limit 1), t = clock_timestamp(), k = 1;
table a;
gives
INSERT 0 1
and
i | j | k | t
----+----+---+----------------------------
11 | 1 | 0 | 2024-08-12 15:13:56.209344
12 | 2 | 0 | 2024-08-12 15:13:56.209361
13 | 3 | 0 | 2024-08-12 15:13:56.209368
2 | 20 | | 2024-08-12 15:13:56.209375
(4 rows)
The order of executor events from what I can tell is
- start (1, 10)
- start (1, 100)
- end (1, 100)
- start (2, 200)
- end (2, 200)
- start (3, 300)
- end (3, 300)
- end (1, 10)
- start (2, 20)
- end (2, 20)
I find it unusual that the (1, 10) insert seems to be ignored.
Jason
From | Date | Subject | |
---|---|---|---|
Next Message | Jason Kim | 2024-08-13 04:57:39 | Re: FDW INSERT batching can change behavior |
Previous Message | Alvaro Herrera from 2ndQuadrant | 2024-08-12 22:33:09 | Re: BUG #18559: Crash after detaching a partition concurrently from another session |