Skip site navigation (1) Skip section navigation (2)

Help with trigger

From: Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Help with trigger
Date: 2010-08-14 21:30:03
Message-ID: AANLkTi==W+adptFjfXONFG0zHkjveweFEuHjt4BqUrr=@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
(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

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2010-08-15 05:21:42
Subject: Re: Help with trigger
Previous:From: Tom LaneDate: 2010-08-13 13:38:37
Subject: Re: Finding deadlocks on postgresql ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group