Re: Seqscan in MAX(index_column) - Mailing list pgsql-hackers

From Bruce Momjian
Subject Re: Seqscan in MAX(index_column)
Date
Msg-id 200309050349.h853nN228200@candle.pha.pa.us
Whole thread Raw
In response to Re: Seqscan in MAX(index_column)  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
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
 


pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Seqscan in MAX(index_column)
Next
From: Christopher Browne
Date:
Subject: Re: Seqscan in MAX(index_column)