Message-ID: <4F689FC8.4090908@pinpointresearch.com>
Date: Tue, 20 Mar 2012 08:18:32 -0700
From: Steve Crawford <scrawford@pinpointresearch.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.27) Gecko/20120216 Lightning/1.0b2 Thunderbird/3.1.19
MIME-Version: 1.0
To: =?UTF-8?B?QsOocnRvIMOrZCBTw6hyYQ==?= <berto.d.sera@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Setting the process title, or can I?
References: 
 <CAKwGa_9MOQ1zsFaXcdyaCqbPUNDXhMHPOzpo1SSxME8a5YJ2UA@mail.gmail.com>
In-Reply-To: 
 <CAKwGa_9MOQ1zsFaXcdyaCqbPUNDXhMHPOzpo1SSxME8a5YJ2UA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable

On 03/20/2012 03:14 AM, B=C3=A8rto =C3=ABd S=C3=A8ra wrote:
>
> I currently have an emergency ... As an emergency procedure we have=20
> set a script that each minute has a look at the situation and runs=20
> pg_cancel_backend() against anything that has been waiting for more=20
> than X secs. Then it sleeps one more minute...
...
> Is there anyway I can mark the process running the filter, maybe using=20
> the update_process_title feature? I'd like to have something I can see=20
> from a ps command, grep for it and kill -15 it, without wasting our=20
> scarce resources on one more pg process to use info from pg_stat_activi=
ty.
>
Several things spring to mind.

1. The script that starts the filter can note its pid and set an at-job=20
to kill it after 30 minutes. I've used this technique with success.

2. Create a new role specifically to run the filter. The role is=20
generally visible in ps.

But this all begs the question. Why not:

3. Set the statement_timeout so long-running statements will be=20
automatically killed. This can be done on a per-role basis so simply set=20
it as desired for the role used by the problematic Java program. It is a=20
default and can be changed by the user so if there are individual=20
statements that need a longer timeout you can change just for those=20
statements.

Cheers,
Steve