Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] (Manfred Koizar) would write: > On Mon, 24 Jan 2005 08:28:09 -0700, "Jonah H. Harris" <[EMAIL PROTECTED]> > 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/> ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend