Postgres Rule for cascading delete on single table

[rangi500]

Hi guys,

I've having trouble setting up a rule to delete from a table when a delete is performed on that same table. I do this all the time fine with two tables, but I can't figure out how to do it with one. When dealing with two tables I do this:

CREATE RULE table1_delete AS ON DELETE TO table1 DO DELETE FROM table2 WHERE table2.table1_oid = OLD.oid;

So when an entry in table1 is deleted, and entries referencing it from table2 are also deleted.

Does anyone know how to do this when you're working on just one table? For example if we use this table:

> CREATE TABLE test(
> name VARCHAR(20),
> parent_test_oid OID
> );
>
> INSERT INTO test VALUES ('parent', null);
INSERT 22567908 1
> INSERT INTO test VALUES ('child', 22567908);
INSERT 22567909 1
>
>SELECT oid, * FROM test;
oid | name | parent_test_oid
----------+--------+-----------------
22567908 | parent |
22567909 | child | 22567908

As you can see I have associated the 'child' record with the parent through the value in parent_test_oid. So I try to set up a rule to delete the child if the parent is deleted:

CREATE RULE test_delete AS ON DELETE TO test DO DELETE FROM test WHERE test.parent_test_oid = OLD.oid;

And the rule is created without a problem. But when I delete the 'parent' record, I get this message:

ERROR: query rewritten 100 times, may contain cycles

Which suggests I've caused an infinite loop.

Note that I need to be able to have null values in the parent_test_oid column, so any solution involving rules or constraints would have to take that into account.

If anybody knows how to do this, any help would be very much appreciated.

Best regards,

Rangi

 

[ceco]

i would suggest you to do it without OIDs!! it is not good idea you will have problems even when updating the name of the parent!!


do it like that

CREATE TABLE test(
id serial not null primary key,
parent integer null REFERENCES test(id) ON DELETE CASCADE,
name character varying(128) not null
);

and that's all folks

also you may look at ltree package in contrib, and some other resource for presenting tree structures within RDBMS (although, I can't recomend one now)

 

[gwinn7]

I found this in the documentation that may help...

"It is very important to take care to avoid circular rules. For example, though each of the following two rule definitions are accepted by PostgreSQL, the SELECT command would cause PostgreSQL to report an error because the query cycled too many times:

CREATE RULE "_RETURN" AS
ON SELECT TO t1
DO INSTEAD
SELECT * FROM t2;

CREATE RULE "_RETURN" AS
ON SELECT TO t2
DO INSTEAD
SELECT * FROM t1;

SELECT * FROM t1;

Presently, if a rule action contains a NOTIFY command, the NOTIFY command will be executed unconditionally, that is, the NOTIFY will be issued even if there are not any rows that the rule should apply to. For example, in

CREATE RULE notify_me AS ON UPDATE TO mytable DO ALSO NOTIFY mytable;

UPDATE mytable SET name = 'foo' WHERE id = 42;
one NOTIFY event will be sent during the UPDATE, whether or not there are any rows that match the condition id = 42. This is an implementation restriction that may be fixed in future releases. "

Gary
gwinn7