Обсуждение: Find all foreign keys and dropping them from a script.
Hello all,
I am having a hard time finding all foreign keys on my database trough a script and then removing them.
The reason I need to do this is that the application I am using has data that has been altered within an update of the product.
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. We already have a script that re-adds all foreign keys and also adds the new foreign keys for the new product release.
So at the moment I am looking for a way to get all current FK’s trough a JDBC connection. Since a script will auto generate the drop key scripts it would be nice to have a way to do this. But after some searching on the internet I could not find a nice way of doing this (if I have missed a resource, please feel free to point me to it J ).
Thanks in advance,
Robert
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
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@fuhr.org] Sent: Tuesday, April 18, 2006 16:53 To: Robert Landsmeer Cc: pgsql-novice@postgresql.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
On Tue, 18 Apr 2006 16:05:48 +0200
"Robert Landsmeer" <r.landsmeer@wis.nl> wrote:
> I am having a hard time finding all foreign keys on my database trough a
> script and then removing them.
>
> The reason I need to do this is that the application I am using has data
> that has been altered within an update of the product.
>
> 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. We
> already have a script that re-adds all foreign keys and also adds the
> new foreign keys for the new product release.
Try:
select c.conname, cl.relname
from pg_constraint c, pg_class cl
where cl.oid=c.conrelid and c.contype='f';
There should be a way to get this from the more portable/standard 'information_schema' schema, but I can't seem to find
itright now...
-- George Young
> So at the moment I am looking for a way to get all current FK's trough a
> JDBC connection. Since a script will auto generate the drop key scripts
> it would be nice to have a way to do this. But after some searching on
> the internet I could not find a nice way of doing this (if I have missed
> a resource, please feel free to point me to it :-) ).
>
>
>
> Thanks in advance,
>
> Robert
>
>
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
Thank you.
This solution does indeed do what I am looking for.
If you ever find the more portable/standard way you described I'll be
happy to hear it but until then I'm happily using the solution you
provided.
Robert
-----Original Message-----
From: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org] On Behalf Of George Young
Sent: Tuesday, April 18, 2006 19:59
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Find all foreign keys and dropping them from a
script.
On Tue, 18 Apr 2006 16:05:48 +0200
"Robert Landsmeer" <r.landsmeer@wis.nl> wrote:
> I am having a hard time finding all foreign keys on my database trough
a
> script and then removing them.
>
> The reason I need to do this is that the application I am using has
data
> that has been altered within an update of the product.
>
> 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.
We
> already have a script that re-adds all foreign keys and also adds the
> new foreign keys for the new product release.
Try:
select c.conname, cl.relname
from pg_constraint c, pg_class cl
where cl.oid=c.conrelid and c.contype='f';
There should be a way to get this from the more portable/standard
'information_schema' schema, but I can't seem to find it right now...
-- George Young
> So at the moment I am looking for a way to get all current FK's trough
a
> JDBC connection. Since a script will auto generate the drop key
scripts
> it would be nice to have a way to do this. But after some searching on
> the internet I could not find a nice way of doing this (if I have
missed
> a resource, please feel free to point me to it :-) ).
>
>
>
> Thanks in advance,
>
> Robert
>
>
--
"Are the gods not just?" "Oh no, child.
What would become of us if they were?" (CSL)
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly