Re: Find all foreign keys and dropping them from a script.

Thanks for the idea,

Altough it looks promissing i am not sure if it can do what i need it to
do.
Since all constaints are made NOT DEFERRABLE when I create the table/FK
the SET CONSTRAINT command won't work on my existing PK's and thus won't
help me with my current problem (as we already have a production
database). But this might be solved by doing a one time conversion of
all tables trough the process of copying data to a temp table, making a
new table with DEFERRABLE FK's and then reinserting the clients data
into the new table.

Tough then I am faced with a new/different problem relating to the
original question.

The way the FK's are managed is trough a multi-developer tool in witch
developers can add FK's to tables. Then when a new version of the
product is released this tool generates all FK's that need to be in the
new release.
Currently the tool does not tell me what FK's where dropped in the
release so in the way you described I would disable them and then just
re-enable them as soon as I am done with the transaction. The same goes
for new FK's since I don't know witch FK's are new we currently first
remove all FK"s then read all FK's the tool generated. When I add a FK
that already exists I'll get an error (witch I can understand, but don't
want) that the FK already exists.

At the moment I can't change the tool that makes the FK's so I am
looking for another way to make this work.

I did manage to make an implementation that query's all out FK's from
the pg system tables but I would prefer not to use that unless there is
no other way as I don't like messing around in the pg system tables.
(and besides the obvious reason I dislike it, it broke when we migrated
from 8.0 to 8.1 since it seems the definition of tgtype in the
pg_triggers table has altered and I'd hate to re-invent my hacky-wheel
every new pg release).

Hope someone has some idea for my problem (and can follow my description
of my problem).

Thanks in advance,

Robert

-----Original Message-----
From: Michael Fuhr [mailto:mike(at)fuhr(dot)org]
Sent: Tuesday, April 18, 2006 16:53
To: Robert Landsmeer
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: [NOVICE] Find all foreign keys and dropping them from a
script.

On Tue, Apr 18, 2006 at 04:05:48PM +0200, Robert Landsmeer wrote:
> To update the database we have a script that will execute all the data
> updates of the product. Since the updater isn't aware of what updates
> need to be done before other updates FK-wise we get all kinds of
errors
> about incorrect data, and the script fails. So we want to drop all
> foreign keys, insert a lot of data, and then re-add all foreign keys.

Are the updates all done in the same transaction? If so then you
might be able to use deferrable foreign key constraints. Search
the following documentation for the words "DEFERRABLE" and "DEFERRED":

http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html
http://www.postgresql.org/docs/8.1/interactive/sql-set-constraints.html

--
Michael Fuhr