Re: postgres: dbname dbuser 9.9.9.9[2222] PARSE waiting

On 06/27/2017 04:16 PM, DrakoRod wrote:
> Yep, the real problem was all connections are used up. A ps command showed
> this:
>
> postgres 1172 23340 1 13:00 ? 00:01:23 postgres: dbsomething
> dbsomething 8.8.8.1[34024] PARSE waiting
> postgres 1527 23340 3 13:07 ? 00:02:47 postgres: dbsomething
> dbsomething 8.8.8.2[49193] PARSE waiting

Hmm, the above is new one to me. Some searching found this:

https://www.postgresql.org/message-id/1282602153-sup-6272%40alvh.no-ip.org

"It means the parse phase is waiting for a lock. You can see exactly
what it's waiting for by looking at pg_locks "WHERE NOT GRANTED"."

If you have not already, you might want to log
connections/disconnections for more insight:

https://www.postgresql.org/docs/9.6/static/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT
>
>
> When I ran *SELECT * FROM pg_stat_activity*, the state in all queries was
> active and most were SELECTs, then the server did not open new connections.
> I canceled many queries (only SELECTs) and server back to normal.
>
> I understand that the principal problem probably are the application, of
> that I'm sure, but in the process debug. The best way to avoid or "fix" this
> are with connections pool like pgbouncer? How is the most secure way to
> return connections without restart service?

Close the connection.

>
> I never had this problem, the idle connections is the normal in almost every
> database I managed, but this is new for me.

FYI there is a difference between 'idle' connections and 'idle in
transaction', not sure which one you are referring to. See below for
more info:

https://www.postgresql.org/docs/9.6/static/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

The bottom line is connections are resources that need to be managed. To
better able to do that is going to require some detective work to
determine what is generating the connections and for what purpose.

>
> Thanks for your help!
>
>
>
>
> -----
> Dame un poco de fe, eso me bastará.
> Rozvo Ware Solutions

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com