Re: Vacuum not identifying rows for removal..

> Any ideas of how to identify the application process that is the
> postgres process (whose id I know). Perhaps I need to turn on a
> different log flag?

select * from pg_stat_activity will give you the pid :)

Joshua D. Drake

>
>
> Thanks
>
> Ike
>
>
>
>
> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Monday, August 21, 2006 2:06 PM
> To: Eamonn Kent
> Cc: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Vacuum not identifying rows for removal..
>
> "Eamonn Kent" <ekent(at)xsigo(dot)com> writes:
>> I am using PostgreSQL 8.1.4 for an embedded application. For some
>> reason, vacuum is not able to identify rows that are candidates for
>> removal (i.e., mark space as available).
>> ...
>> We run auto vacuum and I can see from the logs that it is running
> quite
>> frequently. When I run vacuum full from the psql, I can see that space
>> is not being recovered. I have run vacuum full with the verbose flag
>> set, I can see that messages that indicate the existence of "dead row
>> versions that cannot be removed yet.
>
> This means you've got an open transaction somewhere that could
> potentially still be able to see those rows. Look around for
> applications sitting "idle in transaction".
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/