Re: DB Import Error... - Mailing list pgsql-admin

> when i said "search for the  problematic constraint" i meant to open the
file in a text editor and search for the problematic constraint.

Yes,  I've even opened the dump file and analyzed those constraints.  In
fact, I've already explained in detail about my observations here:
http://archives.postgresql.org/pgsql-admin/2011-04/msg00033.php

As I said in the above link, though many FK constraints were dropped very
long (years) back in our application, but its associated "constraint
triggers" were not dropped properly by PostgreSQL from "pg_trigger" table in
v8.2.3.  After understanding about PostgreSQL system catalog table
(particularly pg_class, pg_trigger, pg_constraint), I wrote a function on my
own to delete the FK constraints that are still referred in pg_trigger
table.

After executing the below function in v8.2.3, I took pg_dump in v8.2.3 and
then imported the SQL dump into v9.0.3.  Now, I don't see any errors while
importing in v9.0.3.

NOTE: Previously, when there was errors while importing in v9.0.3, I could
see all those *dropped* FK constraints were again automatically *recreated*
in v9.0.3.  I confirmed this from 2 places: psql (using \d) and pgAdmin II
tool.  But after executing the below function in v8.0.3 before taking
pg_dump and then import in v9.0.3, I don't see any errors now and also at
the same time, I don't see those dropped FK constraints
reappearing/recreated here.

Any advice/suggestion/remarks/alternatives to my approach are highly
appreciated.

FUNCTION:
CREATE OR REPLACE FUNCTION delete_dropped_fk_constraints_from_pg_trigger()
RETURNS numeric AS'
DECLARE
    v_tgconstrname        VARCHAR;
    v_table1        VARCHAR;
    v_table2        VARCHAR;

    v_child_trigger_count    NUMERIC := 0;
    v_parent_trigger_count    NUMERIC := 0;
C1 CURSOR IS
    SELECT
        DISTINCT tgconstrname,
        (select relname from pg_class where oid = tgrelid) as
table1,
        (select relname from pg_class where oid = tgconstrrelid) as
table2
    FROM
        pg_trigger
    WHERE
        tgisconstraint = true
        AND tgconstrname IS NOT NULL
        AND LENGTH(tgconstrname) > 0
        AND tgconstrname != ''<unnamed>''
        AND tgconstrname NOT IN
            (SELECT conname FROM pg_constraint
                WHERE contype = ''f'')
    ORDER BY 1;
BEGIN
    OPEN C1;
    LOOP
    FETCH C1 INTO v_tgconstrname, v_table1, v_table2;

        IF NOT FOUND THEN
            EXIT;
        END IF;

        RAISE NOTICE ''FK Constraint Name=%, Table1=%, Table2=%'',
v_tgconstrname, v_table1, v_table2;

        SELECT COUNT(*) INTO v_child_trigger_count FROM pg_trigger
        WHERE tgrelid = (SELECT oid FROM pg_class WHERE relname =
LOWER(v_table1)) AND
                tgconstrname = LOWER(v_constr_name);

        SELECT COUNT(*) INTO v_parent_trigger_count FROM pg_trigger
        WHERE tgrelid = (SELECT oid FROM pg_class WHERE relname =
LOWER(v_table2)) AND
                tgconstrname = LOWER(v_constr_name);

        DELETE FROM pg_trigger WHERE LOWER(tgconstrname) =
LOWER(v_constr_name) AND tgrelid IN
        (SELECT oid FROM pg_class WHERE relname IN
(LOWER(v_table1),LOWER(v_table2)));

        UPDATE pg_class SET reltriggers = reltriggers -
v_child_trigger_count WHERE relname = LOWER(v_table1);

        IF (v_table1 <> v_table2) THEN -- This condition is checked
for self-referencing FK constraints
            UPDATE pg_class SET reltriggers = reltriggers -
v_parent_trigger_count WHERE relname = LOWER(v_table2);
        END IF;

    END LOOP;
    CLOSE C1;

    RETURN 1;
END;'
LANGUAGE 'plpgsql';

SELECT delete_dropped_fk_constraints_from_pg_trigger();