Re: DB Import Error...

["Gnanakumar" <gnanam@xxxxxxxxxx>]

> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin