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>
>> 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];;
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
In response to
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|