Re: [HACKERS] Much Ado About COUNT(*) - Mailing list pgsql-general

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yep, that could cause problems.  Okay, now I'm joining the program.

The only thing I can see that would fix this for the integer would be
to keep track of the number of 'committed' records using the integer,
then for each new transaction, make a copy of the integer in order to
remember its "place", using an additional integer to track the offset
(how many rows have been added or removed), so that it can be correctly
applied at commit time.  It's probably too messy to be worthwhile this
way, though.  More trouble than it would be worth.

On Jan 14, 2005, at 1:38 PM, Martijn van Oosterhout wrote:

> On Fri, Jan 14, 2005 at 12:39:04PM -0500, Frank D. Engel, Jr. wrote:
>> This is probably stupid for some reason, but why not use a 64-bit
>> integer to track the number of records in the table? Increment when
>> adding records, decrement when deleting them... then COUNT(*) could
>> just return that in cases where a query is known to be looking at all
>> of the records?
>
> Because there is no single value for count(*), if you're in a
> transaction that has added records it will be bigger than in a
> transaction that hasn't. How does your integer deal with this?
>
> The usual solutions this involve locking, which is precisely what MVCC
> is designed to avoid.
>
> Hope this helps,
> --
> Martijn van Oosterhout   <kleptog@svana.org>
> http://svana.org/kleptog/
>> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is
>> a
>> tool for doing 5% of the work and then sitting around waiting for
>> someone
>> else to do the other 95% so you can sue them.
>>
- -----------------------------------------------------------
Frank D. Engel, Jr.  <fde101@fjrhome.net>

$ ln -s /usr/share/kjvbible /usr/manual
$ true | cat /usr/manual | grep "John 3:16"
John 3:16 For God so loved the world, that he gave his only begotten
Son, that whosoever believeth in him should not perish, but have
everlasting life.
$
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.4 (Darwin)

iD8DBQFB6BPb7aqtWrR9cZoRAjHHAJ9gOp6EOuZtvZATLX+3AUbvhQQmOwCdFF6J
+6JlJKNjrTlYW/8kqu+Z9Xs=
=OV2y
-----END PGP SIGNATURE-----



___________________________________________________________
$0 Web Hosting with up to 120MB web space, 1000 MB Transfer
10 Personalized POP and Web E-mail Accounts, and much more.
Signup at www.doteasy.com