postgresql deadlock

[mfwindy]

• The issue is present in the latest release.
• I have searched the issues of this repository and believe that this is not a duplicate.

What happened?

when user subscribe the query devices by DevEUI, the postgresql make the deadlock. for this reason , the chirpstack_as crashed!

What did you expect?

postgresql can be unlock in the case of high concurrency.

Steps to reproduce this issue

Steps:

1. get devices by devEUI
2. select * from device where dev_eui = $1 for update

Could you share your log output?

Your Environment

Component	Version
Application Server	v?.?.?
Network Server
Gateway Bridge
Chirpstack API
Geolocation
Concentratord

[brocaar]

I'm not exactly sure what you mean with the steps to reproduce. If you are executing manually:

select * from device where dev_eui = $1 for update (where $1 is the dev_eui) then this will lock the row until a database commit or rollback.

[mfwindy]

I'm exactly sure what you mean with the steps to reproduce. If you are executing manually:

select * from device where dev_eui = $1 for update (where $1 is the dev_eui) then this will lock the row until a database commit or rollback.

yes, in the case of postgresql deadlocking, chirpstack-application-server will be crash , it will prompt "pq:sorry,already too many clients".so i want to know how to resolve this question? thank you !

[brocaar]

I'm open for fixes, but I would recommend looking into migrating to ChirpStack v4. The v4 architecture is a lot simpler as you do not have to deal with multiple databases. It is also the place where all the new development is happening :-)

[mfwindy]

I'm so appreciate for you reply.For some reason, i can't upgrade the version to Chirpstack V4, so i hope you can give good news about the question as soon as possible ,thanks so much！

[mfwindy]

