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@sss.pgh.pa.us]
> Sent: Monday, August 21, 2006 2:06 PM
> To: Eamonn Kent
> Cc: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Vacuum not identifying rows for removal..
>
> "Eamonn Kent" <ekent@xsigo.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/