Re: Constraint documentation

On Fri, Aug 10, 2018 at 09:47:09AM -0400, Tom Lane wrote:
> Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> writes:
> > I think it would be very easy to restore check constraints separately
> > after all tables in pg_dump. There is already support for that, but
> > it's only used when necessary, for things like not-valid constraints.
> > The argument in favor of keeping the constraint with the table is
> > probably only aesthetics,
>
> No, it's mainly about performance. Checking the constraint at data load
> time avoids extra scans of the table, and should work in any case that
> we consider supported.

We could deal with this by putting those constraints in the "pre-data"
section, which would let people do any needed surgery using the
standard pg_restore -l/-L machinery, should they actually happen to be
"post-data" constraints.

> To be clear, I totally reject the notion that we should consider this
> case supported, or that kluging pg_dump to not fail would make it so.
> As a counterexample, if you have a poor-mans-FK check constraint on
> table A that only succeeds when there's a matching row in table B, it
> cannot prevent the case where you insert a valid (matching) row in
> table A and then later delete its matching row in B.

That's the case I ran into last week, and it required a schema change
in order to ensure that dumps were restorable in their unmodified
form, that being crucial to disaster recovery operations.

> Maybe someday we'll have full database assertions (with, no doubt,
> a ton of performance caveats).

The initial performance will likely be pretty awful for isolation
levels lower than SERIALIZABLE, anyhow.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate