Thread: 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.klaver@aklaver.com

Johann Spies <johann.spies@gmail.com> writes:
> 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;"

It's not entirely clear what your question is, but here are some possible
answers:

1. For quite some time now, the "query" column in pg_stat_activity has
been defined as "the query currently or most recently run by the session";
it's intentional that it doesn't go back to "<idle>" anymore.  You need to
look at the "state" column to tell whether the session is actively running
the query or not.

2. Are you sure that operating system user "crest" isn't connecting as
database user "postgres"?

3. pg_cancel_backend() is only supposed to terminate the current query
(if any), not kill the session.  If you want the latter try
pg_terminate_backend().

> Why does this happen?
> Should I do something about it?  If so, what?

AFAICS you have not shown anything behaving out of the ordinary.

            regards, tom lane