Re: [GENERAL] postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting - Mailing list pgsql-general

On 06/29/2017 10:03 AM, DrakoRod wrote:

To expand information, the application are written in Grails on wildfly with
pool connections.

Do you control the app?

The app has a pooling component and you still are having problems, have you looked at what the pooler is actually doing?

I didn't have time to check pg_locks with detail, I'll configure the
connections logs to monitoring those.

I can't close connections on the application side. How I close connections
on the database side? With pg_terminate_backend, pg_cancel_backend or exists
other function? I didn't want terminate backends because all connections
state was active.

https://www.postgresql.org/docs/9.4/static/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL

"pg_cancel_backend(pid int)     boolean         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.

...The process ID of an active backend can be found from the pid column of the pg_stat_activity view, ..."

I refer only to "idle" because almost in every database that I've saw the
application doesn't close correctly the connections. If are "idle in
transaction" is not normal.

Not sure what the above means. Are you saying the application you refer to above has a history of not correctly closing connections or are you talking in general terms about applications interacting with databases.

Your right Adrian, I need to know why the connections are not closing
properly.

I can't apply idle_in_transation_session_timeout because the version of
PostgreSQL is 9.4.4 and the paramater not yet i'ts included. But sounds good
the upgrade.

Thanks for your help!




-----
Dame un poco de fe, eso me bastará.
Rozvo Ware Solutions
--
View this message in context: http://www.postgresql-archive.org/postgres-dbname-dbuser-9-9-9-9-2222-PARSE-waiting-tp5968923p5969262.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

DrakoRod,

If you are concerned that the application is leaving connections 'idle in transaction', I've attached two files that may be helpful to you.

The first is a SQL query -> current_queries.sql  'idle in transaction' will appear in the "state" column.

CAVEAT EMPTOR! in PG 9.6 the "CASE WHEN waiting ..."  needs to be commented out..

The second is a Linux script "kill_long_idles.sh" that will kill any connection that is 'idle in transaction' for longer than max_time.
Currently it is set to 30 minutes, but you can adjust to your desire.
Just run it from a root cron job.

Don't forget to
  chmod +x  kill_long_idles.sh

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general