Hello!
Every one.
I met some difficult questions when I was developing an application.
We have an old table object whose definition is:
CREATE TABLE object (
name name, -- object’s name
pid oid, -- parent’s object ID
PRIMARY KEY(oid),
FOREIGN KEY(pid)
REFERENCES object(oid)
ON DELETE SET NULL
);
The table “object” is used as a base table and there are many tables(over 1500) that is derived from it for we treat almost every thing as object. Though the idea isn’t bad and it can really simplify our conceptual mode, it also results in low performance. When we want to find an object from table “object”, it will perform searching in 1500 tables, the long time to wait is insufferable. And the request that gives a pid to find childen oid is called frequently.
So we decide to reimplement the system and now we treat parent-child relationship is a common relationship. The relationship is named “containment”. For the “pid” field is wild used by other programmers, we can’t delete it. Then we have a task to keep the two table existent. To reduce the possibility to make mistakes, we define triggers on every table.
Now the system can work well when we do “insert”, “delete” or “update” operation. But when I drop a table that is inherits from “object” or the table “object” itself, the triggers on the table will not work. To avoid such cases, I wrote a function “_test_delete_and_drop()” that will delete all columns in table “object” and then drop the table. But I met puzzling information when I do that.
I have two questions:
1. Why don’t the triggers work when drop a table?
2. I can do “delete” and “drop” without any problem. Why do the error come out when I combine the two operation together?
Note: The source code is in the accessory(test.sql).
Could any one help?
Any effort will be appreciated.
Thanks very much!
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"
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
PERFORM _helper_drop_table('object');
CREATE TABLE object
(
name name,
pid oid
);
PERFORM _helper_drop_table('containment');
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;
-----------------------------------------------------------------------------
-- Helper functions
-----------------------------------------------------------------------------
/*
Judge a table exists or not
*/
CREATE OR REPLACE FUNCTION _helper_is_table_existent(name)
RETURNS boolean AS $$
BEGIN
RETURN EXISTS(SELECT * FROM pg_class WHERE relname = $1);
END;
$$ LANGUAGE plpgsql;
/*
Drop a table and its descendants by name
*/
CREATE OR REPLACE FUNCTION _helper_drop_table(name)
RETURNS void AS $$
BEGIN
IF _helper_is_table_existent($1)
THEN
EXECUTE 'DROP TABLE ' || quote_ident($1) || ' CASCADE';
END IF;
RETURN;
END;
$$ LANGUAGE plpgsql;
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
