Re: DB Import Error...

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

 



> The recommended upgrade technique is to us pg_dump (or pg_dumpall)
> from the newer version (9.0.3 in this case) to access the old
> database (running the 8.2.3 server in this case).  If you followed
> some other procedure, like using the 8.2.3 software to dump the
> database, you should try the recommended approach instead.
 
> I generally dump in plain text format (the default) and pipe it to
> psql.

Before trying out to use pg_dump from the newer version (9.0.3 in this
case), I analyzed (.sql dump file) to my knowledge on what's going wrong.
Reason for this analysis is that the same .sql dump file is importing into
8.2.3 successfully without any errors, but why it should error when the same
.sql dump is imported into 9.0.3.  There are 260 occurrences of this type of
error during import (ERROR:  constraint "xxxx" for relation "xxxx" already
exist).  I then located the statement in .sql dump file which was throwing
this error.  It points out here:

CREATE CONSTRAINT TRIGGER ifb_ci_ciid_ciid_fk
     AFTER UPDATE ON consultantinterview
     FROM interviewfeedback
     NOT DEFERRABLE INITIALLY IMMEDIATE
     FOR EACH ROW
     EXECUTE PROCEDURE "RI_FKey_noaction_upd"('ifb_ci_ciid_ciid_fk',
'interviewfeedback', 'consultantinterview', 'FULL', 'consultantinterviewid',
'consultantinterviewid');

After seeing this statement, I was shocked and surprised that the constraint
"ifb_ci_ciid_ciid_fk" was dropped very long (years) back in our application
and it's still being referred internally somewhere by PostgreSQL. I then
tried to query from "pg_trigger" table:  "select * from pg_trigger where
tgconstrname = 'ifb_ci_ciid_ciid_fk' " and I'm seeing 9 records with
"tgname" starting with " RI_ConstraintTrigger_xxxx".  Also, on describing
table using \d, I don't see this constraint listed in "Foreign-key
constraints" section, but I could see this constraint listed in "Triggers"
section starting with RI_ConstraintTrigger_xxxx.

What I could infer from this based on my knowledge is that the FK
constraints are dropped from the table but its associated constraint
triggers are not dropped properly.

How do I resolve/clean-up our database at this stage?



-- 
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


[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