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
"<unnamed>" for relation "atom" already exists
Command was: CREATE CONSTRAINT TRIGGER "<unnamed>"
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
"<unnamed>" for relation "results" already exists
Command was: CREATE CONSTRAINT TRIGGER "<unnamed>"
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
"<unnamed>" for relation "results" already exists
Command was: CREATE CONSTRAINT TRIGGER "<unnamed>"
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
"<unnamed>" for relation "offline" already exists
Command was: CREATE CONSTRAINT TRIGGER "<unnamed>"
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
"<unnamed>" for relation "wspolicy" already exists
Command was: CREATE CONSTRAINT TRIGGER "<unnamed>"
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
"<unnamed>" for relation "wspolicy" already exists
Command was: CREATE CONSTRAINT TRIGGER "<unnamed>"
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"('<unnamed>', '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"('<unnamed>', '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"('<unnamed>', '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:
"<unnamed>" 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"('<unnamed>',
'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 "<unnamed>", which ultimately caused the
problem, since all the other triggers wanted to be renamed to
"<unnamed>" as well.

This happened for both the 8.3 and 8.2 pg_dump.

Peter