Re: [GENERAL] pg_dump: creates dumps that cannot be restored

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