Re: Vacuum not identifying rows for removal..

Hi Joshua,

Thanks for the info...but, what I already have the backend id. I was
trying to get the process id of the client application. The client is
using libpq and running on the same workstation. We have approximately
22 different clients running and it would help to isolate the client
program that is causing the problem.

I was unable to locate the client using the backend server's process id
with lsof and netstat. Really the information should be there...since,
each (I believe) each backend postgreSQL server will service a single
client via a unix socket (in the case where they are collocated on a
unix workstation).

Thanks

Ike

> 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/