PostgreSQL DevService does not enable max_prepared_transactions for XA operations

[gastaldi]

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]

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

[mmusgrov]

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

[zhfeng]

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]

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.

[gsmet]

I think it makes sense to have the Dev Service as capable as possible.

I prepared: #45307