postgres. plpgsql stack depth limit exceeded

Question

im working on a simple function where it automatically updates something from a table.

create or replace function total() 
returns void as $$
declare
 sum int;
begin
 sum = (SELECT count(copy_id) FROM copies);
 update totalbooks
 set all_books = sum
 where num = 1;
 end;
$$ language plpgsql;

if i execute "select total();" it works perfectly fine so i made a function trigger so that it automatically updates:

create or replace function total1() returns trigger as $$
begin
 perform (select total());
 return null;
end;
$$ language plpgsql;

but after i execute this:

create trigger total2
after update
on totalbooks
for each row
execute procedure total1();

it gives me an error message:

ERROR: stack depth limit exceeded
HINT: Increase the configuration parameter "max_stack_depth" (currently 3072kB), after ensuring the platform's stack depth limit is adequate.
CONTEXT: SQL statement "SELECT (SELECT count(copy_id) FROM copies)"
PL/pgSQL function total() line 5 at assignment
SQL statement "SELECT (select total())"
PL/pgSQL function total1() line 3 at PERFORM
SQL statement "update totalbooks
set all_books = sum
where num = 1"
PL/pgSQL function total() line 6 at SQL statement
SQL statement "SELECT (select total())"
PL/pgSQL function total1() line 3 at PERFORM
SQL statement "update totalbooks
set all_books = sum
where num = 1"
PL/pgSQL function total() line 6 at SQL statement
SQL statement "SELECT (select total())"
PL/pgSQL function total1() line 3 at PERFORM
SQL statement "update totalbooks
set all_books = sum
where num = 1"
PL/pgSQL function total() line 6 at SQL statement
SQL statement "SELECT (select total())"
PL/pgSQL function total1() line 3 at PERFORM
SQL statement "update totalbooks
set all_books = sum
where num = 1"
PL/pgSQL function total() line 6 at SQL statement
SQL statement "SELECT (select total())"
PL/pgSQL function total1() line 3 at PERFORM
SQL statement "update totalbooks
set all_books = sum
where num = 1"
PL/pgSQL function total() line 6 at SQL statement
SQL statement "SELECT (select total())"
PL/pgSQL function total1() line 3 at PERFORM
SQL statement "update totalbooks
set all_books = sum
where num = 1"
PL/pgSQL function total() line 6 at SQL statement
SQL statement "SELECT (select total())"

Obviously theres something wrong with my trigger. Please help.

I am using Postgres 9.2.4, compiled by Visual C++ build 1600, 64-Bit

EDIT:

i tried the pg_trigger_depth(), but now trigger doesn't automatically update?? i still have to execute 'select total()'

here's my new code:

create or replace function total() 
returns void as $$
declare
 sum int;
begin
 sum = (SELECT count(copy_id) FROM copies);
 update totalbooks
 set all_books = sum;
end;
$$ language plpgsql;
create or replace function total1() returns trigger as $$
begin
 perform (select total());
 return null;
end;
$$ language plpgsql;
create trigger total2
after update
on totalbooks
for each row
WHEN (pg_trigger_depth()=0)
execute procedure total1();

• user330315: Your trigger is fired by an update on totalbooks which then updates the table totalbooks which in turn fires the trigger on totalbooks which updates the table totalbook which fires the trigger on totalbooks which then updates the table totalbooks which then fires the trigger on totalbooks which updates the table totalbook which will fire the trigger ... (you get the picture)
• user2410258: @a_horse_with_no_name so it's like doing things over and over and over again?? so how do i correct this one?? any ideas??

Answer 1

Ok, if you really want the trigger on update, what you could do it set this trigger as column specific, so that it is not fired on an update to all_books, which is causing your recursion. Something like this -

create trigger total2
after update of copy_id
on totalbooks
for each row
execute procedure total1();

Of course, you can change which columns trigger the function, I just chose copy_id because that is what you are counting.

HOWEVER

If you are updating with a count() result, you can just put the trigger on INSERT and DELETE actions. This way the trigger will fire when the count changes, but will not itself be triggered by the update. // EDIT: Since your sum is only a count of all records in copies, it will only change when a record is inserted or updated, so running this trigger on update would not make sense anyway.

EDIT: I figured it would be useful to add a link to the CREATE TRIGGER Documentation. See the section labeled "event", because this details how to specify columns in the event.

EDIT FOR NEW INFORMATION:

Given what it sounds like you need to accomplish, I think you need to rethink your data design, I suggest you use a parent-child relationship (Anytime you are caching shared data on many rows in a table because they share something in common, that is a sign that you might need a parent table instead).

Have a books table where each row is information about one book (title, author, etc), and then have a copies table where each row holds information about one copy of a book (serial number, last checked out, etc).

That way, getting the count of copies is as simple as SELECT COUNT(*) FROM copies WHERE book_id=[some book id].

If you really want to cache the count somewhere, do it on the books table.

Create an INSERT OR UPDATE trigger on copies that does UPDATE books SET copy_count=(SELECT COUNT(*) FROM copies WHERE book_id=NEW.book_id) WHERE id=NEW.book_id.

Then create a DELETE trigger on copies that does UPDATE books SET copy_count=(SELECT COUNT(*) FROM copies WHERE book_id=OLD.book_id) WHERE id=OLD.book_id

The reason for two triggers is that the NEW variable is only available in INSERT or UPDATE triggers, and OLD is only available in DELETE triggers. You could do it all as one trigger, but that requires more code than I wanted to put here.

Make sure all your triggers are AFTER triggers, or else a newly inserted/deleted row won't be considered in the count.

• user2410258: Okay im going to read it as soon as possible. Going to ask some questions after I read the link you gave me. Thank you.
• user2410258: Done reading and trying everything. I specified columns yet it still didnt update by itself.
• Scott S: What columns did you specify, and what SQL statement did you use to test the trigger?
• user2410258: after update of all_books and the sql statement to insert the trigger: insert into copies(book_id) values('BI6');
• Scott S: Your SQL is an INSERT statement, but your trigger is set to fire on UPDATE. That SQL statement will never fire your trigger, you would have to update an existing row (something like UPDATE copies SET book_id='BI6' WHERE ID=1). Also, thats what I was getting at in the second part of my answer - you probably want something like AFTER INSERT OR DELETE instead of AFTER UPDATE because you're doing a count with no constraints, and the result of that can only change after an insert or update.

Answer 2

Look into pg_trigger_depth:

http://www.postgresql.org/docs/9.2/static/functions-info.html

(9.2 or later)

• user2410258: T cant make it work?? how do i put pg_trigger_depth() in my functions??
• Denis de Bernardy: add a check on the depth and return if it's recursive.
• user2410258: i edited my original post, and also posted the new code with the pg_trigger_depth() but im still not sure if i made the right code. can you check? please? thanks!
• Denis de Bernardy: Inside your function, conditionally call perform total() if depth <= 1 (or maybe 2)

Answer 3

Inside your trigger:

IF pg_trigger_depth() < 2 THEN
 PERFORM total();
END IF;