Horton

Re: support for MERGE

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Simon Riggs <simon.riggs@enterprisedb.com>, Tomas Vondra <tomas.vondra@enterprisedb.com>, Zhihong Yu <zyu@yugabyte.com>, Daniel Westermann <dwe@dbi-services.com>, Amit Langote <amitlangote09@gmail.com>, Justin Pryzby <pryzby@telsasoft.com>, Japin Li <japinli@hotmail.com>, Erik Rijkers <er@xs4all.nl>, Jaime Casanova <jcasanov@systemguards.com.ec>
Date: 2022-01-27T15:15:27Z
Lists: pgsql-hackers

Attachments

Here's MERGE v9.

The main difference in this version is that I have changed the way MERGE
is processed at parse analysis.  In previous versions, a fake JOIN was
constructed at that point; this was critiziced a long time ago ([1] is
an example, but IIRC there were others) and had not been addressed.

The new code is ~30 lines shorter.  I think those can be attributed to
comments explaining why the previous thing was so strange; with the new
code we don't need to explain as much.

In this rewrite, the two relations (target and source) are preserved and
passed down separately, and the JOIN is constructed in early optimizer.
This is what was suggested in those earlier sub-threads.

The new code looks a bit simpler, though some things are not completely
clear to me, such as why it works even though we have an empty
'joinaliasvars' for that join.  Another odd thing is the way we pass the
join condition from parse analysis to optimizer.  In this code we're
using the regular 'jointree' to store the source relation, and the
'quals' there refer to both the source relation and the target relation
-- which is not in the jointree.  Later at optimizer time we swap that
jointree out with the manufactured one; and the quals are moved one
layer down.  So for a brief time, the quals can refer to Vars that are
not part of the rangetable they are attached to.

I still have some things to clean up, but it seems worth sharing at
this point as the remaining items that I'm aware of are pretty minor.

[1] https://www.postgresql.org/message-id/CA%2BTgmoZj8fyJGAFxs%3D8Or9LeNyKe_xtoSN_zTeCSgoLrUye%3D9Q%40mail.gmail.com

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"Las cosas son buenas o malas segun las hace nuestra opinión" (Lisias)

Commits

  1. Doc fixes for MERGE statement

  2. MERGE docs adjustments

  3. Link to MVCC docs in MERGE docs

  4. Fix EXPLAIN MERGE output when no tuples are processed

  5. Make EXPLAIN MERGE output format more compact

  6. Add support for MERGE SQL command

  7. Split ExecUpdate and ExecDelete into reusable pieces

  8. Add API of sorts for transition table handling in trigger.c

  9. Represent Lists as expansible arrays, not chains of cons-cells.