On Fri, Aug 10, 2018 at 09:47:09AM -0400, Tom Lane wrote: > Peter Eisentraut <peter.eisentr...@2ndquadrant.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
