Postgres count problem

count_trigger.sql

--
-- http://wiki.postgresql.org/wiki/Slow_Counting
-- http://www.varlena.com/GeneralBits/49.php
-- http://www.varlena.com/GeneralBits/120.php
-- http://omega-glory.net/2007/12/12/postgresql-count-workaround/
--
-- While waiting for 9.2
-- http://rhaas.blogspot.gr/2011/10/index-only-scans-weve-got-em.html
--


CREATE TABLE IF NOT EXISTS row_counts (
    table_name  text NOT NULL,
    total_rows  bigint,
    PRIMARY KEY (table_name)
);


--
-- Create the count procedure
--
CREATE OR REPLACE FUNCTION count_trigger()
RETURNS TRIGGER AS
$$
  BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE row_counts
      SET total_rows = total_rows + 1
      WHERE table_name = TG_RELNAME;
    RETURN NEW;
  ELSEIF TG_OP = 'DELETE' THEN
    UPDATE row_counts
      SET total_rows = total_rows - 1
      WHERE table_name = TG_RELNAME;
    RETURN OLD;
  END IF;
  END;
$$ LANGUAGE 'plpgsql';


--
-- Add procedure to all tables
--
CREATE OR REPLACE FUNCTION add_count_triggers()
RETURNS void AS
$$
  DECLARE
    rec RECORD;
    q   TEXT;
  BEGIN
    FOR rec IN SELECT relname
               FROM pg_class r JOIN pg_namespace n ON (r.relnamespace = n.oid)
               WHERE relkind = 'r' AND n.nspname = 'public' LOOP
      q := 'DROP TRIGGER IF EXISTS ' || rec.relname || '_count ON ' || rec.relname  ;
      EXECUTE q;
      q := 'CREATE TRIGGER ' || rec.relname || '_count BEFORE INSERT OR DELETE ON ' ;
      q := q || rec.relname || ' FOR EACH ROW EXECUTE PROCEDURE count_trigger()';
      EXECUTE q;
    END LOOP;
  RETURN;
  END;
$$ LANGUAGE 'plpgsql';

--
-- Update row counts (slow)
--
CREATE OR REPLACE FUNCTION init_row_counts()
RETURNS void AS
$$
   DECLARE
      rec   RECORD;
      crec  RECORD;
   BEGIN
      -- Delete rows
      DELETE FROM row_counts WHERE table_name NOT IN (SELECT tablename FROM pg_tables);

      FOR rec IN SELECT relname
               FROM pg_class r JOIN pg_namespace n ON (r.relnamespace = n.oid)
               WHERE relkind = 'r' AND n.nspname = 'public' LOOP

        -- TODO: You can't do a transaction inside a function (really?)
        EXECUTE 'LOCK TABLE ' || rec.relname ||' IN SHARE ROW EXCLUSIVE MODE';
        FOR crec IN EXECUTE 'SELECT count(*) as rows from '|| rec.relname LOOP
            -- nothing here, move along
            NULL;
        END LOOP;

        IF EXISTS (SELECT * FROM row_counts WHERE table_name = rec.relname) THEN
          UPDATE row_counts SET  total_rows = crec.rows WHERE table_name = rec.relname ;
        ELSE
          INSERT INTO row_counts(table_name,total_rows) values (rec.relname, crec.rows) ;
        END IF;
      END LOOP;

   RETURN;
   END;
$$  LANGUAGE 'plpgsql';

--
-- Execute the store procedures
--
SELECT add_count_triggers();
SELECT init_row_counts();
SELECT * FROM row_counts;