On Thu, 2008-06-05 at 07:57 -0400, Andrew Dunstan wrote: > > Simon Riggs wrote: > > pg_dump restore times can be high when they include many ALTER TABLE ADD > > FORIEGN KEY statements, since each statement checks the data to see if > > it is fully valid in all cases. > > > > I've been asked "why we run that at all?", since if we dumped the tables > > together, we already know they match. > > > > If we had a way of pg_dump passing on the information that the test > > already passes, we would be able to skip the checks. > > > > Proposal: > > > > * Introduce a new mode for ALTER TABLE ADD FOREIGN KEY [WITHOUT CHECK]; > > When we run WITHOUT CHECK, iff both the source and target table are > > newly created in this transaction, then we skip the check. If the check > > is skipped we mark the constraint as being unchecked, so we can tell > > later if this has been used. > > > > * Have pg_dump write the new syntax into its dumps, when both the source > > and target table are dumped in same run > > > > I'm guessing that the WITHOUT CHECK option would not be acceptable as an > > unprotected trap for our lazy and wicked users. :-) > > > > This whole proposal would be a major footgun which would definitely be > abused, IMNSHO.
OK, understood. Two negatives is enough to sink it. > I think Heikki's idea of speeding up the check using a hash table of the > foreign keys possibly has merit. The query is sent through SPI, so if there was a way to speed this up, we would already be using it implicitly. If we find a way to speed up joins it will improve the FK check also. The typical join plan for the check query is already a hash join, assuming the target table is small enough. If not, its a huge sort/merge join. So in a way, we already follow the suggestion. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers