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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers