Horton

Thread

  1. foreign keys

    Radoslaw Stachowiak <radek@alter.pl> — 2000-08-05T22:05:44Z

    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/

  2. Re: foreign keys

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-08-05T23:39:09Z

    > 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

  3. Re: foreign keys

    Radoslaw Stachowiak <radek@alter.pl> — 2000-08-06T09:28:12Z

    *** 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/

  4. Re: foreign keys

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-08-06T17:51:28Z

    > *** 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

  5. Re: foreign keys

    Ian Turner <vectro@pipeline.com> — 2000-08-06T18:46:10Z

    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

  6. Re: foreign keys

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-08-06T21:26:17Z

    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?).

  7. Re: foreign keys

    Ian Turner <vectro@pipeline.com> — 2000-08-07T00:06:27Z

    > 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

  8. Morten W. Petersen <morten@src.no> — 2000-08-07T09:59:00Z

    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

  9. Re: foreign keys

    Radoslaw Stachowiak <radek@alter.pl> — 2000-08-07T10:03:20Z

    *** 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/

  10. Re:

    Vince Vielhaber <vev@michvhf.com> — 2000-08-07T10:42:57Z

    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
==========================================================================

  11. Re:

    Morten W. Petersen <morten@src.no> — 2000-08-07T11:45:30Z

    > 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

  12. Re:

    Morten W. Petersen <morten@src.no> — 2000-08-07T12:52:37Z

    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

  13. Re:

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-07T14:45:51Z

    "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

  14. Re: foreign keys

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-08-07T18:38:51Z

    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)

  15. Re: foreign keys

    mikeo <mikeo@spectrumtelecorp.com> — 2000-08-07T20:15:27Z

    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)
>
>

  16. Re:

    Morten W. Petersen <morten@src.no> — 2000-08-07T20:35:12Z

    > 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

  17. Re: foreign keys

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2000-08-07T23:28:14Z

    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...).