Обсуждение: Re: [GENERAL] pg_dump: creates dumps that cannot be restored
Hi *, I’ve tried both setting the constraints temporarily to invalid (works) and converting (painstakingly slow, as this is new for me) to triggers (also works). Both can be dumped and restored. I’ve also found out that I probably can ship the schema update that converts the CHECK constraint to a trigger to the customer Right Now™ so I’ll fix this actual schema bug. I still find the CHECK constraint to be a more natural way to express what I want, though. I’m attaching the trigger conversion to help anyone else who does this (and to invite feedback should there be anything I could improve). Thanks, //mirabilos -- tarent solutions GmbH Rochusstraße 2-4, D-53123 Bonn • http://www.tarent.de/ Tel: +49 228 54881-393 • Fax: +49 228 54881-235 HRB 5168 (AG Bonn) • USt-ID (VAT): DE122264941 Geschäftsführer: Dr. Stefan Barth, Kai Ebenrett, Boris Esser, Alexander Steeg
Вложения
Hi again, one, possibly, last, thing. I wrote: > I still find the CHECK constraint > to be a more natural way to express what I want, though. Now let me extend on this a bit. The CHECK constraint says nicely and natively, what constraints (no pun intended) I want the data to fulfil. With both the CHECK constraint and the trigger, we need an equivalent constraint on the referenced foreign table, which we have, which we always had; in the schema example I gave, this is not allowing the field “standalone” to change. I can, however, VALIDATE a CHECK constraint after the dump has been restored; I cannot do that with a trigger (or I haven’t found out how to do it). This means that if a user manually edited the dump prior to restoring I have no way to make the restoring transaction fail if the data is bogus. Sure, SOL on the user, but I created the various CHECK constraints to cover against user errors in the first place. I would very much prefer for PostgreSQL to ① formally allow and support such CHECK constraints, ② add an ALTER TABLE … INVALIDATE CONSTRAINT command, to pair with the existing ALTER TABLE … VALIDATE CONSTRAINT command, and ③ hack pg_dump to invalidate constraints before and revalidate them after the fact. 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. As for validation, see above. 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. Thank you for listening. bye, //mirabilos -- tarent solutions GmbH Rochusstraße 2-4, D-53123 Bonn • http://www.tarent.de/ Tel: +49 228 54881-393 • Fax: +49 228 54881-235 HRB 5168 (AG Bonn) • USt-ID (VAT): DE122264941 Geschäftsführer: Dr. Stefan Barth, Kai Ebenrett, Boris Esser, Alexander Steeg
③ 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.