Re: [GENERAL] Triggers don't activate when dropping table

cheng shan Mon, 06 Dec 2004 22:43:33 -0800

Note: forwarded message attached.

--- Begin Message ---

The reason I drop the table directly is that:

I am developing a graphical information system. There are many tables inherit from object in the system, such as "map", "line", "rect", "circle", etc. In most time, I use tables inherit from "object" instead of table "object".

I group the "objects" in such manners as "map" contains the drawable objects, such as "rect", "circle". And some "object" maybe consists of other "objects", such as a "rect" is assembled by four "line". Besides there are also many relationships between "objects".

Our principle is to construct a model that reflects the association among objects. We use table "relationship" and tables that inherits from it to record the OID of objects that participate this "relationship". And also, every "object" has a field to record the "relationship"s it participates in. To maintain the above constraint automatically, I define many triggers on every "object" table and every "relationship" table.

To make the system extensible, we provides interface make_object_type( ) to permit the user to create the "object type" inherits from "object". And if the user think the existing table is unappropriate, he may drop it using function destroy_object_type( ) if he has sufficient priviledge.

In the first version, the function destroy_object_type( ) just drop the table only. But when I find the triggers havn't been actived yet, I add the delete setense. This time it becomes even worse, the system throws error message.ERROR: could not open relation with OID 1390714.

To avoid the fatal error, I have no choice but to rollback the function to the original version.

I have written a simplified test cast to verify the system, but it performs as I expected and it's wrong.

Could you help me?

Thanks!

Michael Fuhr <[EMAIL PROTECTED]> wrote:

On Tue, Dec 07, 2004 at 11:44:58AM +0800, cheng shan wrote:

> I have one table named "object" and i many tables that are related
> to the table "object". When a record in "object" is deleted, the
> relative records in other tables should also be erased. To implement
> the above goal, i define many triggers on table "object" to make
> operations automatically.
>
> But i find that the triggers don't activate when i dropping the
> table "object". So there is many garbage information left in the
> system which makes the system unstable.

If you want to fire the triggers then delete all records from the
table before dropping it. But why are you dropping the table?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 9: the p lanner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Do You Yahoo!?
注册世界一流品质的雅虎免费电邮

/****************************************************************************
Function Description:
        _test_setup(): Initialize the environment
        _test_teardown(): Clean up
        _test_object_insert(): Insert records into "object"
        _test_object_update(): Update records of "object"
        _test_object_delete(): Delete records from "object"
        _test_containment_insert(): Insert records into "containment"
        _test_containmnet_delete(): Delete records from "containment"
        _trigger_update_containment(): The trigger on table "object" 
                that is used to update table "containment"
        _trigger_update_object(): The trigger on table "containment" 
                that is used to update table "object"
        _test_delete_and_drop(): Delete records from "object" and drop table 
"object"


        Notice the "!!!!!!"

Usage:
        Before running any test, you should reload test.sql use the command 
        "\i test.sql" for the function will be analysed and cached once it run. 
        If the table was deleted, there will be errors.
        See the results use the command "SELECCT * FROM object;" and 
        "SELECT * FROM containment" to examine the records in table "object"
        and table "containment".
        
        Test inserting records into table "object":
        \i test.sql;
        SELECT _test_setup();
        SELECT _test_object_insert();
        SELECT * FROM object;
        SELECT * FROM containment;
        
        Test updating table "object":
        \i test.sql;
        SELECT _test_setup();
        SELECT _test_object_insert();
        SELECT _test_object_update();
        SELECT * FROM object;
        SELECT * FROM containment;
        
        Test deleting records from "object":
        \i test.sql;
        SELECT _test_setup();
        SELECT _test_object_insert();
        SELECT _test_object_delete();
        SELECT * FROM object;
        SELECT * FROM containment;

        Test inserting records into table "containment":
        \i test.sql;
        SELECT _test_setup();
        SELECT _test_object_insert();
        SELECT _test_containment_insert();
        SELECT * FROM object;
        SELECT * FROM containment;
        
        Test deleting records from table "containment":
        \i test.sql;
        SELECT _test_setup();
        SELECT _test_object_insert();
        SELECT _test_containment_delete();
        SELECT * FROM object;
        SELECT * FROM containment;

!!!!!!  All above test cases can pass. But the following is an exception:
        \i test.sql;
        SELECT _test_setup();
        SELECT _test_object_insert();
        SELECT _test_delete_and_drop();

****************************************************************************/

