> 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 "<unnamed>" 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 "<unnamed>" AFTER INSERT OR UPDATE ON wspolicy FROM atom_os_policy_values NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"('<unnamed>', 'wspolicy', 'atom_os_policy_values', 'UNSPECIFIED', 'os', 'os'); NOTICE: ignoring incomplete trigger group for constraint "<unnamed>" 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, "<unnamed>", 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