The logs:
9月 25 10:38:05 linaro-alip chirpstack-network-server[595]: time="2023-09-25T10:38:05.2028042+08:00" level=info msg="gateway/mqtt: uplink frame received" gateway_id=01010122207c0032 uplink_id=eff7ebc2-4575-4c1b-8b67-d4f1c1d5f838
9月 25 10:38:05 linaro-alip chirpstack-network-server[595]: time="2023-09-25T10:38:05.206611035+08:00" level=info msg="gateway/mqtt: uplink frame received" gateway_id=01010122207c00a8 uplink_id=1dc83cec-29cb-4396-b75b-74a5a862f7e4
9月 25 10:38:05 linaro-alip chirpstack-network-server[595]: time="2023-09-25T10:38:05.423433852+08:00" level=info msg="uplink: frame(s) collected" ctx_id=5f4a62d9-c868-48c3-8125-35d750bc9863 mtype=JoinRequest uplink_ids="[eff7ebc2-4575-4c1b-8b67-d4f1c1d5f838 1dc83cec-29cb-4396-b75b-74a5a862f7e4]"
9月 25 10:38:05 linaro-alip chirpstack-network-server[595]: time="2023-09-25T10:38:05.456844868+08:00" level=info msg="lorawan/backend: finished backend api call" message_type=JoinReq protocol_version=1.0 receiver_id=0000000000000010 result_code=Other sender_id=000000 transaction_id=1091045643
9月 25 10:38:05 linaro-alip chirpstack-network-server[595]: time="2023-09-25T10:38:05.476215003+08:00" level=error msg="uplink/join: get as client for routing-profile id error" ctx_id=5f4a62d9-c868-48c3-8125-35d750bc9863 dev_eui=01010138200a0366 error="get routing-profile error: select error: pq: 已保留的连接位置为执行非复制请求的超级用户预留"
9月 25 10:38:05 linaro-alip chirpstack-network-server[595]: time="2023-09-25T10:38:05.477852712+08:00" level=error msg="uplink: processing uplink frame error" ctx_id=5f4a62d9-c868-48c3-8125-35d750bc9863 error="join-request to join-server error: response error, code: Other, description: get device-keys error: select error: pq: 已保留的连接位置为执行非复制请求的超级用户预留"
9月 25 10:38:11 linaro-alip chirpstack-network-server[595]: time="2023-09-25T10:38:11.764780698+08:00" level=info msg="gateway/mqtt: uplink frame received" gateway_id=01010122207c0032 uplink_id=48854381-7835-4bdd-b33d-b32d0c54269e
9月 25 10:38:11 linaro-alip chirpstack-network-server[595]: time="2023-09-25T10:38:11.773350786+08:00" level=info msg="gateway/mqtt: uplink frame received" gateway_id=01010122207c00a8 uplink_id=faee04b0-5def-4777-b7bd-36b0e5258da5
9月 25 10:38:11 linaro-alip chirpstack-network-server[595]: time="2023-09-25T10:38:11.970713799+08:00" level=info msg="uplink: frame(s) collected" ctx_id=9b0d7ff2-26fd-4260-b1f3-c44a99dd847f mtype=JoinRequest uplink_ids="[faee04b0-5def-4777-b7bd-36b0e5258da5 48854381-7835-4bdd-b33d-b32d0c54269e]"
9月 25 10:38:12 linaro-alip chirpstack-network-server[595]: time="2023-09-25T10:38:11.997011395+08:00" level=info msg="lorawan/backend: finished backend api call" message_type=JoinReq protocol_version=1.0 receiver_id=0000000000000010 result_code=Other sender_id=000000 transaction_id=1933984220
9月 25 10:38:12 linaro-alip chirpstack-network-server[595]: time="2023-09-25T10:38:12.010131444+08:00" level=error msg="uplink/join: get as client for routing-profile id error" ctx_id=9b0d7ff2-26fd-4260-b1f3-c44a99dd847f dev_eui=01010138200a0360 error="get routing-profile error: select error: pq: 对不起, 已经有太多的客户"
9月 25 10:38:12 linaro-alip chirpstack-network-server[595]: time="2023-09-25T10:38:12.010937027+08:00" level=error msg="uplink: processing uplink frame error" ctx_id=9b0d7ff2-26fd-4260-b1f3-c44a99dd847f error="join-request to join-server error: response error, code: Other, description: get device-keys error: select error: pq: 已保留的连接位置为执行非复制请求的超级用户预留"
9月 25 10:38:19 linaro-alip chirpstack-network-server[595]: time="2023-09-25T10:38:19.801120333+08:00" level=info msg="gateway/mqtt: uplink frame received" gateway_id=01010122207c0032 uplink_id=5f6988bd-70cf-4c1e-98bb-50c55e7b772e
9月 25 10:38:19 linaro-alip chirpstack-network-server[595]: time="2023-09-25T10:38:19.801710958+08:00" level=info msg="gateway/mqtt: uplink frame received" gateway_id=01010122207c00a8 uplink_id=08ee53f3-7be3-4de5-a011-340a9225788f
9月 25 10:38:20 linaro-alip chirpstack-network-server[595]: time="2023-09-25T10:38:20.00817164+08:00" level=info msg="uplink: frame(s) collected" ctx_id=a867da1b-06aa-4f03-84ca-899c5782d804 mtype=JoinRequest uplink_ids="[08ee53f3-7be3-4de5-a011-340a9225788f 5f6988bd-70cf-4c1e-98bb-50c55e7b772e]"
9月 25 10:38:20 linaro-alip chirpstack-network-server[595]: time="2023-09-25T10:38:20.064967375+08:00" level=info msg="lorawan/backend: finished backend api call" message_type=JoinReq protocol_version=1.0 receiver_id=0000000000000010 result_code=Other sender_id=000000 transaction_id=1635525553
9月 25 10:38:20 linaro-alip chirpstack-network-server[595]: time="2023-09-25T10:38:20.10341256+08:00" level=error msg="uplink/join: get as client for routing-profile id error" ctx_id=a867da1b-06aa-4f03-84ca-899c5782d804 dev_eui=01010138200a0360 error="get routing-profile error: select error: pq: 已保留的连接位置为执行非复制请求的超级用户预留"
9月 25 10:38:20 linaro-alip chirpstack-network-server[595]: time="2023-09-25T10:38:20.105451311+08:00" level=error msg="uplink: processing uplink frame error" ctx_id=a867da1b-06aa-4f03-84ca-899c5782d804 error="join-request to join-server error: response error, code: Other, description: get device-keys error: select error: pq: 已保留的连接位置为执行非复制请求的超级用户预留"

[daniel-rus-innogando]

We are facing the same problem, any update on this?