Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> You could doubtless maintain a fairly good approximate total this
> >> way, and that would be highly useful for some applications ...
> >> but it isn't COUNT(*).
>
> > With MVCC allowing multiple rows with only one visible, I thought the
> > INSERT/DELETE system would work --- once the delete becomes visible, the
> > change becomes visible.
>
> Oh, you're imagining the cache as being a row in an ordinary table?
> I doubt that could work. Multiple transactions trying to update these
> rows would suffer from contention and deadlock problems, wouldn't they?
Oh, they would, woudn't they. I was thinking of the counter UPDATE as a
DELETE and an INSERT. In fact, when we do UPDATE col SET col = col + 1,
we lock the row only so we know the count. Instead, could we insert
+/-1 records into the cache table that were visible only to the running
transaction, and on commit (ON COMMIT TRIGGER) adjust the cached
aggregate counts without requiring locks?
I know I am just shooting out ideas, but it might give someone else a
better idea.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073