PostgreSQL DevService does not enable `max_prepared_transactions` for XA operations

gastaldi commented 1 year ago

Describe the bug

Having XA enabled in a Quarkus app (with quarkus.datasource.jdbc.transactions=xa in the application.properties), and invoking two XA datasources in a @Transactional method (or 1 datasource and 1 Resource Adapter), the following error happens when the transaction is committed:

    Caused by: org.postgresql.util.PSQLException: ERROR: prepared transactions are disabled
  Hint: Set max_prepared_transactions to a nonzero value.
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:335)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:321)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:297)
        at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:270)
        at org.postgresql.xa.PGXAConnection.prepare(PGXAConnection.java:357)

Enabling the max_prepared_transactions to a non-zero value (as stated in the exception message) fixes it.

Expected behavior

No errors while committing the transaction

Actual behavior

The exception message is shown

How to Reproduce?

Create a Quarkus application with two XA datasources (one of them being a PostgreSQL DevService) and perform any operation in a @Transactional method. The error will be displayed when the transaction is committed.

Output of `uname -a` or `ver`

macOS m1

Output of `java -version`

JDK 17

Quarkus version or git rev

3.5.0

Build tool (ie. output of `mvnw --version` or `gradlew --version`)

Apache Maven 3.9.2

Additional information

Setting the max_prepared_transactions parameter to 100 fixes it, but it should preferably be enabled only if XA is enabled - https://stackoverflow.com/questions/45793501/recommended-way-to-configure-max-prepared-transactions-in-postgres-on-kubernetes

quarkus-bot[bot] commented 1 year ago

/cc @geoand (devservices,kubernetes), @iocanel (kubernetes), @stuartwdouglas (devservices)

mmusgrov commented 1 year ago

I've also see the advice to also set max_connections to the same value as max_prepared_transactions.

zhfeng commented 6 months ago

Well, I think we can add this line to the application.properties to enable XA on pregresSQL devservice

quarkus.datasource.devservices.command = --max_prepared_transactions=100

michieldondorp commented 6 months ago

I've also see the advice to also set max_connections to the same value as max_prepared_transactions.

The postgres doc also advices this: https://www.postgresql.org/docs/current/runtime-config-resource.html#GUC-MAX-PREPARED-TRANSACTIONS

If you are using prepared transactions, you will probably want max_prepared_transactions to be at least as large as max_connections, so that every session can have a prepared transaction pending.

quarkusio / quarkus