Re: [HACKERS] MERGE SQL Statement for PG11
От | Nico Williams |
---|---|
Тема | Re: [HACKERS] MERGE SQL Statement for PG11 |
Дата | |
Msg-id | 20171030183649.GM4496@localhost обсуждение исходный текст |
Ответ на | Re: [HACKERS] MERGE SQL Statement for PG11 (Peter Geoghegan <pg@bowt.ie>) |
Список | pgsql-hackers |
On Mon, Oct 30, 2017 at 10:59:43AM -0700, Peter Geoghegan wrote: > On Mon, Oct 30, 2017 at 6:21 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > > If a general purpose solution exists, please explain what it is. > > For the umpteenth time, a general purpose solution is one that more or > less works like an UPDATE FROM, with an outer join, whose ModifyTable > node is capable of insert, update, or delete (and accepts quals for > MATCHED and NOT matched cases, etc). You could still get duplicate > violations due to concurrent activity in READ COMMITTED mode, but not > at higher isolation levels thanks to Thomas Munro's work there. In > this world, ON CONFLICT and MERGE are fairly distinct things. FWIW, and as an outsider, having looked at MERGE docs from other RDBMSes, I have to agree that the PG UPSERT (ON CONFLICT .. DO) and MERGE are rather different beasts. In particular, I suspect all UPSERT statements can be mapped onto equivalent MERGE statements, but not all MERGE statements can be mapped onto UPSERTs. The reason is that UPSERT depends on UNIQUE constraints, whereas MERGE uses a generic join condition that need not even refer to any INDEXes, let alone UNIQUE ones. Perhaps PG's UPSERT can be generalized to create a temporary UNIQUE constraint on the specified in the conflict_target portion of the statement, increasing the number of MERGE statements that could be mapped onto UPSERT. But even then, that would still be a UNIQUE constraint, whereas MERGE does not even imply such a thing. Now, a subset of MERGE (those using equijoins in the ON condition) can be mapped onto UPSERT provided either a suitable UNIQUE index exists (or that PG notionally creates a temporary UNIQUE constraint for the purpose of evaluating the UPSERT). This approach would NOT preclude a more complete subsequent implementation of MERGE. But I wonder if that's worthwhile given that a proper and complete implementation of MERGE is probably very desirable. On a tangentially related note, I've long wanted to have an RDBMS- independent SQL parser for the purpose of implementing external query- rewriting (and external optimizers), syntax highlighting, and so on. Having an external / plug-in method for rewriting unsupported SQL as a way of bridging functionality gaps (like lack of MERGE support) would be very nice. PG does have a way to expose its AST... It might be a good idea to start by implementing unsupported SQL features in such a way that they parse and can produce an AST along with a syntax/unsupported error -- then one might rewrite the parsed AST, generate appropriate SQL, and execute that. Nico -- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
В списке pgsql-hackers по дате отправления: