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

Hi,

On 2014-07-17 18:18:41 -0700, Peter Geoghegan wrote:
> I'm working on UPSERT again. I think that in order to make useful
> progress, I'll have to find a better way of overcoming the visibility
> issues (i.e. the problem of what to do about a
> still-in-progress-to-our-snapshot row being locked at READ COMMITTED
> isolation level [1][2]).

Agreed.

> I've made some tentative additions to my earlier patch to change the syntax:
>
> postgres=# explain analyze
> with c as (
> insert into ints(key, val) values (1, 'a'), (2, 'b')
> on conflict
> select * for update)
> update ints set val = c.val from c conflicts;

I still don't agree that this is a sane syntax for upsert. Avoiding it
would reduce the scope of the problems you have measureably. We should
provide a syntax that does the UPSERT itself, instead of delegating that
to the user as you're proposing above. Afaics nearly none of the
problems you're debating in your email would exist if upsert were
entirely done internally.

I think the things that use "wierd" visibility semantics are pretty much
all doing that internally (things being EvalPlanQual stuff for
INSERT/UPDATE/DELETE and the referential integrity triggers). I don't
see sufficient reason why we should break away from that here. Yes,
there's stuff that cannot be done when it's done internally, but we can
live with those not being possible.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services