Subject: pg_dump restore time and Foreign Keys
From: Simon Riggs <simon@2ndquadrant.com>
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain
Date: Thu, 05 Jun 2008 07:23:23 +0100
Message-Id: <1212647003.19964.19.camel@ebony.site>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

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. :-)

-- 
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support