Re: Legacy foreign keys - Mailing list pgsql-admin

> 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