Re: support for MERGE

From: Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>
To: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Cc: Simon Riggs <simon(dot)riggs(at)enterprisedb(dot)com>, Tomas Vondra <tomas(dot)vondra(at)enterprisedb(dot)com>, Zhihong Yu <zyu(at)yugabyte(dot)com>, Daniel Westermann <dwe(at)dbi-services(dot)com>, Amit Langote <amitlangote09(at)gmail(dot)com>, Justin Pryzby <pryzby(at)telsasoft(dot)com>, Japin Li <japinli(at)hotmail(dot)com>, Erik Rijkers <er(at)xs4all(dot)nl>, Jaime Casanova <jcasanov(at)systemguards(dot)com(dot)ec>
Subject: Re: support for MERGE
Date: 2022-01-27 15:15:27
Message-ID: 202201271515.rtftzh6boln3@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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.


Álvaro Herrera PostgreSQL Developer —
"Las cosas son buenas o malas segun las hace nuestra opinión" (Lisias)

Attachment Content-Type Size
v9-0001-MERGE-SQL-Command-following-SQL-2016.patch text/x-diff 402.2 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2022-01-27 16:05:59 Re: logical decoding and replication of sequences
Previous Message Bharath Rupireddy 2022-01-27 15:07:37 Re: Add checkpoint and redo LSN to LogCheckpointEnd log message