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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-08-15 05:21:42 | Re: Help with trigger |
Previous Message | Tom Lane | 2010-08-13 13:38:37 | Re: Finding deadlocks on postgresql ? |