Skip site navigation (1) Skip section navigation (2)

Re: Legacy foreign keys

From: "Peter Koczan" <pjkoczan(at)gmail(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Alvaro Herrera" <alvherre(at)alvh(dot)no-ip(dot)org>, pgsql-admin(at)postgresql(dot)org
Subject: Re: Legacy foreign keys
Date: 2007-12-12 03:01:18
Message-ID: 4544e0330712111901k1fa50c5dgb26287aff018e98c@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
> 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

In response to

Responses

pgsql-admin by date

Next:From: Tom LaneDate: 2007-12-12 03:06:10
Subject: Re: Legacy foreign keys
Previous:From: Elvis HenrĂ­quezDate: 2007-12-12 02:34:52
Subject: Re: (Pl/SQL) Obtaining field names from record 'variable'

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group