Subject: Re: pg_dump restore time and Foreign Keys
From: Simon Riggs <simon@2ndquadrant.com>
To: Andrew Dunstan <andrew@dunslane.net>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
In-Reply-To: <4847D4A7.70309@dunslane.net>
References: <1212647003.19964.19.camel@ebony.site>
	<4847D4A7.70309@dunslane.net>
Content-Type: text/plain
Date: Thu, 05 Jun 2008 13:56:35 +0100
Message-Id: <1212670595.19964.55.camel@ebony.site>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit


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