Re: idle processes

On 08/05/2015 12:38 AM, Johann Spies wrote:
> I have read quite a variety of stuff on the internet about an
> explanation for idle postgresql processes but still do not understand
> the following typical scenario.
>
> This is on Debian (postgresql 9.4.4-1.pgdg80+1).
>
> Running the following (as user crest) on an empty table using psql:
>
> select * from wos.person;
>
> Shows that there are no data in the table. In psql the process ends and
> psql is ready for the next query. However, much later one sees
> something like this when querying pg_stat_activity:
>
> "book_citation";"postgres";3358;"";f;"2015-08-05
> 09:00:02.503117+02";"select * from wos.person;"
>
> Now the process is indicated as one run by the user postgres.
>
>
> As user postgres:
>
>
> postgres=# select pg_cancel_backend(3358);
> pg_cancel_backend
> -------------------
> t
> (1 row)
>
> And I can repeat this but ps (or pg_stat_activity) shows process 3358.
> Now my questions:
>
> Why does this happen?
> Should I do something about it? If so, what?

Depends on what you are trying to accomplish. From here:

http://www.postgresql.org/docs/9.4/static/functions-admin.html

pg_cancel_backend

"Cancel a backend's current query. You can execute this against another
backend that has exactly the same role as the user calling the function.
In all other cases, you must be a superuser."

So it will just cancel a running query, not get rid of the process. If
you want to do that, then:

pg_terminate_backend

"Terminate a backend. You can execute this against another backend that
has exactly the same role as the user calling the function. In all other
cases, you must be a superuser."

>
> Regards
> Johann
> --
> Because experiencing your loyal love is better than life itself,
> my lips will praise you. (Psalm 63:3)

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com