a row is not inserted in nested INSERT ON CONFLICT

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

Responses

Browse pgsql-bugs by date

  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