Re: Key joins

From: Magnus Hagander <magnus(at)hagander(dot)net>
To: Arne Roland <arne(dot)roland(at)malkut(dot)net>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>, Joel Jacobson <joel(at)compiler(dot)org>, Tomas Vondra <tomas(at)vondra(dot)me>, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, Anders Granlund <anders(dot)granlund(dot)0(at)gmail(dot)com>, Andreas Karlsson <andreas(at)proxel(dot)se>, Vik Fearing <vik(at)chouppes(dot)com>, Alexander Melnikov <asmmp(at)protonmail(dot)com>
Subject: Re: Key joins
Date: 2026-11-16 10:48:46
Message-ID: CABUevEwpD3z__Mcj=1R=ZzzsADLr7mOxqBHBPdUPku6Kiuv8HQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi!

I did a quick run-through of this one together with Joel and Andreas on
Friday, and found a few smaller things. Not a detailed review, but some
things worth thinking about. I thin the version we reviewed includes what
yous ent here.

1. There are still a few references that tails about "KEY clause", that
should be "FOR KEY" clause (in comments).

2. Right now there are terms that are "referencing/referenced" and "fk/pk"
used in different places meaning different things. Let's try to harmonize
that to just one of them (since we already also have left/right which does
not mean the same thing). There's also a "rfacts" and "pfacts" where the
"p" probably comes from "primary", another version of this?

3. Small nitpick on error msg:
errmsg("key join from referencing relation %s to referenced relation %s
cannot be proven",

feels very verbose, since we know that keyjoins are from a referencing
relation to a referenced one. Could probably just be:
errmsg("key join from %s to %s cannot be proven",

4. Another minor comment issue that sent us spending unnecessary time
tracking:
KJF_ROW_COVERAGE, /* all-non-null relid key
values reach here */
coudl be
KJF_ROW_COVERAGE, /* all-non-null relid key
values reach this surface */

(as the "here" would reference the place where the enum value is used)

5. Unsure how much it matters but it would save a byte to merge the boolean
active into the enum inactiveReason, as one possible value for it. But it
would also make it more clear, since it would no longer be theoretically
possible to end up in the situation of active=true,
inactiveReason=<something>.

And finally, consider reorder/track the patchset in a slightly different
order and with phases of consideration, to make it easier to do partial
review/merge.

Phase 0 = the bugfix that's really independent, but needs to be fixed.

Phase 1 = minimum for "shippable feature", which would basically be all
patches up to but not including storable key joins. Still a useful
feature,, but not the target. But if this level was merged, we wouldn't
have to revert if the rest isn't done.

Phase 2 = complete feature. This would be all patches up to but not
including filter conjuncts. This would be the target for v20, but if
missed phase 1 would still remain in and this could go into v21.

Phase 3 = bonus feature. This would include filter conjuncts, but can
easily be done in a later version. This would be a nice-to-have in v20, but
could easily be pushed back to v21 if needed.

The only really special one there is that full tab complete would be in
phase 2, but if we'd end up not getting to phase 2 we'd backport a limited
tab completion into a "phase 1.1" to go in with just those parts.

//Magnus

On Thu, 12 Nov 2026 at 22:44, Arne Roland <arne(dot)roland(at)malkut(dot)net> wrote:

> Hi hackers,
>
> the main change with this patch are the simplified regression tests.
> While most of the tests were create with the help of claude (mix of
> different models cooperating), I've gone over them three times. I have
> ripped out most of senseless duplication and added in four test cases,
> which seemed relevant to me. A few comments were also rewritten by me. I
> do not think the tests are perfect, but they are way more reasonable
> than the extreme amount of pilled on regression tests, we had prior.
>
> Two small changes for : KeyJoinBarrier is a new enum for tracking which
> object has no foreign key, because that is helpful to report. Also I
> added a new unrecognized key join requirement, because an earlier
> version of this patch forgot to set the return, so the error detail was
> misleading. This now gives a clean error in case that is forgotten.
>
> I also got rid of the revalidate of ALTER CONSTRAINT .. NO INHERIT; as
> that keeps the constraint row of the child intact and as far as I am
> concerned never really warranted any special treatment.
>
> Note I did not touch the injection tests yet, but I started working on
> them. This is still blowing up the Record and revalidate where 14 of the
> 15 ones live (the last one is routine).
>
> Regards
> Arne
>
> On 2026-10-08 10:15 PM, Arne Roland wrote:
> > Hi hackers,
> >
> > the attached patch is rebased on master. We also decided to drop the
> > FILTER (WHERE ...) syntax, to avoid further discussions about that
> > syntax hold back the patch itself.
> >
> > The biggest change is the syntax change affecting the whole chain, but
> > mostly our first commit. FOR KEY now names a table on both sides of
> > the arrow, left operand first: a JOIN b FOR KEY a_tab (id) <- b_tab
> > (a_id) replaces the old FOR KEY (a_id) -> a_tab (id), which named only
> > the referenced table. The arrow still points from the referencing side
> > to the referenced side. This moves the nice canonical form from all
> > left arrows to all forward right arrows. This lead to a lot of
> > mechanical changes in the tests. I did not review all of them by hand.
> >
> > This patch also patches the debug section of pg_rewrite_query (the
> > roundtrip eq check). Because proofDependencies are never written,
> > we skip the serialization tests for FOR KEY JOIN dependencies. This is
> > not done to address any live bugs, but avoid failing tests with
> > debug_write_read_parse_plan_trees enabled. This is part of the Record
> > and revalidate member of the series.
> >
> > Regards
> > Arne
> >

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2026-11-16 10:52:59 Re: Hash index: stop registering pin-only bucket pages in WAL (remove REGBUF_NO_CHANGE)
Previous Message Bertrand Drouvot 2026-11-16 10:38:56 Re: pgstat: Flush some statistics within running transactions, take 2