Thread: Checking FKs after COPY and disabled Triggers

hi PGurus,

i searched the archives and read the docs, because this problem shouldn't be
new. But i really don't know what to search for.

i am populating a database (v7.4.1) with COPY. Some triggers in the db schema
fire on inserts and do some additional data manipulations, which i dont want
to take place by running COPY because my data don't need those additional
manipulation. Therefor and for performance reasons, i disable all triggers
with commands like this (stolen from pg_dump output)

UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid =
'Customers'::pg_catalog.regclass;

of course i enable them afterwards. This does in fact diable Foreign Key
Constraints too, right? And that's nice because i think its much faster.

My problem: my COPY data files are build by perl scripts which might be buggy.
i would like to check that all FKs are correct after or while populating the
data.

1. Can i disable my triggers without disabling FK constraints? if yes, how?

2. If no to question 1 or lacks performance:
can i check my database for violating FKs if i populated the database with FK
constraints disabled? I guess there is a SQL query which consults some
pg_catalog tables and checks all fk integrity

Any help is very appreciated.

kind regards,
janning

Janning Vygen <vygen@gmx.de> writes:
> can i check my database for violating FKs if i populated the database with FK
> constraints disabled?

Drop the constraints and then add them back afterwards.  You might want
to think about dropping and rebuilding indexes as well.

            regards, tom lane

Am Donnerstag, 1. April 2004 16:24 schrieb Tom Lane:
> Janning Vygen <vygen@gmx.de> writes:
> > can i check my database for violating FKs if i populated the database
> > with FK constraints disabled?
>
> Drop the constraints and then add them back afterwards.  You might want
> to think about dropping and rebuilding indexes as well.

many thanks. Now i got it.  But now i have another question:

Is there a way to drop all foreign keys and indices and reinstall them after
COPY finished? Maybe it can be done  with a magic update statement or sql
function. And maybe somebody has already written it or can tell me that this
completey nonsens? I dont want to alter my schema by hand and write hundreds
of ALTER TABLE statements just to have a faster COPY statement.

kind regards
janning

-------
just for my own clarification i tried both methods for the first problem
above. Maybe it clarifies some other brains, too.

It works with ALTER TABLE .. DROP CONSTRAINT and ALTER TABLE ADD CONSTRAINT
like this:
-------
CREATE TABLE foo (foo text);
CREATE TABLE bar (bar text);
COPY foo from stdin DELIMITERS '|' NULL AS '';
a
b
\.
COPY bar from stdin DELIMITERS '|' NULL AS '';
c
d
\.
ALTER TABLE bar ADD CONSTRAINT fk_foo FOREIGN KEY (bar) REFERENCES foo (foo);
-------

results in

ERROR:  insert or update on table "bar" violates foreign key constraint
"fk_foo"
DETAIL:  Key (bar)=(c) is not present in table "foo".


But manipulating the pg_catalog tables like with UPDATE statements like
pg_dump does:

-------
\connect - postgres

CREATE TABLE foo (foo text PRIMARY KEY);
CREATE TABLE bar (bar text REFERENCES foo(foo));

UPDATE pg_catalog.pg_class SET reltriggers = 0 WHERE oid =
'bar'::pg_catalog.regclass;

COPY foo from stdin DELIMITERS '|' NULL AS '';
a
b
\.

COPY bar from stdin DELIMITERS '|' NULL AS '';
c
d
\.

UPDATE pg_catalog.pg_class SET reltriggers=(SELECT pg_catalog.count(*)
FROM pg_catalog.pg_trigger where pg_class.oid = tgrelid)
WHERE oid = 'bar'::pg_catalog.regclass;
-------

Runs without error because FK are enabled but of course not checked when they
are enabled again.

Ok just wrote this mail for my own clarification, maybe its not of any use for
the rest of the world...