> 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(); -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