-----------------------------------------------------------------------------
-- Tests
-----------------------------------------------------------------------------

-- Initialize the test environment
CREATE OR REPLACE FUNCTION _test_setup()
RETURNS void AS $$
BEGIN
        -- Drop table "object"
        IF EXISTS(SELECT * FROM pg_class WHERE relname = 'object') THEN
                DROP TABLE object;
        END IF; 

        CREATE TABLE object
        (
                name name,
                pid oid
        );
        
        -- Drop table "object"
        IF EXISTS(SELECT * FROM pg_class WHERE relname = 'containment') THEN
                DROP TABLE containment;
        END IF; 
        
        CREATE TABLE containment
        (
                parent oid,
                child oid
        );

        CREATE TRIGGER _trigger_update_containment AFTER DELETE OR INSERT OR 
UPDATE ON object 
                FOR EACH ROW EXECUTE PROCEDURE _trigger_update_containment();
        CREATE TRIGGER _trigger_update_object AFTER DELETE OR INSERT ON 
containment 
                FOR EACH ROW EXECUTE PROCEDURE _trigger_update_object();        
        RETURN;
END;
$$ LANGUAGE plpgsql;

-- Clean up
CREATE OR REPLACE FUNCTION _test_teardown()
RETURNS void AS $$
BEGIN
        DROP TABLE object;
        DROP TABLE containment;
        RETURN;
END;
$$ LANGUAGE plpgsql;

-- Insert records into "object"
CREATE OR REPLACE FUNCTION _test_object_insert()
RETURNS void AS $$
DECLARE
        l_pid oid;
BEGIN
        INSERT INTO object(name) VALUES('obj1');
        GET DIAGNOSTICS l_pid = RESULT_OID;
        INSERT INTO object(name, pid) VALUES('obj11', l_pid);
        INSERT INTO object(name, pid) VALUES('obj12', l_pid);
        INSERT INTO object(name) VALUES('obj21');
        INSERT INTO object(name, pid) VALUES('obj22', l_pid);
        INSERT INTO object(name) VALUES('obj23');
        INSERT INTO object(name, pid) VALUES('obj24', l_pid);
        INSERT INTO object(name, pid) VALUES('obj25', l_pid);
        RETURN;
END;
$$ LANGUAGE plpgsql;

-- Update records of "object"
CREATE OR REPLACE FUNCTION _test_object_update()
RETURNS void AS $$
DECLARE
        l_obj1 oid;
        l_obj21 oid;
BEGIN
        SELECT oid INTO l_obj1 FROM object WHERE name = 'obj1';
        SELECT oid INTO l_obj21 FROM object WHERE name = 'obj21';

        -- NEW.pid = OLD.pid = NULL
        UPDATE object SET pid = NULL WHERE name = 'obj21';

        -- NEW.pid = OLD.pid
        UPDATE object SET pid = l_obj1 WHERE name = 'obj22';

        -- OLD.pid = NULL AND NEW.pid <> NULL
        UPDATE object SET pid = l_obj21 WHERE name = 'obj23';

        -- OLD.pid <> NULL AND NEW.pid = NULL
        UPDATE object SET pid = NULL WHERE name = 'obj24';

        -- OLD.pid <> NULL AND NEW.pid <> NULL
        UPDATE object SET pid = l_obj21 WHERE name = 'obj25';

        RETURN;
