Re: [PATCH] Make ON CONFLICT DO NOTHING and ON CONFLICT DO UPDATE consistent
От | Aleksander Alekseev |
---|---|
Тема | Re: [PATCH] Make ON CONFLICT DO NOTHING and ON CONFLICT DO UPDATE consistent |
Дата | |
Msg-id | CAJ7c6TOYaovnjJtqr2DjVjrWN2zBc0rpU2jBddAFZC4hxpjQ6Q@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: [PATCH] Make ON CONFLICT DO NOTHING and ON CONFLICT DO UPDATE consistent (Peter Geoghegan <pg@bowt.ie>) |
Ответы |
Re: [PATCH] Make ON CONFLICT DO NOTHING and ON CONFLICT DO UPDATE consistent
(Aleksander Alekseev <aleksander@timescale.com>)
Re: [PATCH] Make ON CONFLICT DO NOTHING and ON CONFLICT DO UPDATE consistent (Andres Freund <andres@anarazel.de>) |
Список | pgsql-hackers |
Hi, > Yes, and the fact is that cmin == cmax is something that we don't normally > produce Not sure if this is particularly relevant to this discussion but I can't resist noticing that the heap doesn't even store cmin and cmax... There is only HeapTupleHeaderData.t_cid and flags. cmin/cmax are merely smoke and mirrors we use to trick a user. And yes, the patch doesn't seem to break much mirrors: ``` =# create table t (a int unique, b int); =# insert into t values (1,1), (1,2) on conflict (a) do update set b = 0; =# SELECT xmin, xmax, cmin, cmax, * FROM t; xmin | xmax | cmin | cmax | a | b ------+------+------+------+---+--- 731 | 0 | 0 | 0 | 1 | 0 =# begin; =# insert into t values (2,1), (2,2), (3,1) on conflict (a) do update set b = 0; =# SELECT xmin, xmax, cmin, cmax, * FROM t; xmin | xmax | cmin | cmax | a | b ------+------+------+------+---+--- 731 | 0 | 0 | 0 | 1 | 0 732 | 0 | 0 | 0 | 2 | 0 732 | 0 | 0 | 0 | 3 | 1 =# insert into t values (2,1), (2,2), (3,1) on conflict (a) do update set b = 0; =# SELECT xmin, xmax, cmin, cmax, * FROM t; xmin | xmax | cmin | cmax | a | b ------+------+------+------+---+--- 731 | 0 | 0 | 0 | 1 | 0 732 | 732 | 1 | 1 | 2 | 0 732 | 732 | 1 | 1 | 3 | 0 =# commit; =# SELECT xmin, xmax, cmin, cmax, * FROM t; xmin | xmax | cmin | cmax | a | b ------+------+------+------+---+--- 731 | 0 | 0 | 0 | 1 | 0 732 | 732 | 1 | 1 | 2 | 0 732 | 732 | 1 | 1 | 3 | 0 ``` > That's a spectactularly wrong argument in almost all cases. Unless you have a > way to get to full branch coverage or use a model checker that basically does > the same, testing isn't going to give you a whole lot of confidence that you > haven't introduced bugs. But neither will reviewing a lot of code... > I've said my piece, as-is I vote to reject the patch. Fair enough. I'm merely saying that rejecting a patch because it doesn't include a TLA+ model is something novel :) > I don't buy your argument about DO UPDATE needing to be brought into > line with DO NOTHING. In any case I'm pretty sure that Tom's remarks > in 2016 about a behavioral inconsistencies (which you cited) actually > called for making DO NOTHING more like DO UPDATE -- not the other way > around. Interesting. Yep, we could use a bit of input from Tom on this one. This of course would break backward compatibility. But we can always invent something like: ``` INSERT INTO .. ON CONFLICT DO [NOTHING|UPDATE .. ] [ALLOWING|FORBIDDING] SELF CONFLICTS; ``` ... if we really want to. > problem. To me the best argument is also the simplest: who would want > us to allow it, and for what purpose? Good question. This arguably has little use for application developers. As an application developer you typically know your unique constraints and using this knowledge you can rewrite the query as needed and add any other accompanying logic. However, extension developers, as an example, often don't know the underlying unique constraints (more specifically, it's difficult to look for them and process them manually) and often have to process any garbage the application developer passes to an extension. This of course is applicable not only to extensions, but to any middleware between the DBMS and the application. -- Best regards, Aleksander Alekseev
В списке pgsql-hackers по дате отправления:
Следующее
От: "Hayato Kuroda (Fujitsu)"Дата:
Сообщение: RE: Exit walsender before confirming remote flush in logical replication