Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On Wed, Nov 19, 2014 at 5:37 PM, Andreas Karlsson <andreas(at)proxel(dot)se> wrote:
> I think we have three options.
>
> 1. Return only inserted tuples
> 2. Return inserted and updated tuples
> 3. Return inserted, updated and skipped tuples
>
> To me option 1 is surprising and less useful since I imagine in most cases
> where you do an upsert you do not care if the tuple was inserted or updated
> as long as it has the right values after the upsert, and these values is
> also what I would expect to be returned.

I can see why you'd say that about option 1. That also seems like an
argument against surfacing the distinction directly (through a
dedicated hidden column or other expressing that RETURNING might
reference, say).

> The possible use case I see for option 3 is when you want the values of
> automatically generated columns but there is actually no work to do if
> another transaction had already inserted the same row (same according to the
> unique constraints). But this behavior even though useful in certain cases
> might be surprising.

I think that 3 is out. It seems hard to justify not RETURNING anything
in respect of a slot when there is a before row insert trigger that
returns NULL on the one hand, but RETURNING something despite not
inserting for ON CONFLICT UPDATE on the other.

I think if we do this, we're also going to have to set a command tag.
That could look like this:

postgres=# INSERT INTO upsert values(1, 'Foo'), (2, 'Bar') ON CONFLICT
(key) UPDATE SET val = EXCLUDED.val;
INSERT 0 1 UPDATE 1

Or perhaps like this:

postgres=# INSERT INTO upsert values(1, 'Foo'), (2, 'Bar') ON CONFLICT
(key) UPDATE SET val = EXCLUDED.val;
UPSERT 0 2

Maybe the latter is better, because it's less likely to break tools
that currently parse the command tag. But if we went with the former
command tag format, we'd have to figure out if there should always be
an "UPDATE part" of INSERT command tags generally, even when there was
no ON CONFLICT UPDATE clause. I guess in that case it would have to
become stable/consistent across INSERTs, so we'd always have an
"UPDATE part", but I'm not sure.

--
Peter Geoghegan