On Thu, Jun 29, 2017 at 7:30 PM, Adrian Klaver <adrian.kla...@aklaver.com> 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. >> >> >> > > > > *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.kla...@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 > -- *Melvin Davidson* 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;
kill_long_idles.sh
Description: Bourne shell script
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
