Re: Removing unneeded self joins

On Mon, Jun 17, 2024 at 8:00 AM jian he <jian(dot)universality(at)gmail(dot)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(at)mail(dot)gmail(dot)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