From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
Cc: | DrakoRod <drakoflames(at)hotmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting |
Date: | 2017-06-29 23:59:21 |
Message-ID: | CANu8Fiw0R1Q==_FYPZhHJip-_Qj8AFWEKJnL65DXnre7R29P+A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, Jun 29, 2017 at 7:30 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)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.
Attachment | Content-Type | Size |
---|---|---|
current_queries.sql | text/plain | 427 bytes |
kill_long_idles.sh | application/x-sh | 1.2 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Steven Chang | 2017-06-30 01:07:17 | Re: duplicate key value violates unique constraint and duplicated records |
Previous Message | Adrian Klaver | 2017-06-29 23:38:19 | Re: duplicate key value violates unique constraint and duplicated records |