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();

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.