Re: Update blocking a select count(*)?

I see! Thank you very much!

~Ben

On Fri, Jun 15, 2012 at 3:51 PM, Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov
> wrote:

> Benedict Holland <benedict(dot)m(dot)holland(at)gmail(dot)com> wrote:
>
> > I can even accept the use case that the select should block with
> > an Alter Table operation if data is retrieved from the table, but
> > a select count(*) only returns the number of rows and should be
> > table space independent.
>
> Just as an example of why the data must be scanned for transactional
> behavior. Open three connections to the same database. On the
> first, run this:
>
> create table t (id int not null);
> insert into t select generate_series(1, 1000000);
> vacuum analyze t;
> begin;
> delete from t where id between 1 and 50000;
>
> Then, on the second, run this:
>
> begin;
> insert into t select generate_series(1000001, 1000600);
>
> Now, run this on each of the three connections:
>
> select count(*) from t;
>
> You should not get the same count on each one. Depending on your
> transactional context, you will get 950000, 1000600, or 1000000.
> Over and over as long as the modifying transactions are open. If
> you want a fast approximation:
>
> select reltuples from pg_class where oid = 't'::regclass;
> reltuples
> -----------
> 1e+06
> (1 row)
>
> -Kevin
>