Am Donnerstag, den 11.08.2005, 20:36 +1000 schrieb Gavin Sherry: > On Thu, 11 Aug 2005, Tino Wildenhain wrote: > > > Am Donnerstag, den 11.08.2005, 00:40 -0400 schrieb Mark Cotner: > > > Here's a trigger I wrote to perform essentially the same purpose. The > > > nice > > > thing about this is it keeps the number up to date for you, but you do > > > incur > > > slight overhead. > > ... > > > > > > CREATE TRIGGER del_rowcount_tr BEFORE DELETE ON test FOR EACH ROW EXECUTE > > > PROCEDURE del_rowcount(); > > > CREATE TRIGGER add_rowcount_tr BEFORE INSERT ON test FOR EACH ROW EXECUTE > > > PROCEDURE add_rowcount(); > > > > > > INSERT INTO rowcount (tablename) VALUES ('test'); > > ... > > > > beware of problems with concurrency and even what happens > > if transactions roll back. Maybe you can "fix" it a bit > > by regulary correcting the count via cronjob or so. > > What problems? MVCC takes care of this.
Actually in this case MVCC works against you. Just imagine some competing transactions to insert end delete at will. You could lock the count table to prevent the problem where 2 competing transactions do an insert, read the start value and add 1 to it and then write the result - which is n+1 rather then n+2 - so you are off by one. Think of the same when one transaction inserts 100 and the other 120. Then you could even be off by 100. But locking probably gets your worser performance then simply count(*) all the time if you insert a lot. Also prepare for the likeness of deadlocks. ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match