constraints names on partitions
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t39496psql -h localhost -U postgresBuilt from patchset v1 (message #1), July 28, 2026 at 05:09 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t39496_1 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t39496_1 && git checkout t39496_1Patchset v1 (message #1) is on t39496_1
Hello
I just realized that the current code to assign constraint names to
partitions is going against the SQL standard's idea that constraint
names must be unique within a schema. When a partition is created, the
foreign key gets exactly the same name as the constraint in the parent
table.
Now maybe you could argue that these constraints should simply be hidden
from view, because they are implementation artifacts; and then their
names don't matter. But we already expose the partitions themselves as
individual tables, so I don't buy this argument.
One way to fix this would be to use ChooseConstraintName() for the FK in
the partition, as in the attached patch. One caveat with this is that
there is no visual clue (in \d <partition>) that distinguishes FKs
inherited from the parent rel from ones that have been created in the
partition directly. I'm not sure that that's an important issue,
though. Another point, maybe more visible, is that if you give an
explicit name to the constraint in the parent table, this is completely
lost in the partitions -- again with any visual clue to link the two.
I'm +0.2 on applying this patch to pg11, but I'd like to hear others'
opinions.
Thanks
--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Fri, Oct 12, 2018 at 12:39 PM Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:
I just realized that the current code to assign constraint names to
partitions is going against the SQL standard's idea that constraint
names must be unique within a schema. When a partition is created, the
foreign key gets exactly the same name as the constraint in the parent
table.
I guess I don't see the point in trying to do something about this in
the partition case but not any other case. The standard may have a
rule here, but if we don't follow it in general, what benefit do we
get out of trying to sorta follow it in the specific case of
partitions with inherited foreign keys? There's probably quite a bit
of work to do here to fix this properly - I seem to recall some
previous discussion where it didn't seem simple even to ensure that
constraint names were unique within a relation in certain corner
cases.
At any rate, -1 from me for inserting a fix like this between rc1 and final.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
On Fri, Oct 12, 2018 at 12:39 PM Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:I just realized that the current code to assign constraint names to
partitions is going against the SQL standard's idea that constraint
names must be unique within a schema. When a partition is created, the
foreign key gets exactly the same name as the constraint in the parent
table.
I guess I don't see the point in trying to do something about this in
the partition case but not any other case. The standard may have a
rule here, but if we don't follow it in general, what benefit do we
get out of trying to sorta follow it in the specific case of
partitions with inherited foreign keys? There's probably quite a bit
of work to do here to fix this properly - I seem to recall some
previous discussion where it didn't seem simple even to ensure that
constraint names were unique within a relation in certain corner
cases.
We moved the goalposts on that recently, see 17b7c302b. Possibly you
are recalling the discussion that led up to that.
At any rate, -1 from me for inserting a fix like this between rc1 and final.
I agree that this is no time to be messing with it, but I support the
general concept of not intentionally violating the SQL spec for
automatically-chosen constraint names. We get complaints regularly about
the information_schema being inadequate for displaying constraints, and
our only defense against that is "don't violate the SQL spec, and you'll
be fine using the SQL spec's views". That defense falls down if the
system ever automatically chooses duplicate constraint names.
I'm not sure that this would be a good thing to change in a point release,
either :-(. We might have to just delay the fix to v12.
regards, tom lane