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

On Tue, 27 Jun 2017 16:16:53 -0700 (MST)
DrakoRod <drakoflames@hotmail.com> 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
> postgres  1869 23340  1 13:13 ?        00:01:05 postgres: dbsomething
> dbsomething 8.8.8.1[34209] PARSE waiting
> postgres  1963 23340  0 13:15 ?        00:00:23 postgres: dbsomething
> dbsomething 8.8.8.1[34244] PARSE waiting
> postgres  2408 23340  2 13:23 ?        00:01:31 postgres: dbsomething
> dbsomething 8.8.8.3[38324] PARSE waiting
> postgres  2442 23340  3 13:23 ?        00:02:19 postgres: dbsomething
> dbsomething 8.8.8.3[38359] PARSE waiting
> postgres  2526 23340  2 13:25 ?        00:01:39 postgres: dbsomething
> dbsomething 8.8.8.2[49994] PARSE waiting
> postgres  2533 23340  2 13:25 ?        00:02:00 postgres: dbsomething
> dbsomething 8.8.8.4[58916] PARSE waiting
> postgres  2616 23340  2 13:26 ?        00:01:28 postgres: dbsomething
> dbsomething 8.8.8.3[38496] PARSE waiting
> postgres  2632 23340  3 13:27 ?        00:02:09 postgres: dbsomething
> dbsomething 8.8.8.2[50088] idle in transaction
> postgres  2644 23340  0 13:27 ?        00:00:25 postgres: dbsomething
> dbsomething 8.8.8.4[58999] PARSE waiting
> postgres  2787 23340  0 13:30 ?        00:00:16 postgres: dbsomething
> dbsomething 8.8.8.5[57944] PARSE waiting
> postgres  2815 23340  1 13:31 ?        00:00:52 postgres: dbsomething
> dbsomething 8.8.8.2[50263] PARSE waiting
> postgres  2822 23340  0 13:31 ?        00:00:29 postgres: dbsomething
> dbsomething 8.8.8.4[59158] PARSE waiting
> postgres  2825 23340  1 13:31 ?        00:00:47 postgres: dbsomething
> dbsomething 8.8.8.4[59161] PARSE waiting
> postgres  2826 23340  0 13:31 ?        00:00:11 postgres: dbsomething
> dbsomething 8.8.8.4[59163] PARSE waiting
> postgres  2876 23340  0 13:32 ?        00:00:26 postgres: dbsomething
> dbsomething 8.8.8.1[34469] PARSE waiting
> postgres  2888 23340  0 13:32 ?        00:00:36 postgres: dbsomething
> dbsomething 8.8.8.3[38729] PARSE waiting
> postgres  2911 23340  0 13:33 ?        00:00:11 postgres: dbsomething
> dbsomething 8.8.8.2[50352] PARSE waiting
> postgres  2912 23340  0 13:33 ?        00:00:36 postgres: dbsomething
> dbsomething 8.8.8.2[50353] PARSE waiting
> postgres  2916 23340  0 13:33 ?        00:00:30 postgres: dbsomething
> dbsomething 8.8.8.3[38750] PARSE waiting
> postgres  2922 23340  0 13:33 ?        00:00:33 postgres: dbsomething
> dbsomething 8.8.8.4[59238] PARSE waiting
> postgres  2927 23340  1 13:33 ?        00:00:38 postgres: dbsomething
> dbsomething 8.8.8.4[59242] PARSE waiting
> postgres  3012 23340  0 13:35 ?        00:00:03 postgres: dbsomething
> dbsomething 8.8.8.2[50439] PARSE waiting
> postgres  3017 23340  0 13:35 ?        00:00:01 postgres: dbsomething
> dbsomething 8.8.8.3[38833] PARSE waiting
> postgres  3018 23340  0 13:35 ?        00:00:27 postgres: dbsomething
> dbsomething 8.8.8.3[38834] PARSE waiting
> postgres  3020 23340  0 13:35 ?        00:00:24 postgres: dbsomething
> dbsomething 8.8.8.4[59318] PARSE waiting
> postgres  3026 23340  0 13:35 ?        00:00:04 postgres: dbsomething
> dbsomething 8.8.8.4[59323] PARSE waiting
> postgres  3033 23340  0 13:35 ?        00:00:15 postgres: dbsomething
> dbsomething 8.8.8.4[59328] PARSE waiting
>
>
> 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?

There are various timeout settings that can be configured:
https://www.postgresql.org/docs/9.6/static/runtime-config-client.html
idle_in_transation_session_timeout is probably the one you want to
enable.

It's likely that your application developers will start to complain about
database "errors" once you enable that, as connections will get killed and
cause errors on the application. You'll need to work to educate your
developers on how to fix their application so the situation stops happening.

--
PT <wmoran@potentialtech.com>