END;
$$ LANGUAGE plpgsql;

-- Delete records of "object"
CREATE OR REPLACE FUNCTION _test_object_delete()
RETURNS void AS $$
BEGIN
        DELETE FROM object WHERE name = 'obj1';
        RETURN;
END;
$$ LANGUAGE plpgsql;

-- Delete records in "containment"
CREATE OR REPLACE FUNCTION _test_containment_delete()
RETURNS void AS $$
DECLARE
        l_obj12 oid;
BEGIN
        SELECT oid INTO l_obj12 FROM object WHERE name = 'obj12';
        DELETE FROM containment WHERE child = l_obj12;
        RETURN;
END;
$$ LANGUAGE plpgsql;

-- Insert records to "containment"
CREATE OR REPLACE FUNCTION _test_containment_insert()
RETURNS void AS $$
DECLARE
        l_obj11 oid;
        l_obj12 oid;
BEGIN
        SELECT oid INTO l_obj11 FROM object WHERE name = 'obj11';
        SELECT oid INTO l_obj12 FROM object WHERE name = 'obj12';
        INSERT INTO containment(parent, child) VALUES(l_obj11, l_obj12);
        RETURN;
END;
$$ LANGUAGE plpgsql;

--
-- A special test
-- 
CREATE OR REPLACE FUNCTION _test_delete_and_drop()
RETURNS void AS $$
BEGIN
        DELETE FROM object;
        DROP TABLE object;
        RETURN;
END;
$$ LANGUAGE plpgsql;

-----------------------------------------------------------------------------
-- Triggers
-----------------------------------------------------------------------------
-- The trigger on table "object" that is used to update table "containment"
CREATE OR REPLACE FUNCTION _trigger_update_containment()
RETURNS trigger AS $$
DECLARE
        l_rec RECORD;
BEGIN
        IF TG_OP = 'DELETE' THEN
                -- clear all descendants
                FOR l_rec IN SELECT child FROM containment WHERE parent = 
OLD.oid LOOP
                        DELETE FROM object WHERE oid = l_rec.child;
                END LOOP;
                -- delete corresponding record in table containment
                DELETE FROM containment WHERE child = OLD.oid;
        ELSIF TG_OP = 'INSERT' THEN
                -- add record in table containment
                IF NEW.pid NOTNULL THEN
                        INSERT INTO containment(parent, child) VALUES(NEW.pid, 
NEW.oid);
                END IF;         
        ELSIF TG_OP = 'UPDATE' THEN
                -- NEW is equral to OLD
                IF (NEW.pid ISNULL AND OLD.pid ISNULL) OR NEW.pid = OLD.pid THEN
                        RETURN null;
                END IF;
        
                IF OLD.pid ISNULL THEN
                        IF NOT EXISTS(SELECT * FROM containment WHERE child = 
NEW.oid) THEN
                                INSERT INTO containment(parent, child) 
VALUES(NEW.pid, NEW.oid);
                        END IF;
                ELSE
                        IF NEW.pid IS NULL THEN
                                DELETE FROM containment WHERE child = NEW.oid;
                        ELSE
                                UPDATE containment SET parent = NEW.pid WHERE 
child = NEW.oid;
                        END IF;
                END IF;                 
        END IF;
        RETURN null;
END;
$$ LANGUAGE plpgsql;

-- The trigger on table "containment" that is used to update table "object"
CREATE OR REPLACE FUNCTION _trigger_update_object()
RETURNS trigger AS $$
BEGIN
        IF TG_OP = 'DELETE' THEN
                UPDATE object SET pid = null WHERE oid = OLD.child;
        ELSIF TG_OP = 'INSERT' THEN
                UPDATE object SET pid = NEW.parent WHERE oid = NEW.child;
        END IF;
        RETURN null;
END;
$$ LANGUAGE plpgsql;

--- End Message ---

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] Triggers don't activate when dropping table

Reply via email to