Re: selects during vacuum

On Wednesday 16 Jul 2003 8:04 pm, Joe Maldonado wrote:
> Here it is...
> things to note:
> This table contains 1 record allways.
> The record is updated once per second.
> Every 167 seconds a vacuum analyze is run on the table
> After some time it hangs in the analyze and blocks all
> access to that table including selects.
>
>
> # vacuum analyze verbose <table_name>
> # ;
> NOTICE: --Relation <table_name>--
> NOTICE: Pages 513: Changed 1, Empty 0; Tup 80: Vac 0, Keep 79, UnUsed
> 4540. Total CPU 0.02s/0.00u sec elapsed 0.02 sec.
> NOTICE: --Relation pg_toast_18119--
> NOTICE: Pages 0: Changed 0, Empty 0; Tup 0: Vac 0, Keep 0, UnUsed 0.
> Total CPU 0.00s/0.00u sec elapsed 0.00 sec.
> NOTICE: Analyzing <table_name>

OK - this is telling us there are 513 disk pages in use, and 80 tuples (rows)
in use with 4540 not in use (old/deleted versions of rows). There's also an
empty toasted table associated (presumably you have a text field?)

Are you sure there's only one row in the table? Could it be you have 79 old,
long-running transactions seeing older versions of the data?

> We dropped the table and recreated it and things have seemed to be
> working right for now though I have an accelerated simulation of the
> conditions running on another db to see if we can reproduce...

Someone else suggested reindexing (which frankly is what I suspected) but I
don't see an index mentioned above. Must admit I'm puzzled - does the fact
you had 80 tuples above make any sense to you?

--
Richard Huxton