③ hack pg_dump to invalidate constraints before and revalidate them
after the fact.
I suspect there are many people who'd rather take the dump at face value then expending considerably amounts of time re-validating everything that is known to already be valid. dump-restore people are usually less concerned about downtime but its not unimportant to them.
This would allow me to express what I want in a more natural and
easier to validate (pun intended this time) way.
It feels “right” to use a trigger on the referenced table preventing
the field from changing, but it feels more right for the referencing
table to simply use a CHECK constraint.
This isn't a bug, isn't the first time its come up, and hasn't been changed in the many years of awareness. While your feelings are important we cannot commit feelings. If those get translated into a feature then the necessary trade-off analysis can be performed and a decision can be made as to whether the benefit of such clean syntax outweighs the costs such a capability would impose at runtime. Triggers do work and while they constrain what PostgreSQL can support in terms of architecture and promises the current setup no one has felt strongly enough to volunteer time or money to lessen the constraints.
For my current use case, the ship has sailed, but (especially given
that such CHECK constrains are currently, while not officially
supported, at least “tolerated” and (except in pg_dump) work) this
is something to consider for PostgreSQL 10 in my opinion.
No matter the merits of this feature the ship for 10 also sailed, about 3 weeks ago: feature freeze is now in effect.
David J.
