Обсуждение: foreign keys
Could someone explain me some fact about FK handling in postgresql (7.0.2) ? 1. WHen I create table with foreign key to other one ( with ON UPDATE CASCADE), i get two constraints for created table: one for insert one for update. Additionaly there is created constraint (on rows deletion) in referenced table 2. when I pg_dump whole database all of them are saved OK. 3. when I pg_dump just the new table (created) the third constraint is not dumped cause it's "connected" to referenced table. Due to the fact that many ALTER TABLE xxx commands are not implemented this can lead to loss of database "knowledge" during modification of tables structured. Of course manipulation of such loose constraints is extremly uncomfortable; Not to mentions fact that in a few places in docs it's shown as a method for copying table "SELECT... INTO" which does not "take" keys with it leading to database knwoledge loss. Maybe there is something wchich I missed or misunderstanded in FK handling in postgres? Could someone explain me this, please? -- radoslaw.stachowiak.........................................http://alter.pl/
> Not to mentions fact that in a few places in docs it's shown as a method > for copying table "SELECT... INTO" which does not "take" keys with it > leading to database knwoledge loss. That is a good point. SELECT INTO doesn't support constraints. Unfortunately, I don't really know a way around that. The only solution is CREATE TABLE and then INSERT INTO ... SELECT. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
*** Bruce Momjian <pgman@candle.pha.pa.us> [Saturday, 05.August.2000, 19:39 -0400]: > > Not to mentions fact that in a few places in docs it's shown as a method > > for copying table "SELECT... INTO" which does not "take" keys with it > > leading to database knwoledge loss. > > That is a good point. SELECT INTO doesn't support constraints. > Unfortunately, I don't really know a way around that. The only solution > is CREATE TABLE and then INSERT INTO ... SELECT. [.rs.] what about my other statement about third constraint not being transferred withh pg_dump -t table because it was "connected" to second database? Am I right? What is correct (mean: most simple) way of dupicating table with all FK ? -- radoslaw.stachowiak.........................................http://alter.pl/
> *** Bruce Momjian <pgman@candle.pha.pa.us> [Saturday, 05.August.2000, 19:39 -0400]: > > > Not to mentions fact that in a few places in docs it's shown as a method > > > for copying table "SELECT... INTO" which does not "take" keys with it > > > leading to database knwoledge loss. > > > > That is a good point. SELECT INTO doesn't support constraints. > > Unfortunately, I don't really know a way around that. The only solution > > is CREATE TABLE and then INSERT INTO ... SELECT. > [.rs.] > > what about my other statement about third constraint not being transferred > withh pg_dump -t table because it was "connected" to second database? Am I right? > > What is correct (mean: most simple) way of dupicating table with all FK ? > Sorry, I don't know. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
On Sun, 6 Aug 2000, Radoslaw Stachowiak wrote: > *** Bruce Momjian <pgman@candle.pha.pa.us> [Saturday, 05.August.2000, 19:39 -0400]: > > > Not to mentions fact that in a few places in docs it's shown as a method > > > for copying table "SELECT... INTO" which does not "take" keys with it > > > leading to database knwoledge loss. > > > > That is a good point. SELECT INTO doesn't support constraints. > > Unfortunately, I don't really know a way around that. The only solution > > is CREATE TABLE and then INSERT INTO ... SELECT. > [.rs.] > > what about my other statement about third constraint not being transferred > withh pg_dump -t table because it was "connected" to second database? Am I right? Actually, you should only be seeing one constraint out on the referencing table and two out of the referenced one, but yes, fundamentally it only is dumping the constraint triggers for the table you are dumping at the moment. > What is correct (mean: most simple) way of dupicating table with all FK ? Umm, possibly taking the dump of the table you want and a schema only dump of the referenced table and removing the bits you don't need. Or, turn the constraint triggers into alter table add constraint statements (although you'd then have to only get one alter table add constraint in case you were on the referenced table - and that could still get you in trouble depending on what precisely you're doing -- if the table was the referenced table of a fk constraint, would you necessarily want to alter the table that was referencing it?).
On Sat, 5 Aug 2000, Bruce Momjian wrote: > > Not to mentions fact that in a few places in docs it's shown as a method > > for copying table "SELECT... INTO" which does not "take" keys with it > > leading to database knwoledge loss. > > That is a good point. SELECT INTO doesn't support constraints. > Unfortunately, I don't really know a way around that. The only solution > is CREATE TABLE and then INSERT INTO ... SELECT. Argh, that's annoying. Is there a way to CREATE TABLE, getting the schema from another table? :o Ian
Hiya guys I'm having a problem with a PostgreSQL backend, the problem being that the backend can die, from the simplest queries. I'm wondering if this can be the sympthom of a corrupted database, or just a buggy version of Postgre. -Morten
*** Stephan Szabo <sszabo@megazone23.bigpanda.com> [Sunday, 06.August.2000, 14:26 -0700]: > > Actually, you should only be seeing one constraint out on the referencing > table and two out of the referenced one, but yes, fundamentally it only is > dumping the constraint triggers for the table you are dumping at the > moment. ok, but let's talk about number of constraints. I think that the correct number (for my meaning of full foreign key) is 4. 2 for both tables: referenced: UPDATE contrains AND DELETE constraint referencing: INSERT constraint AND UPDATE constraint am I right? > > What is correct (mean: most simple) way of dupicating table with all FK ? > > Umm, possibly taking the dump of the table you want and a schema only > dump of the referenced table and removing the bits you don't need. > > Or, turn the constraint triggers into alter table add constraint > statements (although you'd then have to only get one alter table add > constraint in case you were on the referenced table - and that could > still get you in trouble depending on what precisely you're doing -- > if the table was the referenced table of a fk constraint, would you > necessarily want to alter the table that was referencing it?). [.rs.] my english is not so good for such complicated sentences :) what i need/try to accomplish is to full dump/recreate/modify of table with all needed (applied) constraints. Primiary I thought only about referencing table but now I know that "prescription" should also mention operation on referenced table. In short: 1. how to full duplicate/modify table (referencing) 2. how to full duplicate/modify table (referenced) It should take care of fact that it should at start DESTROY table and all constraints (on both tables!!!) and than recreate it from scratch - this is needed to satisfy the modify case (someone may need to changee FK schema to sth different). How can I manipulate existing unnamed (created automaticly by foreign key) constraints on tables in PSQL tool ? -- radoslaw.stachowiak.........................................http://alter.pl/
On Mon, 7 Aug 2000, Morten W. Petersen wrote: > Hiya guys > > I'm having a problem with a PostgreSQL backend, the problem being that the > backend can die, from the simplest queries. I'm wondering if this can be > the sympthom of a corrupted database, or just a buggy version of Postgre. It'll help if you provide more info, like what version of PostgreSQL, what OS and version, example queries, etc.. Vince. -- ========================================================================== Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking Online Campground Directory http://www.camping-usa.com Online Giftshop Superstore http://www.cloudninegifts.com ==========================================================================
> It'll help if you provide more info, like what version of PostgreSQL, > what OS and version, example queries, etc.. Woop. The database that's working is a PostgreSQL 6.5 on Debian 2.1, the database that isn't is a PostgreSQL 6.5 on Suse 6.4, both x86 architecture machines. The PostgreSQL on the Suse box is a standard package, the PostgreSQL on the Debian box is compiled from scratch. An example of a query is "SELECT id FROM inmail WHERE mailsetup_id = 1". This is through the Python database adapter. I think I've maybe found the problem, the database adapter on the machine that is working is at 3.0, while the one that isn't is at 3.0 beta. Hmm. Anyways, if you guys have any ideas I'd appreciate to hear about them. -Morten
NOTICE: Rel inmail: Uninitialized page 433 - fixing I got this message trying to vacuum the DB. Can this be the problem? If so, does anyone know why this may be happening? -Morten
"Morten W. Petersen" <morten@src.no> writes:
> NOTICE: Rel inmail: Uninitialized page 433 - fixing
> I got this message trying to vacuum the DB. Can this be the problem?
No, that would be a symptom of recovery from an earlier problem.
One plausible explanation is that you're trying to insert tuples > 8K
in a pre-7.0 database; older versions tended to notice the problem
only after extending the relation by one page, leading to the above
symptom.
But as Vince remarked, it's all speculation when you haven't told us
a single detail about your installation or the troublesome queries.
regards, tom lane
On Mon, 7 Aug 2000, Radoslaw Stachowiak wrote: > *** Stephan Szabo <sszabo@megazone23.bigpanda.com> [Sunday, 06.August.2000, 14:26 -0700]: > > > > Actually, you should only be seeing one constraint out on the referencing > > table and two out of the referenced one, but yes, fundamentally it only is > > dumping the constraint triggers for the table you are dumping at the > > moment. > > ok, but let's talk about number of constraints. I think that the correct > number (for my meaning of full foreign key) is 4. 2 for both tables: > referenced: UPDATE contrains AND DELETE constraint > referencing: INSERT constraint AND UPDATE constraint > > am I right? It's actually just one constraint, but it's implemented in 3 triggers, because one constraint trigger is both INSERT AND UPDATE on referencing both. The semantics of UPDATE and DELETE on referenced can be different, so they get separate constraint triggers , but INSERT and UPDATE on referencing have the same semantics so they share one constraint trigger that is called on either operation. > what i need/try to accomplish is to full dump/recreate/modify of table with all > needed (applied) constraints. Primiary I thought only about referencing > table but now I know that "prescription" should also mention operation on > referenced table. In short: > 1. how to full duplicate/modify table (referencing) > 2. how to full duplicate/modify table (referenced) > > It should take care of fact that it should at start DESTROY table and all > constraints (on both tables!!!) and than recreate it from scratch - this > is needed to satisfy the modify case (someone may need to changee FK > schema to sth different). Umm, it's very hard to do automatically. Pretty much, your best bet is to look at pg_trigger and find constraints that reference the tables you're doing, dumping the table schema, dropping the table, removing the create constraint trigger statements that are dumped and replacing them with an ALTER TABLE ADD CONSTRAINT. > How can I manipulate existing unnamed (created automaticly by foreign key) > constraints on tables in PSQL tool ? Don't use unnamed constraints? :-) In practice it's probably always good form to use: constraint <name> references ... or constraint <name> foreign key (...) references ... Seriously, for foreign key constraints, you can remove them by removing the rows in pg_trigger that are associated with them. If they're unnamed, you'll have to use the data in tgargs to determine which is the correct one. --- Reading pg_trigger for fk constraints The function that is referenced will tell you which trigger it is... You'll need to do something like select pg_trigger.*, proname from pg_trigger, pg_proc where pg_trigger.tgfoid=pg_proc.oid. And the proname will be like RI_<thing to do>_<ins|del|upd> The checking constraint on the referencing table is RI_check_ins I believe. The constraints on the referenced table will have the action you specified in the name, so RI_cascade_upd or RI_setnull_del. Tgargs stores the information on the tables and columns referenced. It's in the form: name\000referencing table\000referenced table\000match type\000 referencing column1\000referenced column 1\000... (Note: the internal form here may change for 7.1)
for what it's worth...i wrote this perl script to manage deleting foreign keys and keeping the pg_class table in synch with pg_trigger. it will do local hosts or remote hosts. like most free things it probably can be improved on. please see attached... HTH somewhat, mikeo At 11:38 AM 8/7/00 -0700, Stephan Szabo wrote: > >Umm, it's very hard to do automatically. Pretty much, your best bet is to >look at pg_trigger and find constraints that reference the tables you're >doing, dumping the table schema, dropping the table, removing the create >constraint trigger statements that are dumped and replacing them with an >ALTER TABLE ADD CONSTRAINT. > > >> How can I manipulate existing unnamed (created automaticly by foreign key) >> constraints on tables in PSQL tool ? >Don't use unnamed constraints? :-) >In practice it's probably always good form to use: >constraint <name> references ... or >constraint <name> foreign key (...) references ... > >Seriously, for foreign key constraints, you can remove them by removing >the rows in pg_trigger that are associated with them. If they're unnamed, >you'll have to use the data in tgargs to determine which is the correct >one. > >--- >Reading pg_trigger for fk constraints > >The function that is referenced will tell you which trigger it is... >You'll need to do something like >select pg_trigger.*, proname from pg_trigger, pg_proc where >pg_trigger.tgfoid=pg_proc.oid. >And the proname will be like >RI_<thing to do>_<ins|del|upd> >The checking constraint on the referencing table is >RI_check_ins I believe. The constraints on the referenced table will >have the action you specified in the name, so RI_cascade_upd or >RI_setnull_del. > >Tgargs stores the information on the tables and columns referenced. >It's in the form: >name\000referencing table\000referenced table\000match type\000 >referencing column1\000referenced column 1\000... >(Note: the internal form here may change for 7.1) > >
Вложения
> But as Vince remarked, it's all speculation when you haven't told us > a single detail about your installation or the troublesome queries. Well, I posted some detailed information earlier, if you'll have a look. -Morten
> Seriously, for foreign key constraints, you can remove them by removing > the rows in pg_trigger that are associated with them. If they're unnamed, > you'll have to use the data in tgargs to determine which is the correct > one. Perhaps we should come up with a better naming convention than <unnamed> for unnamed constraints? Like is done with implied indices and sequences. Ian
On Sun, 6 Aug 2000 vectro@pipeline.com wrote: > > Seriously, for foreign key constraints, you can remove them by removing > > the rows in pg_trigger that are associated with them. If they're unnamed, > > you'll have to use the data in tgargs to determine which is the correct > > one. > > Perhaps we should come up with a better naming convention than <unnamed> > for unnamed constraints? Like is done with implied indices and sequences. Definately. It's on my hopefully to get done reasonably soon list since Jan's really busy with TOAST. I'm actually trying to get it done mostly correctly to the SQL spec which is why I haven't hit it yet. I want to make sure that constraint names (or at least fk constraint names) never conflict with any other constraint name in the database (schema technically - but until we have schemas...).