Re: Removing unneeded self joins - Mailing list pgsql-hackers

From jian he
Subject Re: Removing unneeded self joins
Date
Msg-id CACJufxFNDukHU2WG7T61orXdgFJ5gEkSVWcgsiGbb8tRL5=DTQ@mail.gmail.com
Whole thread Raw
In response to Re: Removing unneeded self joins  (jian he <jian.universality@gmail.com>)
Responses Re: Removing unneeded self joins
List pgsql-hackers
On Mon, Jun 17, 2024 at 8:00 AM jian he <jian.universality@gmail.com> wrote:
>
> > Another thing that made me pretty sad was 8c441c082 (Forbid SJE with
> > result relation).  While I don't claim that that destroyed the entire
> > use case for SJE, it certainly knocked its usefulness down by many
> > notches, maybe even to the point where it's not worth putting in the
> > effort needed to get it to re-committability.  So I think we need to
> > look harder at finding a way around that.  Is the concern that
> > RETURNING should return either old or new values depending on which
> > RTE is mentioned?  If so, maybe the feature Dean has proposed to
> > allow RETURNING to access old values [1] is a prerequisite to moving
> > forward.  Alternatively, perhaps it'd be good enough to forbid SJE
> > only when the non-target relation is actually mentioned in RETURNING.
> >
> >                         regards, tom lane
> >
> > [1] https://www.postgresql.org/message-id/flat/CAEZATCWx0J0-v=Qjc6gXzR=KtsdvAE7Ow=D=mu50AgOe+pvisQ@mail.gmail.com

attached patchset:
v3-0001-Remove-useless-self-joins.patch
v3-0002-make-SJE-to-apply-DML-MERGE-UPDATE-INSERT-DELETE.patch
v3-0003-use-SJE-conditionally-with-delete-update-merge-RE.patch

v3-0001: based on [1] patch, replace all replace_varno to ChangeVarNodes.
v3-0002: make SJE apply to UPDATE/DELETE/MERGE
v3-0003: make SJE apply to UPDATE/DELETE/MERGE RETURNING conditionally
v3-0001, v3-0002 content, reasoning is the same as the previous thread[2].
v3-0003 is new.
to make sure it's correct, I have added a lot of tests,
Some of this may be contrived, maybe some of the tests are redundant.

[1]: https://postgr.es/m/55f680bc-756d-4dd3-ab27-3c6e663b0e4c%40postgrespro.ru
[2]: https://postgr.es/m/CACJufxG3sqJKe1OskHhn7OCdtrEeeRFcD8R4TTQE%2BLGJEQaL9w%40mail.gmail.com

Attachment

pgsql-hackers by date:

Previous
From: David Rowley
Date:
Subject: Re: Make tuple deformation faster
Next
From: Michael Paquier
Date:
Subject: Re: Avoid incomplete copy string (src/backend/access/transam/xlog.c)