(Postgres) pgbouncer in transaction mode

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
bursch
Posts: 16
Joined: Tue 25 Sep 2018 07:45

(Postgres) pgbouncer in transaction mode

Post by bursch » Fri 04 Sep 2020 09:40

Hi,

we want to use pgbouncer to limit the number of concurrent connections to the database. Postgres documentation recommends no more than 800 simultanious connections. For optimial sharing of the connections we would like to use transaction mode in pgbouncer.

Documentation is here:
https://www.pgbouncer.org/config.html

With transaction mode we get errors because named prepared statements are not possible in this mode. This is documented and quite sensible:
  • using autocommit pgbouncer could switch connections between preparing and executing a statement.
Unfortunately Unidac uses only named prepared statements when executing SQL-Statements with parameters. This is corresponding to functions in libpq.dll named PQprepare and PQexecPrepared which are documented here:
https://www.postgresql.org/docs/10/libpq-exec.html

We made tests with unnamed prepared statements without Unidac. These are usable with pgbouncer and transaction mode. This is corresponding to libpq function PQexecParams.

You can see the difference in postgres log: if statement logging is enabled in Postgresql database then we have prepare and execute with named statement here:

Code: Select all

    2020-09-03 16:34:15.569 CEST 5f50fee1.948 22 LOG:  Ausführen PRSTMTST557682082/PORTALST557682082: Update AL_TEST set CharText = $1 where xkey = $2
    2020-09-03 16:34:15.569 CEST 5f50fee1.948 23 DETAIL:  Parameter: $1 = 'ABCDefgh', $2 = '3'
    2020-09-03 16:34:15.569 CEST 5f50fee1.948 24 LOG:  Dauer: 0.159 ms
and unnamed statements using PQexecParams here:

Code: Select all

    2020-09-03 16:32:36.293 CEST 5f50fe81.ad4 11 LOG:  Ausführen <unnamed>: Update AL_TEST set CharText = $1 where xkey = $2
    2020-09-03 16:32:36.293 CEST 5f50fe81.ad4 12 DETAIL:  Parameter: $1 = '\x4142434465666768', $2 = '3'
    2020-09-03 16:32:36.293 CEST 5f50fe81.ad4 13 LOG:  Dauer: 0.562 ms
Question or Request:
Is it possible to use unnamed prepared statements with Unidac like PQexecParams so that pgbouncer can be used in transaction mode?

Best regards,
Manuel

MaximG
Devart Team
Posts: 1617
Joined: Mon 06 Jul 2015 11:34

Re: (Postgres) pgbouncer in transaction mode

Post by MaximG » Wed 09 Sep 2020 09:42

Please try to use protocol version 2 when working with PgBouncer:

Code: Select all

UniConnection.SpecificOptions.Values ['ProtocolVersion']: = 'pv20';

bursch
Posts: 16
Joined: Tue 25 Sep 2018 07:45

Re: (Postgres) pgbouncer in transaction mode

Post by bursch » Wed 09 Sep 2020 13:38

With protocol version 2 other problems arise, e.g. using ParamByName (..). AsBytes no longer works.

Maybe this option could help. Are there any drawbacks with this (except Performance)?

Code: Select all

SpecificOptions.Values ['UnpreparedExecute']: = 'True';
Edit:
This option works only for TUniQuery. Some catalog queries in the table component using prepared statements.

bursch
Posts: 16
Joined: Tue 25 Sep 2018 07:45

Re: (Postgres) pgbouncer in transaction mode

Post by bursch » Mon 05 Oct 2020 10:39

Hi,

is there something new about this issue?

Greetings,
Manuel

oleg0k
Devart Team
Posts: 190
Joined: Wed 11 Mar 2020 08:28

Re: (Postgres) pgbouncer in transaction mode

Post by oleg0k » Tue 23 Feb 2021 08:41

Hello,
We're still investigating the issue. We'll keep you updated on the progress.

wbr, Oleg
Devart Team

Post Reply