Open
Description
I have automated tests every time I save any file (with mocha --watch), and this means knex store is initialized A LOT throughout my day.
I've noticed that after a few writes, any attempted knex connection to postgres results in this:
Unhandled rejection error: sorry, too many clients already
at Connection.parseE (/app/node_modules/pg/lib/connection.js:606:11)
at Connection.parseMessage (/app/node_modules/pg/lib/connection.js:403:19)
at Socket.<anonymous> (/app/node_modules/pg/lib/connection.js:123:22)
at Socket.emit (events.js:210:5)
at addChunk (_stream_readable.js:308:12)
at readableAddChunk (_stream_readable.js:289:11)
at Socket.Readable.push (_stream_readable.js:223:10)
at TCP.onStreamRead (internal/stream_base_commons.js:182:23)
The cause seems to be, at least partially, that all the session expiry queries persist after the application is shut down. From psql:
somedbname=# select pid, query from pg_stat_activity;
pid | query
------+----------------------------------------------------------------------------
29 |
1389 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1410 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1388 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
624 | select pid, query from pg_stat_activity;
1337 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1412 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1419 |
1270 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1338 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1404 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1405 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1390 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1429 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1367 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1408 |
1430 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1234 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1368 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1413 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1241 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1414 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1415 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1416 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1335 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1417 |
1418 |
1421 |
1264 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1277 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
647 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1420 |
651 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1248 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1422 |
1393 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1301 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1423 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1395 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1396 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1397 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1321 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1424 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1425 |
1398 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1426 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1351 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1382 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1399 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1427 |
1327 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1401 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1403 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1407 |
1354 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
1431 | delete from "sessions" where expired < CAST($1 as timestamp with time zone)
Is there a workaround for this?
I don't know if this should be fixed on connect-session-knex side, or I should be doing something differently from my code.
While this gets sorted, does anyone know how to remove those queries programmatically, ideally efficiently? My best guess was select pid, query from pg_stat_activity where query like '%sessions%expired%' and pid != pg_backend_pid() and pg_cancel_backend(pid), but it seems pg_cancel_backend is ran before the pid != pg_backend_pid check, so the command never finishes and a lot of those cookie clearing queries persist.
My understanding of postgres is quite limited.
Metadata
Metadata
Assignees
Labels
No labels