Thread: Help with trigger

(Postgres 8.4.2)

I have two small tables:

dimensions (id, height, width, depth, weight);
components (id, dimension references dimensions(id));

When dimensions are changed, a new row is inserted in dimensions-table
and the id is updated in the components-table. This means there can be
several dimension-ids that are "orphans" (not referenced by
components). Instead of purging them manually, I read about triggers
and tried to use them.

I did this:

ecdb=> CREATE OR REPLACE FUNCTION dimension_purge() RETURNS "trigger"
AS 'BEGIN DELETE FROM dimensions WHERE id NOT IN (SELECT dimensions
FROM components WHERE dimensions IS NOT NULL); END;' LANGUAGE
'plpgsql';
CREATE FUNCTION
ecdb=> CREATE TRIGGER dimensions_trigger AFTER INSERT OR UPDATE OR
DELETE ON dimensions EXECUTE PROCEDURE dimension_purge();
CREATE TRIGGER

So far so good. I try to insert a dimension to test the trigger:

ecdb=> insert into dimensions (weight, height, depth, width) values
((100*random())::smallint,(100*random())::smallint,(100*random())::smallint,(100*random())::smallint);

here, Postgres hangs (99.9% cpu according to top(1)). I press ctrl+c
and see "Cancel request sent". After a minute or two, the following
appears:

ERROR:  stack depth limit exceeded
HINT:  Increase the configuration parameter "max_stack_depth", after
ensuring the platform's stack depth limit is adequate.
CONTEXT:  SQL statement "DELETE FROM dimensions WHERE id NOT IN
(SELECT dimensions FROM components WHERE dimensions IS NOT NULL)"
PL/pgSQL function "dimension_purge" line 1 at SQL statement
SQL statement "DELETE FROM dimensions WHERE id NOT IN (SELECT
dimensions FROM components WHERE dimensions IS NOT NULL)"
PL/pgSQL function "dimension_purge" line 1 at SQL statement
SQL statement "DELETE FROM dimensions WHERE id NOT IN (SELECT
dimensions FROM components WHERE dimensions IS NOT NULL)"
PL/pgSQL function "dimension_purge" line 1 at SQL statement
SQL statement "DELETE FROM dimensions WHERE id NOT IN (SELECT
dimensions FROM components WHERE dimensions IS NOT NULL)"
PL/pgSQL function "dimension_purge" line 1 at SQL statement
[...]

If I manually run "DELETE FROM dimensions WHERE id NOT IN (SELECT
dimensions FROM components WHERE dimensions IS NOT NULL);" it works
fine - all unused id:s are purged - so this cannot be the error.

Can anyone tell me what I'm doing wrong?


--
- Rikard

Rikard Bosnjakovic <rikard.bosnjakovic@gmail.com> writes:
> I have two small tables:

> dimensions (id, height, width, depth, weight);
> components (id, dimension references dimensions(id));

> When dimensions are changed, a new row is inserted in dimensions-table
> and the id is updated in the components-table. This means there can be
> several dimension-ids that are "orphans" (not referenced by
> components). Instead of purging them manually, I read about triggers
> and tried to use them.

> I did this:

> ecdb=> CREATE OR REPLACE FUNCTION dimension_purge() RETURNS "trigger"
> AS 'BEGIN DELETE FROM dimensions WHERE id NOT IN (SELECT dimensions
> FROM components WHERE dimensions IS NOT NULL); END;' LANGUAGE
> 'plpgsql';
> CREATE FUNCTION
> ecdb=> CREATE TRIGGER dimensions_trigger AFTER INSERT OR UPDATE OR
> DELETE ON dimensions EXECUTE PROCEDURE dimension_purge();
> CREATE TRIGGER

This is a recursive trigger: it fires a DELETE on dimensions, which
causes the trigger itself to be invoked again, etc etc.  Because it's a
statement-level trigger, the fact that the inner invocations don't
actually find anything to delete doesn't stop the recursion.  A DELETE
was executed, so the trigger's supposed to be fired.

From your verbal description I wonder if you shouldn't have the trigger
on components instead.

Also, have you looked into using a foreign key with an ON CASCADE DELETE
action?  That might not have the semantics you want, but if it can be
made to work it beats the heck out of debugging your own triggers ...

            regards, tom lane

On Sun, Aug 15, 2010 at 07:21, Tom Lane <tgl@sss.pgh.pa.us> wrote:

[...]
> From your verbal description I wonder if you shouldn't have the trigger
> on components instead.

Adding a trigger ON UPDATE on the components table solved the problem
- dimensions are now purged automatically as soon as the components
are updated.

Thanks.


--
- Rikard