Обсуждение: stack depth limit exceeded

I'm getting the following error from Postgres:

  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 "UPDATE _test_changed_table SET updates = updates + 1
WHERE table_name =  $1 "
  PL/pgSQL function "fn_update_changes" line 2
This happens even after I drop and recreate the database.  What's going on is
that for every table in the database we create three triggers, similar to these
for the 'users' table:

tr_insert_users
tr_update_users
tr_delete_users

Each trigger will add 1 to the corresponding insert/update/delete column in a
table which tracks those.  This allows me, when I finish a test run, to check my
"_test_changed_table" table to see what updates, inserts and deletes have
happened on which table:

veure_test=# select * from _test_changed_table;
   id |table_name  |is_static |inserts |updates |deletes
----+-------------+-----------+---------+---------+---------
  4|location    |        1|      1|      0|      0
  8|email       |        0|      0|      0|      0
  1|roles       |        1|      0|      0|      0
 6|users |        1|      0|      0|      0
    ...

(We do this because we then know which tables have been altered on a given test
run and we only rebuild the changed tables, not the entire db. Saves a lot of
time).

This was working fine until this mornings 'stack depth' errors.  Can anyone give
me a pointer as to what's going on?  Is there more information I can provide to
help diagnose this?

Cheers,
Ovid
--
Buy the book         - http://www.oreilly.com/catalog/perlhks/
Tech blog            - http://blogs.perl.org/users/ovid/
Twitter              - http://twitter.com/OvidPerl
Official Perl 6 Wiki - http://www.perlfoundation.org/perl6

On 5 September 2010 10:13, Ovid <curtis_ovid_poe@yahoo.com> wrote:
> I'm getting the following error from Postgres:
>
>  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 "UPDATE _test_changed_table SET updates = updates + 1
> WHERE table_name =  $1 "
>  PL/pgSQL function "fn_update_changes" line 2
> This happens even after I drop and recreate the database.  What's going on is
> that for every table in the database we create three triggers, similar to these
> for the 'users' table:
>
> tr_insert_users
> tr_update_users
> tr_delete_users
>
> Each trigger will add 1 to the corresponding insert/update/delete column in a
> table which tracks those.  This allows me, when I finish a test run, to check my
> "_test_changed_table" table to see what updates, inserts and deletes have
> happened on which table:
>
> veure_test=# select * from _test_changed_table;
>   id |table_name  |is_static |inserts |updates |deletes
> ----+-------------+-----------+---------+---------+---------
>  4|location    |        1|      1|      0|      0
>  8|email       |        0|      0|      0|      0
>  1|roles       |        1|      0|      0|      0
>  6|users |        1|      0|      0|      0
>    ...
>
> (We do this because we then know which tables have been altered on a given test
> run and we only rebuild the changed tables, not the entire db. Saves a lot of
> time).
>
> This was working fine until this mornings 'stack depth' errors.  Can anyone give
> me a pointer as to what's going on?  Is there more information I can provide to
> help diagnose this?

When you say that you have a trigger on "every table", does that
include "_test_changed_table"?  If you have an UPDATE trigger on that
table which fires, it'll result in a recursive trigger.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935