Обсуждение: BUG #13886: When INSERT ON CONFLICT DO UPDATE updates, it returns INSERT rather than UPDATE

The following bug has been logged on the website:

Bug reference:      13886
Logged by:          Randy Westlund
Email address:      rwestlun@gmail.com
PostgreSQL version: 9.5.0
Operating system:   FreeBSD-10.2
Description:

Situation:         When INSERT ON CONFLICT DO UPDATE does an update.

Current behavior:  Postgres returns "INSERT"

Expected behavior: Postgres should return "UPDATE"


Steps:

uvbase=> create table test ( id integer primary key, str text );
CREATE TABLE
uvbase=> insert into test (id, str) values (1, 'insert') on conflict(id) do
update set str = 'update';
INSERT 0 1
uvbase=> select * from test;
 id |  str
----+--------
  1 | insert
(1 row)

uvbase=> insert into test (id, str) values (1, 'insert') on conflict(id) do
update set str = 'update';
INSERT 0 1
uvbase=> select * from test;
 id |  str
----+--------
  1 | update
(1 row)



I believe postgres should be returning "UPDATE" here instead of "INSERT"
because I see no way to know which operation took place.  I believe my only
options at the moment are using a second query or adding a column like
"just_created BOOLEAN DEFAULT true", setting it to false in the UPDATE
clause, and returning it.

On 25/01/16 08:48, rwestlun@gmail.com wrote:
> I believe postgres should be returning "UPDATE" here instead of "INSERT"
> because I see no way to know which operation took place.

That seems a bit short-sighted.  What should happen if one row was added
and one updated?

FWIW, I would've expected to be able to do  RETURNING excluded.foo
which would have been NULL in case of INSERT, and the value from the
updated tuple otherwise.  But that doesn't seem to work.


.m

On Mon, Jan 25, 2016 at 11:00:38AM +0100, Marko Tiikkaja wrote:
> On 25/01/16 08:48, rwestlun@gmail.com wrote:
> > I believe postgres should be returning "UPDATE" here instead of "INSERT"
> > because I see no way to know which operation took place.
>=20
> That seems a bit short-sighted.  What should happen if one row was added=
=20
> and one updated?

That's a good point. I'm not sure what the proper way to determine which
operation took place should be :/

On Sun, Jan 24, 2016 at 11:48 PM,  <rwestlun@gmail.com> wrote:
> I believe postgres should be returning "UPDATE" here instead of "INSERT"
> because I see no way to know which operation took place.  I believe my only
> options at the moment are using a second query or adding a column like
> "just_created BOOLEAN DEFAULT true", setting it to false in the UPDATE
> clause, and returning it.

This is the documented behavior for the command tag. I don't think
that it's workable to expose more information there.

It might be desirable to have a feature that exposed whether an insert
or update occurred in respect of each row projected by RETURNING,
perhaps by using a special expression (you might be able to do
something kludgey with xmax today, but I don't want to recommend that,
even informally).

This is a feature request, not a bug. Please post feature requests to
the -general list.

--
Peter Geoghegan

On Mon, Jan 25, 2016 at 2:00 AM, Marko Tiikkaja <marko@joh.to> wrote:
> FWIW, I would've expected to be able to do  RETURNING excluded.foo which
> would have been NULL in case of INSERT, and the value from the updated tuple
> otherwise.  But that doesn't seem to work.

The problem with that approach is that it makes both the target table
and the excluded pseudo table visible from within RETURNING. If we
were to do that, virtually every use of INSERT with both an ON
CONFLICT DO UPDATE clause and a RETURNING clause breaks. That's
because any unqualified column reference becomes ambiguous ("Did you
mean target.foo or excluded.foo?").

I was against doing this during the development of ON CONFLICT DO
UPDATE, because it would have introduced a surprising inconsistency
between INSERT statements (that use RETURNING) with and without the
new clause. Compatibility with 9.5 certainly seals that decision now.

--
Peter Geoghegan

On 2016-01-25 8:35 PM, Peter Geoghegan wrote:
> On Mon, Jan 25, 2016 at 2:00 AM, Marko Tiikkaja <marko@joh.to> wrote:
>> FWIW, I would've expected to be able to do  RETURNING excluded.foo which
>> would have been NULL in case of INSERT, and the value from the updated tuple
>> otherwise.  But that doesn't seem to work.
>
> The problem with that approach is that it makes both the target table
> and the excluded pseudo table visible from within RETURNING. If we
> were to do that, virtually every use of INSERT with both an ON
> CONFLICT DO UPDATE clause and a RETURNING clause breaks. That's
> because any unqualified column reference becomes ambiguous ("Did you
> mean target.foo or excluded.foo?").

Surely there's a way to make this work so that EXCLUDED is a special
tuple whose fields are normally not in scope, but can be accessed
explicitly.

But this discussion belongs to -HACKERS, as you said upthread.


.m

On Mon, Jan 25, 2016 at 11:46 AM, Marko Tiikkaja <marko@joh.to> wrote:
>> The problem with that approach is that it makes both the target table
>> and the excluded pseudo table visible from within RETURNING. If we
>> were to do that, virtually every use of INSERT with both an ON
>> CONFLICT DO UPDATE clause and a RETURNING clause breaks. That's
>> because any unqualified column reference becomes ambiguous ("Did you
>> mean target.foo or excluded.foo?").
>
>
> Surely there's a way to make this work so that EXCLUDED is a special tuple
> whose fields are normally not in scope, but can be accessed explicitly.

Perhaps, but that seems kind of invasive. I don't think that the
contents of EXCLUDED is necessarily interesting enough to be able to
project via RETURNING. There isn't that much new information to be
found in EXCLUDED.* in general. There are other details like that that
are also in general not visible from RETURNING, involving before
triggers, for example.

--
Peter Geoghegan