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
limit1), 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
