[Bug]: postgresql pgbouncer prepared statement , encode plan

[todeb]

⚠️ This issue respects the following points: ⚠️

• This is a bug, not a question or a configuration issue.
• This issue is not already reported on Github (I've searched it).

Bug description

running sftpgo with postgresql and pgbouncer:

When sftpgo is killed, prepared statement seems not be cleaned and error is triggered:

SFTPGO_DATA_PROVIDER__CONNECTION_STRING="postgresql://sftpgo:sftpgo@mydbserver/sftpgo?sslmode=require"

{"level":"error","time":"2024-01-12T16:03:32.162","sender":"service","message":"error initializing data provider: ERROR: prepared statement \"stmtcache_1\" already exists (SQLSTATE 42P05)"}

when changing to:

SFTPGO_DATA_PROVIDER__CONNECTION_STRING="postgresql://sftpgo:sftpgo@mydbserver/sftpgo?sslmode=require&default_query_exec_mode=simple_protocol"

{"level":"error","time":"2024-01-12T16:01:17.447","sender":"service","message":"error initializing data provider: unable to encode 1 into text format for unknown type (OID 0): cannot find encode plan"}

Steps to reproduce

1. setup posthres with pgboucer
2. setup sftpgo to use pgbouncer
3. kill sftpgo

Expected behavior

should work with pgbouncer by default or by setting some parameters in connection string

SFTPGo version

drakkan/sftpgo:v2.5.6-alpine

Data provider

name=sftpgo ; driver=postgresql

Installation method

Community Docker image

Configuration

config

Relevant log output

No response

What are you using SFTPGo for?

Medium business

Additional info

No response

[drakkan]

This is probably a problem in the pgx library. Please write and share a standalone reproducer using pgx in database/sql mode and confirm that it works instead. Thank you

[todeb]

I used hashicorp vault with pgx with simple protocol and was ok. Which pgx version should I try? Any specific recommendation what reproducer should contain? Or should I just connect, do some insert and select?

[drakkan]

see this discussion for some more details.

Simple protocol is not supported in SFTPGo. Please test the other query exec modes and re-open if they don't work (I haven't done any tests myself).

I'd like to see a benchmark to understand the performance differences with and without pgbouncer in a real-world use case. Thank you