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