I am experiencing an exhaustion of postgres connections after/during
harvesting multiple files (sequentially, not simultaneously) with calls via
the GeoServer REST API. The culprit seems to be a lot of select now()
queries, on connections which seem to never close and which quickly lead to
"org.postgresql.util.PSQLException: FATAL: remaining connection slots are
reserved for non-replication superuser connections" errors.
My pg_stat_activity looks like this before harvesting files (2 total rows):
postgres=# select datid, datname, pid, client_addr, client_port, state,
query from pg_stat_activity;
datid | datname | pid | client_addr | client_port | state |
query
-------+----------+-------+-------------+-------------+--------+-------------------------------------------------------------------------------------------
19062 | basegis | 24675 | 127.0.0.1 | 39598 | idle | select
now()
12035 | postgres | 24729 | | -1 | active | select
datid, datname, pid, client_addr, client_port, state, query from
pg_stat_activity;
(2 rows)
It looks like this after harvesting files (98 total rows -- connections
exhausted):
datid | datname | pid | client_addr | client_port | state |
query
-------+------------+-------+-------------+-------------+--------+-------------------------------------------------------------------------------------------
19062 | basegis | 24675 | 127.0.0.1 | 39598 | idle | select
now()
17688 | stormprint | 25669 | 127.0.0.1 | 40072 | idle | select
now()
17688 | stormprint | 24869 | 127.0.0.1 | 39602 | idle | COMMIT
17688 | stormprint | 25671 | 127.0.0.1 | 40074 | idle | select
now()
17688 | stormprint | 24872 | 127.0.0.1 | 39605 | idle | COMMIT
17688 | stormprint | 25675 | 127.0.0.1 | 40076 | idle | select
now()
17688 | stormprint | 25677 | 127.0.0.1 | 40078 | idle | select
now()
17688 | stormprint | 25681 | 127.0.0.1 | 40080 | idle | select
now()
17688 | stormprint | 25683 | 127.0.0.1 | 40082 | idle | select
now()
17688 | stormprint | 25687 | 127.0.0.1 | 40084 | idle | select
now()
... [multiple similar rows omitted] ...
17688 | stormprint | 25930 | 127.0.0.1 | 40251 | idle | select
now()
17688 | stormprint | 25934 | 127.0.0.1 | 40253 | idle | select
now()
17688 | stormprint | 25936 | 127.0.0.1 | 40255 | idle | select
now()
17688 | stormprint | 25940 | 127.0.0.1 | 40257 | idle | select
now()
12035 | postgres | 26035 | | -1 | active | select
datid, datname, pid, client_addr, client_port, state, query from
pg_stat_activity;
(98 rows)
I see "select now()" being used as a validationQuery in lots of places in
the source. It appears, though, that database connections are not being
closed somewhere after harvesting via REST calls.
Thanks,
Mike Grogan
------------------------------------------------------------------------------
BPM Camp - Free Virtual Workshop May 6th at 10am PDT/1PM EDT
Develop your own process in accordance with the BPMN 2 standard
Learn Process modeling best practices with Bonita BPM through live exercises
http://www.bonitasoft.com/be-part-of-it/events/bpm-camp-virtual- event?utm_
source=Sourceforge_BPM_Camp_5_6_15&utm_medium=email&utm_campaign=VA_SF
_______________________________________________
Geoserver-users mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/geoserver-users
