Just curious... but often, I have only needed a "fairly" accurate count of rows. As in, outside the context of a transaction. In other words, how difficult would it be to keep track of the number of fully committed and visible rows in a table? I would be willing to accept a limitation such as: I open a transaction, delete a bunch of rows and before comitting my transaction get a row count which returns the number of rows that are in the table "outside of" my transaction (the count would include the rows I have just deleted in my transaction). Would it be difficult to count the number of rows in a table that exist outside of all transactions?
Martijn van Oosterhout wrote:
On Wed, May 15, 2002 at 10:24:55AM -0400, Jean-Luc Lachance wrote:
The real question is:
Why is reltuples only an approximation?
It's only an approximation because it is updated by VACUUM. It's used to
estimate the cost of queries.
Secondly, remember that there is not really a canonical
number-of-tuples-in-a-table. If you start a transaction and insert a row,
you'll see one more row than any other transaction running at the time. If
you're using a trigger to keep a count of the total, you'll get different
answers depending on whether your trigger is deferred or not. I have no idea
what happens if the trigger is not deferred but a transaction aborts.
Deadlock?
I wish people would remember this before declaring the total number of
tuples in a table a trivial problem.
HTH,