From: | Andres Freund <andres(at)anarazel(dot)de> |
---|---|
To: | Aleksander Alekseev <aleksander(at)timescale(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Peter Geoghegan <pg(at)bowt(dot)ie> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: [PATCH] Make ON CONFLICT DO NOTHING and ON CONFLICT DO UPDATE consistent |
Date: | 2023-01-25 18:34:47 |
Message-ID: | 20230125183447.zx3hqd2cui6joiel@awork3.anarazel.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
On 2023-01-25 18:45:12 +0300, Aleksander Alekseev wrote:
> Currently we allow self-conflicting inserts for ON CONFLICT DO NOTHING:
>
> ```
> CREATE TABLE t (a INT UNIQUE, b INT);
> INSERT INTO t VALUES (1,1), (1,2) ON CONFLICT DO NOTHING;
> -- succeeds, inserting the first row and ignoring the second
> ```
> ... but not for ON CONFLICT .. DO UPDATE:
>
> ```
> INSERT INTO t VALUES (1,1), (1,2) ON CONFLICT (a) DO UPDATE SET b = 0;
> ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
> HINT: Ensure that no rows proposed for insertion within the same
> command have duplicate constrained values.
> ```
>
> Tom pointed out in 2016 that this is actually a bug [1] and I agree.
I don't think I agree with this being a bug.
We can't sensible implement updating a row twice within a statement - hence
erroring out for ON CONFLICT DO UPDATE affecting a row twice. But what's the
justification for erroring out in the DO NOTHING case? ISTM that it's useful
to be able to handle such duplicates, and I don't immediately see what
semantic confusion or implementation difficulty we avoid by erroring out.
It seems somewhat likely that a behavioural change will cause trouble for some
of the uses of DO NOTHING out there.
Greetings,
Andres Freund
From | Date | Subject | |
---|---|---|---|
Next Message | Dimos Stamatakis | 2023-01-25 18:38:55 | pg_upgrade from PG-14.5 to PG-15.1 failing due to non-existing function |
Previous Message | Andres Freund | 2023-01-25 18:27:52 | Re: heapgettup() with NoMovementScanDirection unused in core? |