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.
