[OKAPI-583] Possible leak of SQL connections/sessions Created: 21/Mar/18  Updated: 03/Jan/20  Resolved: 11/Apr/18

Status: Closed
Project: Okapi
Components: None
Affects versions: None
Fix versions: None

Type: Bug Priority: P3
Reporter: Adam Dickmeiss Assignee: Adam Dickmeiss
Resolution: Duplicate Votes: 0
Labels: back-end, ci, core, sprint36
Remaining Estimate: Not Specified
Time Spent: 4 hours
Original estimate: Not Specified

Attachments: Text File error_log.txt    
Issue links:
Duplicate
duplicates RMB-154 Investigate postgresql connection pool Closed
Relates
relates to OKAPI-561 Docker pull not working (anymore) Closed
Sprint:

 Description   

The issue was seen with install calls (new tenants each time). RMB-based modules could play role as well .. This is because tenant init calls are used during install as well.



 Comments   
Comment by Adam Dickmeiss [ 21/Mar/18 ]

error_log.txt as reported by Chason Luis

Comment by Adam Dickmeiss [ 05/Apr/18 ]

Chason Luis wrote:
I found a problem when I install modules for tenants. I did this operation with the okapi's proxy service:`/_/proxy/tenants/

{tenant_id}

/install`. I wanted to enable `mod-users/permission/login/users-bl` to different tenants. At first , the `install` service runs well . But after doing this for several tenants. The connections of postgresql seems not enough. And the log reports something like: `3:39:29 ERROR PostgreSQLConnection Error with message -> ErrorMessage(fields=Map(Line -> 779, File -> postinit.c, SQLSTATE -> 53300, Routine -> InitPostgres, V -> FATAL, Message -> 已保留的连接位置为执行非复制请求的超级用户预留, Severity -> 致命错误))`
The sqlState is 53300

Comment by shale99 [ 09/Apr/18 ]

i am looking into rmb to see if i see this

Comment by shale99 [ 09/Apr/18 ]

i activated 12 tenants locally

running

select max_conn,used,res_for_super,max_conn-used-res_for_super res_for_normal 
from 
  (select count(*) used from pg_stat_activity) A,
  (select setting::int res_for_super from pg_settings where name=$$superuser_reserved_connections$$) B,
  (select setting::int max_conn from pg_settings where name=$$max_connections$$) C

yielded:

max_connections: 100
used: 4
reserved for super user: 3
reserved for normal: 93

looking into the used (4) - 3 o them belonged to pgAdmin (postgres sql client) and only one to rmb. so i dont yet believe this is comming from an rmb storage module but i will continue looking. biz modules should not have any db access

Comment by shale99 [ 09/Apr/18 ]

to check the connections:

  SELECT
    pid,
    datname,
    usename,
    application_name,
    client_addr,
    to_char(backend_start, 'YYYY-MM-DD HH24:MI:SS TZ') AS backend_start,
    state,
    wait_event_type || ': ' || wait_event AS wait_event,
    pg_blocking_pids(pid) AS blocking_pids,
    query,
    to_char(state_change, 'YYYY-MM-DD HH24:MI:SS TZ') AS state_change,
    to_char(query_start, 'YYYY-MM-DD HH24:MI:SS TZ') AS query_start
FROM
    pg_stat_activity
ORDER BY pid

you should see only one connection that is active usually, while a query is running it will be in active state, when the query completes it will be in idle state although the connection will remain open.

Generated at Thu Feb 08 23:11:36 UTC 2024 using Jira 1001.0.0-SNAPSHOT#100246-sha1:7a5c50119eb0633d306e14180817ddef5e80c75d.