On Thu, Jun 29, 2017 at 7:30 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
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-tp5968 923p5969262.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
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.CAVEAT EMPTOR! in PG 9.6 the "CASE WHEN waiting ..." needs to be commented out..The first is a SQL query -> current_queries.sql 'idle in transaction' will appear in the "state" column.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.
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@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.![]()
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
SELECT backend_start as be_start,
datname,
pid as pid,
client_addr,
usename as user,
state,
query,
CASE WHEN waiting = TRUE
THEN 'BLOCKED'
ELSE 'no'
END as waiting,
query_start,
current_timestamp - query_start as duration
FROM pg_stat_activity
WHERE pg_backend_pid() <> pid
ORDER BY 1,
datname,
query_start;
Attachment:
kill_long_idles.sh
Description: Bourne shell script
-- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
