Reply-To: <gnanam@zoniac.com>
From: "Gnanakumar" <gnanam@zoniac.com>
To: "'Marcelo Leite'" <leite.marcelo@gmail.com>
Cc: "'Kevin Grittner'" <Kevin.Grittner@wicourts.gov>,
	<pgsql-admin@postgresql.org>, <raghuchennuru@gmail.com>
References: <201104011542.p31Fg6L3025075@rs73.luxsci.com>
	<006201cbf2d1$1ea55730$5bf00590$@com>
	<4D99A788020000250003C21A@gw.wicourts.gov>
	<002501cbf35d$54246310$fc6d2930$@com>
	<4D9AE575020000250003C2F4@gw.wicourts.gov>
	<002b01cbf507$ae806f60$0b814e20$@com>
	<BANLkTinYkB7b1jo7+v+S0=a_uJih29KEzQ@mail.gmail.com>
	<BANLkTin1y+sfs7BhtL01WpMWYh0i7+oh1w@mail.gmail.com>
In-Reply-To: <BANLkTin1y+sfs7BhtL01WpMWYh0i7+oh1w@mail.gmail.com>
Subject: Re: DB Import Error...
Date: Mon, 11 Apr 2011 11:35:10 +0530
Organization: Zoniac Software Private Limited
Message-ID: <003c01cbf80e$6e5dc220$4b194660$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Thread-Index: Acv2sYwUoxtNFdcuRbGVAkUxclzIzwBVhMQw
Content-Language: en-us

> when i said "search for the=A0 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.=20

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 :=3D 0;
	v_parent_trigger_count	NUMERIC :=3D 0;
C1 CURSOR IS
	SELECT=20
		DISTINCT tgconstrname,
		(select relname from pg_class where oid =3D tgrelid) as
table1,
		(select relname from pg_class where oid =3D tgconstrrelid) as
table2
	FROM=20
		pg_trigger
	WHERE=20
		tgisconstraint =3D true=20
		AND tgconstrname IS NOT NULL=20
		AND LENGTH(tgconstrname) > 0=20
		AND tgconstrname !=3D ''<unnamed>''
		AND tgconstrname NOT IN=20
			(SELECT conname FROM pg_constraint=20
				WHERE contype =3D ''f'')=20
	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=3D%, Table1=3D%, Table2=3D%'',
v_tgconstrname, v_table1, v_table2;

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

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

		DELETE FROM pg_trigger WHERE LOWER(tgconstrname) =3D
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 =3D reltriggers -
v_child_trigger_count WHERE relname =3D LOWER(v_table1);

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

	END LOOP;
	CLOSE C1;

	RETURN 1;
END;'
LANGUAGE 'plpgsql';

SELECT delete_dropped_fk_constraints_from_pg_trigger();