Re: Making joins involving ctid work for the benefit of UPSERT

Robert Haas wrote:
> On Wed, Jul 23, 2014 at 4:05 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> > On Wed, Jul 23, 2014 at 9:58 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:

> >> 2. If you find more than one tuple that is visible to your scan, error.
> >
> > This point seems to concern making the UPSERT UPDATE only affect zero
> > or one rows. Is that right? If so, why do you think that's a useful
> > constraint to impose?
>
> Because nobody wants an operation to either insert 1 tuple or update
> n>=1 tuples. The intention is that the predicate should probably be
> something like WHERE unique_key = 'some_value', but you can use
> something else. So it's kinda like saying which index you care about
> for a particular operation, except without having to explicitly name
> an index. But in any event you should use a predicate that uniquely
> identifies the tuple you want to update.

This seemed a nice idea when I first read it earlier today, but now I'm
not so sure. Are you saying that it wouldn't be allowed to use an
UPSERT with some sort of join, such that each joined row would produce
either one insert or one update? To clarify: suppose I import some
external data into a temp table, then run UPSERT "USING" that table so
that the rows end up in a permanent table; some of the rows might be
already in the permanent table, some others might not. I would hope
that by the time UPSERT is done, all the rows are in the permanent
table. Would that raise an error, with your proposed design?

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services