Re: Much Ado About COUNT(*)

Centuries ago, Nostradamus foresaw when mkoi-pg@aon.at (Manfred Koizar) would write:
> On Mon, 24 Jan 2005 08:28:09 -0700, "Jonah H. Harris" <jharris@tvi.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_countsgroup by schemaname, tablename;    delete from pg_user_table_counts;    insert into
pg_user_table_countsselect * 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/>