(Postgres) pgbouncer in transaction mode

[bursch]

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]

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

Code: Select all

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

[bursch]

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]

Hi,

is there something new about this issue?

Greetings,
Manuel

[oleg0k]

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

wbr, Oleg
Devart Team