From: Gnanakumar <gnanam@zoniac.com>
To: 'Marcelo Leite' <leite.marcelo@gmail.com>
Cc: 'Kevin Grittner' <Kevin.Grittner@wicourts.gov>
Cc: pgsql-admin@postgresql.org
Cc: raghuchennuru@gmail.com
Subject: Re: DB Import Error...
Date: Mon, 11 Apr 2011 11:35:10 +0530
Message-ID: <003c01cbf80e$6e5dc220$4b194660$@com> (raw)
In-Reply-To: <BANLkTin1y+sfs7BhtL01WpMWYh0i7+oh1w@mail.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>
> 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();
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: gnanam@zoniac.com, leite.marcelo@gmail.com, Kevin.Grittner@wicourts.gov, raghuchennuru@gmail.com
Subject: Re: DB Import Error...
In-Reply-To: <003c01cbf80e$6e5dc220$4b194660$@com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox