Re: Legacy foreign keys

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> 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

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

[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux