Re: pg_dump restore time and Foreign Keys

On Thursday 05 June 2008 08:56:35 Simon Riggs wrote:
> 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.
>

Heh, I would have argued that the idea should go the other way and just make
this part of the normal syntax. Oracle DBA's have been doing this for years
(MS SQL supports it too actually) and it really helps working around having
to hold locks on large relations for lengthy periods of times. Heck, I'd like
to see a no check option for all constraints really.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL