Legacy foreign keys

Hi all,

I am having a problem with what appear to be legacy foreign keys in
some of my databases (probably pre-7.2 days). They won't restore from
8.2 to 8.3, because the '' field in the called function isn't
unique when I try to restore more than one of these legacy keys


Here's an example
=> \d sushi.atom
....table definition, rules, etc....
Triggers:
"RI_ConstraintTrigger_23125842" AFTER INSERT OR UPDATE ON
sushi.atom FROM sushi.atom_type_values NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_check_ins"('', 'atom', 'atom_type_values',
'UNSPECIFIED', 'type', 'atom_type')
"RI_ConstraintTrigger_23125843" AFTER INSERT OR UPDATE ON
sushi.atom FROM sushi.state_values NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"('',
'atom', 'state_values', 'UNSPECIFIED', 'state', 'state')

While I could fix this, I figure this would be a good opportunity to
quickly run alter table and get these keys in a not-ancient form.

Does anyone know how to translate these to the modern form (including
proper CASCADE and RESTRICT values so I can drop the trigger and add
the proper foreign key?

Thanks much.
Peter

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

Re: Legacy foreign keys

Peter Koczan escribi=F3:
> Hi all,
>=20
> I am having a problem with what appear to be legacy foreign keys in
> some of my databases (probably pre-7.2 days). They won't restore from
> 8.2 to 8.3, because the '' field in the called function isn't
> unique when I try to restore more than one of these legacy keys

8.3's pg_dump contains smarts to dump these things as normal FKs. Try
it.

--=20
Alvaro Herrera http://www.PlanetPostgreSQL.=
org/
"Postgres is bloatware by design: it was built to house
PhD theses." (Joey Hellerstein, SIGMOD annual conference 2002)

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Legacy foreign keys

On Dec 10, 2007 1:50 PM, Alvaro Herrera wrote:
> Peter Koczan escribi=F3:
> > Hi all,
> >
> > I am having a problem with what appear to be legacy foreign keys in
> > some of my databases (probably pre-7.2 days). They won't restore from
> > 8.2 to 8.3, because the '' field in the called function isn't
> > unique when I try to restore more than one of these legacy keys
>
> 8.3's pg_dump contains smarts to dump these things as normal FKs. Try
> it.

Oh, that explains the problems...I was using 8.2's pg_dump. I'll try
it out. Thanks.

Peter

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Legacy foreign keys

Alvaro Herrera writes:
> Peter Koczan escribió:
>> I am having a problem with what appear to be legacy foreign keys in
>> some of my databases (probably pre-7.2 days). They won't restore from
>> 8.2 to 8.3, because the '' field in the called function isn't
>> unique when I try to restore more than one of these legacy keys

> 8.3's pg_dump contains smarts to dump these things as normal FKs. Try
> it.

No, pg_dump isn't involved --- the new smarts are inside the server,
in CREATE CONSTRAINT TRIGGER. It sounds like Peter has a case that is
confusing that new code. Could we see a complete example?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Legacy foreign keys

"Peter Koczan" writes:
> I am having a problem with what appear to be legacy foreign keys in
> some of my databases (probably pre-7.2 days). They won't restore from
> 8.2 to 8.3, because the '' field in the called function isn't
> unique when I try to restore more than one of these legacy keys

BTW, I spent some time trying to reproduce that, without success;
so I'm now even more interested in seeing your example case.
I think your "because" diagnosis is probably wrong ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Legacy foreign keys

> No, pg_dump isn't involved --- the new smarts are inside the server,
> in CREATE CONSTRAINT TRIGGER. It sounds like Peter has a case that is
> confusing that new code. Could we see a complete example?

Sure, here's the command:
$ /s/postgresql-8.2.5/bin/pg_dump -h sensei -p 5432 -C -Fc sushi |
/s/postgresql-8.3-beta/bin/pg_restore -h mitchell -p 5434 -C -d
postgres

Here are the errors:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1775; 2620 23125843
TRIGGER RI_ConstraintTrigger_23125843 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: trigger
"" for relation "atom" already exists
Command was: CREATE CONSTRAINT TRIGGER ""
AFTER INSERT OR UPDATE ON atom
FROM state_values
NOT DEFERRABLE INITIALLY ...
pg_restore: [archiver (db)] Error from TOC entry 1783; 2620 23125845
TRIGGER RI_ConstraintTrigger_23125845 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: trigger
"" for relation "results" already exists
Command was: CREATE CONSTRAINT TRIGGER ""
AFTER INSERT OR UPDATE ON results
FROM result_values
NOT DEFERRABLE INITIA...
pg_restore: [archiver (db)] Error from TOC entry 1784; 2620 23125846
TRIGGER RI_ConstraintTrigger_23125846 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: trigger
"" for relation "results" already exists
Command was: CREATE CONSTRAINT TRIGGER ""
AFTER INSERT OR UPDATE ON results
FROM on_disk_values
NOT DEFERRABLE INITI...
pg_restore: [archiver (db)] Error from TOC entry 1780; 2620 23125848
TRIGGER RI_ConstraintTrigger_23125848 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: trigger
"" for relation "offline" already exists
Command was: CREATE CONSTRAINT TRIGGER ""
AFTER INSERT OR UPDATE ON offline
FROM offline_disposition_values
NOT DEFE...
pg_restore: [archiver (db)] Error from TOC entry 1786; 2620 23125852
TRIGGER RI_ConstraintTrigger_23125852 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: trigger
"" for relation "wspolicy" already exists
Command was: CREATE CONSTRAINT TRIGGER ""
AFTER INSERT OR UPDATE ON wspolicy
FROM atom_dump_policy_values
NOT DEFERR...
pg_restore: [archiver (db)] Error from TOC entry 1787; 2620 23125853
TRIGGER RI_ConstraintTrigger_23125853 postgres
pg_restore: [archiver (db)] could not execute query: ERROR: trigger
"" for relation "wspolicy" already exists
Command was: CREATE CONSTRAINT TRIGGER ""
AFTER INSERT OR UPDATE ON wspolicy
FROM atom_type_values
NOT DEFERRABLE IN...
WARNING: errors ignored on restore: 6

Table definition in 8.2 server:
sushi=> \d sushi.wspolicy
Table "sushi.wspolicy"
Column | Type | Modifiers
----------------+--------+-----------
os | text | not null
dump_policy | text | not null
atom_type | text | not null
file_system | text | not null
est_epoch_size | bigint |
Indexes:
"su_wspolicy_pkey" PRIMARY KEY, btree (os, dump_policy, atom_type,
file_system)
Triggers:
"RI_ConstraintTrigger_23125851" AFTER INSERT OR UPDATE ON
sushi.wspolicy FROM sushi.atom_os_policy_values NOT DEFERRABLE
INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_check_ins"('', 'wspolicy', 'atom_os_policy_values',
'UNSPECIFIED', 'os', 'os')
"RI_ConstraintTrigger_23125852" AFTER INSERT OR UPDATE ON
sushi.wspolicy FROM sushi.atom_dump_policy_values NOT DEFERRABLE
INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_check_ins"('', 'wspolicy',
'atom_dump_policy_values', 'UNSPECIFIED', 'dump_policy',
'dump_policy')
"RI_ConstraintTrigger_23125853" AFTER INSERT OR UPDATE ON
sushi.wspolicy FROM sushi.atom_type_values NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_check_ins"('', 'wspolicy', 'atom_type_values',
'UNSPECIFIED', 'atom_type', 'atom_type')


Table definition in 8.3 beta sever:
sushi=> \d sushi.wspolicy
Table "sushi.wspolicy"
Column | Type | Modifiers
----------------+--------+-----------
os | text | not null
dump_policy | text | not null
atom_type | text | not null
file_system | text | not null
est_epoch_size | bigint |
Indexes:
"su_wspolicy_pkey" PRIMARY KEY, btree (os, dump_policy, atom_type,
file_system)
Triggers:
"" AFTER INSERT OR UPDATE ON sushi.wspolicy FROM
sushi.atom_os_policy_values NOT DEFERRABLE INITIALLY IMMEDIATE FOR
EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"('',
'wspolicy', 'atom_os_policy_values', 'UNSPECIFIED', 'os', 'os')

It's still trying to create the foreign key as triggers. And the
trigger changed its name to "", which ultimately caused the
problem, since all the other triggers wanted to be renamed to
"" as well.

This happened for both the 8.3 and 8.2 pg_dump.

Peter

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Legacy foreign keys

"Peter Koczan" writes:
>> No, pg_dump isn't involved --- the new smarts are inside the server,
>> in CREATE CONSTRAINT TRIGGER. It sounds like Peter has a case that is
>> confusing that new code. Could we see a complete example?

> Sure, here's the command:

[ squint... ] Which 8.3beta are you testing, exactly? This was dealt
with in beta3.

If it is beta3 or later, please send the output of "pg_dump -s -t wspolicy"
from the 8.2 database.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: Legacy foreign keys

On Dec 11, 2007 9:06 PM, Tom Lane wrote:
> "Peter Koczan" writes:
> >> No, pg_dump isn't involved --- the new smarts are inside the server,
> >> in CREATE CONSTRAINT TRIGGER. It sounds like Peter has a case that is
> >> confusing that new code. Could we see a complete example?
>
> > Sure, here's the command:
>
> [ squint... ] Which 8.3beta are you testing, exactly? This was dealt
> with in beta3.
>
> If it is beta3 or later, please send the output of "pg_dump -s -t wspolicy"
> from the 8.2 database.

*lightbulb* beta2. I haven't had much time to dedicate to testing new
betas yet (I was just trying to get a test 8.3 server up and running),
but I will do so. Thanks.

Peter

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Legacy foreign keys

> > [ squint... ] Which 8.3beta are you testing, exactly? This was dealt
> > with in beta3.
> >
> > If it is beta3 or later, please send the output of "pg_dump -s -t wspolicy"
> > from the 8.2 database.
>
> *lightbulb* beta2. I haven't had much time to dedicate to testing new
> betas yet (I was just trying to get a test 8.3 server up and running),
> but I will do so. Thanks.

I finally found more time to test this more extensively. i'm still
running into an issue with this, although it's different this time.
There are no errors printed to the terminal, but neither the foreign
key nor the trigger get made.

8.2 schema for the wspolicy table:
sushi=> \d sushi.wspolicy
Table "sushi.wspolicy"
Column | Type | Modifiers
----------------+--------+-----------
os | text | not null
dump_policy | text | not null
atom_type | text | not null
file_system | text | not null
est_epoch_size | bigint |
Indexes:
"su_wspolicy_pkey" PRIMARY KEY, btree (os, dump_policy, atom_type,
file_system)
Triggers:
"RI_ConstraintTrigger_60045810" AFTER INSERT OR UPDATE ON
sushi.wspolicy FROM sushi.atom_os_policy_values NOT DEFERRABLE
INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_check_ins"('', 'wspolicy', 'atom_os_policy_values',
'UNSPECIFIED', 'os', 'os')
"RI_ConstraintTrigger_60045811" AFTER INSERT OR UPDATE ON
sushi.wspolicy FROM sushi.atom_dump_policy_values NOT DEFERRABLE
INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_check_ins"('', 'wspolicy',
'atom_dump_policy_values', 'UNSPECIFIED', 'dump_policy',
'dump_policy')
"RI_ConstraintTrigger_60045812" AFTER INSERT OR UPDATE ON
sushi.wspolicy FROM sushi.atom_type_values NOT DEFERRABLE INITIALLY
IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_check_ins"('', 'wspolicy', 'atom_type_values',
'UNSPECIFIED', 'atom_type', 'atom_type')

8.3 schema:
sushi=> \d sushi.wspolicy
Table "sushi.wspolicy"
Column | Type | Modifiers
----------------+--------+-----------
os | text | not null
dump_policy | text | not null
atom_type | text | not null
file_system | text | not null
est_epoch_size | bigint |
Indexes:
"su_wspolicy_pkey" PRIMARY KEY, btree (os, dump_policy, atom_type,
file_system)

And the here's the output of pg_dump that was requested:
$ /s/postgresql/bin/pg_dump -h sensei -p 5432 sushi -s -t sushi.wspolicy
--
-- PostgreSQL database dump
--

SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

SET search_path = sushi, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = true;

--
-- Name: wspolicy; Type: TABLE; Schema: sushi; Owner: postgres; Tablespace:
--

CREATE TABLE wspolicy (
os text NOT NULL,
dump_policy text NOT NULL,
atom_type text NOT NULL,
file_system text NOT NULL,
est_epoch_size bigint
);


ALTER TABLE sushi.wspolicy OWNER TO postgres;

--
-- Name: su_wspolicy_pkey; Type: CONSTRAINT; Schema: sushi; Owner:
postgres; Tablespace:
--

ALTER TABLE ONLY wspolicy
ADD CONSTRAINT su_wspolicy_pkey PRIMARY KEY (os, dump_policy,
atom_type, file_system);


--
-- Name: RI_ConstraintTrigger_60045810; Type: TRIGGER; Schema: sushi;
Owner: postgres
--

CREATE CONSTRAINT TRIGGER ""
AFTER INSERT OR UPDATE ON wspolicy
FROM atom_os_policy_values
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins"('', 'wspolicy',
'atom_os_policy_values', 'UNSPECIFIED', 'os', 'os');


--
-- Name: RI_ConstraintTrigger_60045811; Type: TRIGGER; Schema: sushi;
Owner: postgres
--

CREATE CONSTRAINT TRIGGER ""
AFTER INSERT OR UPDATE ON wspolicy
FROM atom_dump_policy_values
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins"('', 'wspolicy',
'atom_dump_policy_values', 'UNSPECIFIED', 'dump_policy',
'dump_policy');


--
-- Name: RI_ConstraintTrigger_60045812; Type: TRIGGER; Schema: sushi;
Owner: postgres
--

CREATE CONSTRAINT TRIGGER ""
AFTER INSERT OR UPDATE ON wspolicy
FROM atom_type_values
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins"('', 'wspolicy',
'atom_type_values', 'UNSPECIFIED', 'atom_type', 'atom_type');


--
-- Name: wspolicy; Type: ACL; Schema: sushi; Owner: postgres
--

REVOKE ALL ON TABLE wspolicy FROM PUBLIC;
REVOKE ALL ON TABLE wspolicy FROM postgres;
GRANT ALL ON TABLE wspolicy TO postgres;
GRANT SELECT ON TABLE wspolicy TO sushi;
GRANT ALL ON TABLE wspolicy TO staff;


--
-- PostgreSQL database dump complete
--

I did see these errors in the postgres syslog pertaining to wspolicy's
foreign keys. It's trying, but it's not working for some reason.

Jan 31 17:21:19 mitchell postgres[3152]: [42-1] NOTICE: ignoring
incomplete trigger group for constraint "" FOREIGN KEY
wspolicy(os) REFERENCES
Jan 31 17:21:19 mitchell postgres[3152]: [42-2] atom_os_policy_values(os)
Jan 31 17:21:19 mitchell postgres[3152]: [42-3] DETAIL: Found
referencing table's trigger.
Jan 31 17:21:19 mitchell postgres[3152]: [43-1] NOTICE: ignoring
incomplete trigger group for constraint "" FOREIGN KEY
wspolicy(dump_policy) REFERENCES
Jan 31 17:21:19 mitchell postgres[3152]: [43-2]
atom_dump_policy_values(dump_policy)
Jan 31 17:21:19 mitchell postgres[3152]: [43-3] DETAIL: Found
referencing table's trigger.
Jan 31 17:21:19 mitchell postgres[3152]: [44-1] NOTICE: ignoring
incomplete trigger group for constraint "" FOREIGN KEY
wspolicy(atom_type) REFERENCES
Jan 31 17:21:19 mitchell postgres[3152]: [44-2] atom_type_values(atom_type)
Jan 31 17:21:19 mitchell postgres[3152]: [44-3] DETAIL: Found
referencing table's trigger.

I'm testing this on 8.3-RC2. If it comes to it, I'm willing to
manually translate the triggers into proper foreign keys.

Peter

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Legacy foreign keys

"Peter Koczan" writes:
> I finally found more time to test this more extensively. i'm still
> running into an issue with this, although it's different this time.
> There are no errors printed to the terminal, but neither the foreign
> key nor the trigger get made.

It looks like you are trying to restore from a selective dump (-t
switch) that includes only one of the two tables involved in the FK
constraint. The backend logic for converting triggers to proper FK
constraints can only work if it sees both tables (and their triggers)
created during a single session.

regards, tom lane

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

Re: Legacy foreign keys

> > I finally found more time to test this more extensively. i'm still
> > running into an issue with this, although it's different this time.
> > There are no errors printed to the terminal, but neither the foreign
> > key nor the trigger get made.
>
> It looks like you are trying to restore from a selective dump (-t
> switch) that includes only one of the two tables involved in the FK
> constraint. The backend logic for converting triggers to proper FK
> constraints can only work if it sees both tables (and their triggers)
> created during a single session.

I see what you're saying, but the weird thing is that this happened
even when I did a full dump/restore of that database. I'll try out a
few different things (like restoring the tables, then restoring the
triggers). Hopefully I'll be able to suss out the issue or at least
find a workaround.

Peter

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Re: Legacy foreign keys

> I see what you're saying, but the weird thing is that this happened
> even when I did a full dump/restore of that database. I'll try out a
> few different things (like restoring the tables, then restoring the
> triggers). Hopefully I'll be able to suss out the issue or at least
> find a workaround.

I have a theory as to what's happening. It can find both tables The
new smarts in the 8.3 server seem to require a "complete" foreign key,
which, I'm guessing, would require additional triggers defining
RESTRICT or CASCADE on UPDATEs and DELETEs.

I think the equivalent command would be:
"ALTER TABLE wspolicy ADD CONSTRAINT "" FOREIGN KEY
(dump_policy) REFERENCES atom_dump_policy_values (os);"

I restored the entire schema, and only the schema, to the 8.3 server.
Then I tried to create the trigger, and it didn't work.

$ /s/postgresql-8.3/bin/pg_dump -h sensei -p 5432 -C --schema-only
sushi | /s/postgresql-8.3/bin/psql -h mitchell -p 5434 postgres
(lots of commands, no errors).

$ /s/postgresql-8.3/bin/psql -h mitchell -p 5434 postgres
sushi=# SET search_path TO sushi;
SET

sushi=# \d wspolicy
Table "sushi.wspolicy"
Column | Type | Modifiers
----------------+--------+-----------
os | text | not null
dump_policy | text | not null
atom_type | text | not null
file_system | text | not null
est_epoch_size | bigint |
Indexes:
"su_wspolicy_pkey" PRIMARY KEY, btree (os, dump_policy, atom_type,
file_system)

sushi=# \d atom_os_policy_values
Table "sushi.atom_os_policy_values"
Column | Type | Modifiers
--------+------+-----------
os | text | not null
Indexes:
"su_atom_os_policy_values_pkey" PRIMARY KEY, btree (os)

sushi=# CREATE CONSTRAINT TRIGGER ""
AFTER INSERT OR UPDATE ON wspolicy
FROM atom_os_policy_values
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins"('', 'wspolicy',
'atom_os_policy_values', 'UNSPECIFIED', 'os', 'os');
NOTICE: ignoring incomplete trigger group for constraint ""
FOREIGN KEY wspolicy(os) REFERENCES atom_os_policy_values(os)
DETAIL: Found referencing table's trigger.
CREATE TRIGGER

Since there are no definitions for what happens for UPDATES or
DELETES, and possibly there's something else, it's considered, an
"incomplete trigger group" for the foreign key, as the NOTICE says.

The other problem is that other legacy FK triggers on that same table
have the same value for the first field, "", which will cause
name conflicts.

I think I've figured it out enough that I could translate the keys
manually if I need to. It's not a big deal to me if handling this case
isn't included in the 8.3 server, since these are relatively few and
far between, but I figured you should know in case other people run
into this problem.

One more thing. Is there any way to quickly find all the old-style
FKs? I tried looking in pg_trigger but it appears that even new,
legitimate foreign keys have triggers driving them.

Peter

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Legacy foreign keys

"Peter Koczan" writes:
> I have a theory as to what's happening. It can find both tables The
> new smarts in the 8.3 server seem to require a "complete" foreign key,
> which, I'm guessing, would require additional triggers defining
> RESTRICT or CASCADE on UPDATEs and DELETEs.

Yeah, it's expecting to see a set of three related triggers. For
instance, if I do this in a 7.0 database:

play=> create table master(f1 int primary key);
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'master_pkey' for table 'master'
CREATE
play=> create table slave(ff int references master);
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE

and then pg_dump it, I get these three triggers:

--
-- Name: RI_ConstraintTrigger_4583989; Type: TRIGGER; Schema: public; Owner: tgl
--

CREATE CONSTRAINT TRIGGER ""
AFTER INSERT OR UPDATE ON slave
FROM master
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_check_ins"('', 'slave', 'master', 'UNSPECIFIED', 'ff', 'f1');


--
-- Name: RI_ConstraintTrigger_4583991; Type: TRIGGER; Schema: public; Owner: tgl
--

CREATE CONSTRAINT TRIGGER ""
AFTER DELETE ON master
FROM slave
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_noaction_del"('', 'slave', 'master', 'UNSPECIFIED', 'ff', 'f1');


--
-- Name: RI_ConstraintTrigger_4583993; Type: TRIGGER; Schema: public; Owner: tgl
--

CREATE CONSTRAINT TRIGGER ""
AFTER UPDATE ON master
FROM slave
NOT DEFERRABLE INITIALLY IMMEDIATE
FOR EACH ROW
EXECUTE PROCEDURE "RI_FKey_noaction_upd"('', 'slave', 'master', 'UNSPECIFIED', 'ff', 'f1');


If your original database doesn't have all three triggers, then you had
a problem already --- the FK constraint wasn't being enforced properly.


> The other problem is that other legacy FK triggers on that same table
> have the same value for the first field, "", which will cause
> name conflicts.

There's a hack to deal with that in the conversion code --- it'll assign
a generated name instead.

> One more thing. Is there any way to quickly find all the old-style
> FKs? I tried looking in pg_trigger but it appears that even new,
> legitimate foreign keys have triggers driving them.

In 8.3, you could look for the pg_trigger entries with zero tgconstraint
values. However, prior versions don't have that column; you'd have to
look instead for entries that aren't linked to a pg_constraint entry
by a pg_depend entry.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Re: Legacy foreign keys

> Yeah, it's expecting to see a set of three related triggers. For
> instance, if I do this in a 7.0 database:
> ...
> If your original database doesn't have all three triggers, then you had
> a problem already --- the FK constraint wasn't being enforced properly.

Ah, these were from before I became the main database admin. It was
probably some previous admin or programmer who thought he could be
clever and drop the other FK-related triggers.

> In 8.3, you could look for the pg_trigger entries with zero tgconstraint
> values. However, prior versions don't have that column; you'd have to
> look instead for entries that aren't linked to a pg_constraint entry
> by a pg_depend entry.

Thanks again, I should have enough info to fix this now.

Peter

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Legacy foreign keys

"Peter Koczan" writes:
>> If your original database doesn't have all three triggers, then you had
>> a problem already --- the FK constraint wasn't being enforced properly.

> Ah, these were from before I became the main database admin. It was
> probably some previous admin or programmer who thought he could be
> clever and drop the other FK-related triggers.

Or it could just be a long-ago oversight. In versions before about 7.3,
it was possible to drop and recreate one of the two tables involved in
an FK relationship without any impact on the other table's FK
trigger(s). Depending on usage patterns you might never notice that
the FK constraint was now only partially enforced.

Hmm, actually the note in the code says that there were two separate
bad things that had to happen to get into this situation:

* The conversion is complex because a pre-7.3 foreign key involved three
* separate triggers, which were reported separately in dumps. While the
* single trigger on the referencing table adds no new information, we need
* to know the trigger functions of both of the triggers on the referenced
* table to build the constraint declaration. Also, due to lack of proper
* dependency checking pre-7.3, it is possible that the source database had
* an incomplete set of triggers resulting in an only partially enforced
* FK constraint. (This would happen if one of the tables had been dropped
* and re-created, but only if the DB had been affected by a 7.0 pg_dump bug
* that caused loss of tgconstrrelid information.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate