Re: Promise index tuples for UPSERT

On Wed, 2014-10-08 at 02:22 -0700, Peter Geoghegan wrote:
> On Wed, Oct 8, 2014 at 1:25 AM, Heikki Linnakangas
> <hlinnakangas(at)vmware(dot)com> wrote:
> > Instead of naming the index, you should name the columns, and the system can
> > look up the index or indexes that match those columns.
>
> It's not totally clear that we need *any* WITHIN clause, BTW. I'm not
> dead set on it. It was something I mainly added at Kevin's request. I
> do see the risks, though.

To be usable in Django ORM's .save() method there must be an option to
use the primary key index, and only the primary key index for conflict
resolution.

Assume an author table with id SERIAL PRIMARY KEY, email TEXT UNIQUE,
age INTEGER, then when saving an object, Django must update the row with
matching primary key value, or otherwise do an insert. Doing an update
of matching email column isn't allowed. So, Django can't use the query:

INSERT INTO author values(1, 'tom(at)example(dot)com', 35)
ON CONFLICT UPDATE
SET email = conflicting(email), age = conflicting(age);

If the table contains data (id=2, email='tom(at)example(dot)com', age=34), the
query would update tom's age to 35 when it should have resulted in
unique constraint violation.

Other ORMs have similar save/persist implementations, that is objects
are persisted on primary key identity alone.

- Anssi