Skip site navigation (1) Skip section navigation (2)

Re: Reconstructing FKs in pg_dump

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: <pgsql-hackers(at)postgreSQL(dot)org>
Subject: Re: Reconstructing FKs in pg_dump
Date: 2002-09-26 22:10:35
Message-ID: 20020926145655.D29123-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-hackers
On Thu, 26 Sep 2002, Tom Lane wrote:

> Patrick Welche's recent problems (see pgsql-general) point out that the
> old CREATE CONSTRAINT TRIGGER syntax that optionally omits a "FROM
> table" clause doesn't work anymore --- the system *needs* tgconstrrelid
> to be set in an RI constraint trigger record, because the RI triggers
> now use that OID to find the referenced table.  (The table name in the
> tgargs field isn't used anymore, mainly because it's not schema-aware.)
>
> This means that RI trigger definitions dating back to 7.0 (or whenever
> it was that we fixed the pg_dump bug about not dumping tgconstrrelid)
> don't work anymore.
>
> There are a couple things I think we should do.  One: modify the CREATE
> CONSTRAINT TRIGGER code to try to extract a foreign relation name from
> the tgargs if FROM is missing.  Without this, we have no hope of loading
> working FK trigger definitions from old dumps.  Two: modify pg_dump to
> extract a name from the tgargs in the same fashion.  I'd rather have
> pg_dump do this than the backend, and this will at least make things
> better in the case where you're using a 7.3 pg_dump against an older
> database.

I'd worry about doing things only to pg_dump since that'd still leave
people that did use the old dump in the dark and there'd be nothing even
indicating a problem until they did something that used the constraint.
Even a notice for a missing FROM would be better (although at that
point how far is it to just fixing the problem).  I can look at it this
weekend (since it probably was my bug in the first place) unless you'd
rather do it.

> However, if we are going to put that kind of knowledge into pg_dump,
> it would only be a small further step to have it dump these triggers
> as ALTER TABLE ADD CONSTRAINT commands instead.  Which would be a lot
> better for forward compatibility than dumping the raw triggers.

Wasn't there still some question about the fact that ATAC causes a
check of the constraint which for large tables is not insignificant.
I don't remember if there was any consensus on how to deal with that.



In response to

Responses

pgsql-hackers by date

Next:From: Tom LaneDate: 2002-09-26 22:22:45
Subject: Re: Reconstructing FKs in pg_dump
Previous:From: Bruce MomjianDate: 2002-09-26 22:04:52
Subject: Re: [HACKERS] Performance while loading data and indexing

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group