Message-ID: <4847D4A7.70309@dunslane.net>
Date: Thu, 05 Jun 2008 07:57:27 -0400
From: Andrew Dunstan <andrew@dunslane.net>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.8.0.12) Gecko/20071019 Fedora/1.0.9-3.fc6 pango-text
	SeaMonkey/1.0.9
MIME-Version: 1.0
To: Simon Riggs <simon@2ndquadrant.com>
CC: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: pg_dump restore time and Foreign Keys
References: <1212647003.19964.19.camel@ebony.site>
In-Reply-To: <1212647003.19964.19.camel@ebony.site>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit


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.

I think Heikki's idea of speeding up the check using a hash table of the 
foreign keys possibly has merit.

cheers

andrew