FAQ
I am using Postgresql 7.2 -- I would like to know how to turn off the
referential integrity of tables so that I can
do a bulk load & then turn the referential integrity back on.

Thanks very much

Pam Wampler

Search Discussions

  • Joshua b. Jore at Apr 8, 2002 at 6:15 pm
    I have two functions for just that trick. I'm retyping this from another
    screen so there may be a typo or two. The DisableTriggers() function does
    more work than it has to - I copied the joins from the EnableTriggers()
    function which does need the extra joins.

    The key is, just set pg_class.reltriggers to either 0 or the correct
    number of triggers.

    Josh

    DROP FUNCTION DisableTriggers();
    CREATE FUNCTION DisableTriggers() RETURNS BOOLEAN AS '
    DECLARE
    Rows INTEGER;
    BEGIN
    UPDATE pg_class SET reltriggers = 0
    FROM (
    (SELECT relname,oid FROM pg_class WHERE relname !~
    ''^pg_'') AS A
    JOIN
    (SELECT count(*),tgrelid FROM pg_trigger GROUP BY tgrelid)
    AS B
    ON (A.oid = B.tgrelid)
    ) AS C
    WHERE pg_class.oid = C.oid;
    GET DIAGNOSTICS Rows = ROW_COUNT;
    IF Rows > 0 THEN
    RETURN TRUE;
    ELSE
    RAISE NOTICE ''Relation does not exist'';
    RETURN FALSE;
    END IF;
    END;
    ' LANGUAGE 'plpgsql' WITH (isstrict);

    DROP FUNCTION EnableTriggers();
    CREATE FUNCTION EnableTriggers() RETURNS BOOLEAN AS '
    DECLARE
    Rows INTEGER;
    BEGIN
    UPDATE pg_class SET reltriggers = C.Count
    FROM (
    (SELECT relname,oid FROM pg_class WHERE relname !~
    ''^pg_'') AS A
    JOIN
    (SELECT count(*),tgrelid FROM pg_trigger GROUP BY tgrelid)
    AS B
    ON (A.oid = B.tgrelid)
    ) AS C
    WHERE pg_class.oid = C.oid;
    GET DIAGNOSTICS Rows = ROW_COUNT;
    IF Rows > 0 THEN
    RETURN TRUE;
    ELSE
    RAISE NOTICE ''Relation does not exist'';
    RETURN FALSE;
    END IF;
    END;
    ' LANGUAGE 'plpgsql' WITH (isstrict);

    Joshua b. Jore
    http://www.greentechnologist.org
    On Mon, 8 Apr 2002, Pam Wampler wrote:

    I am using Postgresql 7.2 -- I would like to know how to turn off the
    referential integrity of tables so that I can
    do a bulk load & then turn the referential integrity back on.

    Thanks very much

    Pam Wampler

    ---------------------------(end of broadcast)---------------------------
    TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
  • Tom Lane at Apr 8, 2002 at 7:20 pm

    Pam Wampler writes:
    I am using Postgresql 7.2 -- I would like to know how to turn off the
    referential integrity of tables so that I can
    do a bulk load & then turn the referential integrity back on.
    There's a little dance you can do involving disabling and then
    re-enabling the triggers that enforce RI. The easiest example is
    to do a "pg_dump -a" (data only) dump from a small table and look
    to see how pg_dump does it. IIRC, you don't need to touch the
    triggers themselves, just hit the pg_class field that says whether
    the table has triggers.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedApr 8, '02 at 5:17p
activeApr 8, '02 at 7:20p
posts3
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase