Centuries ago, Nostradamus foresaw when mkoi-pg(at)aon(dot)at (Manfred Koizar) would write:
> On Mon, 24 Jan 2005 08:28:09 -0700, "Jonah H. Harris" <jharris(at)tvi(dot)edu>
> wrote:
>>            UPDATE pg_user_table_counts
>>                SET rowcount = rowcount + 1
>>                WHERE schemaname = this_schemaname
>>                    AND tablename = TG_RELNAME;
>
> This might work for small single user applications.  You'll have to keep
> an eye on dead tuples in pg_user_table_counts though.
>
> But as soon as there are several concurrent transactions doing both
> INSERTs and DELETEs, your solution will in the best case serialise
> access to test_tbl or it will break down because of deadlocks.

At that point, what you need to do is to break the process in three:

 1.  Instead of the above, use...

     insert into pg_user_table_counts (rowcount, schemaname,
       tablename) values (1, this_schemaname, TG_RELNAME);

     The process for DELETEs involves using the value -1, of course...

 2.  A process needs to run once in a while that does...

     create temp table new_counts as
        select sum(rowcount), schemaname, tablename from
            pg_user_table_counts group by schemaname, tablename;
     delete from pg_user_table_counts;
     insert into pg_user_table_counts select * from new_counts;

     This process "compresses" the table so that it becomes cheaper to
     do the aggregate in 3.

 3.  Querying values is done differently...

     select sum(rowcount) from pg_user_table_counts where schemaname =
      'this' and tablename = 'that';
-- 
let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];;
http://www.ntlug.org/~cbbrowne/nonrdbms.html
Rules of the  Evil Overlord #118. "If I  have equipment which performs
an  important function,  it  will not  be  activated by  a lever  that
someone  could  trigger  by   accidentally  falling  on  when  fatally
wounded." <http://www.eviloverlord.com/>

In response to

• Re: Much Ado About COUNT(*) at 2005-01-25 07:33:57 from Manfred Koizar

pgsql-announce by date

Next:	From: David Fetter	Date: 2005-01-29 07:04:59
	Subject: == PostgreSQL Weekly News - January 28 2005 ==
Previous:	From: Manfred Koizar	Date: 2005-01-25 07:33:57
	Subject: Re: Much Ado About COUNT(*)

pgsql-hackers by date

Next:	From: Merlin Moncure	Date: 2005-01-25 13:19:05
	Subject: Re: userlock changes for 8.1/8.2
Previous:	From: Christopher Kings-Lynne	Date: 2005-01-25 10:36:34
	Subject: Re: [HACKERS] RQ: Prepared statements used by multiple connections

pgsql-patches by date

Next:	From: Dave Page	Date: 2005-01-25 12:39:35
	Subject: Re: pg_autovacuum Win32 Service startup delay
Previous:	From: Andrew Dunstan	Date: 2005-01-25 11:02:53
	Subject: Re: add soundex difference function to contrib/fuzzystrmatch