Fixes ISSUE-51718: Fix create.database with latest PG minor releases
PostgreSQL latest minor versions (15.2, 14.7, 13.10, 12.14, 11.19) include a change to prohibit certain SQL to run in a pipeline without an explicit commit done just before.
Note: 10.x is not affected as it just went End Of Life before this change.
PG Release notes do not explain this very clearly as only talk about specific case:
Avoid an immediate commit after ANALYZE when using query pipelining.
This PostgreSQL commit goes into more details:
- https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=20432f873
- Last paragraph in that commit talks about safeguarding more statements via function PreventInTransactionBlock (in PostgreSQL source code).
Discussion by another affected project explain the connection from 'in a pipeline to jdbc':
- https://github.com/liquibase/liquibase/issues/3806#issuecomment-1466374267
- Statement.executeBatch() => pipeline mode
- cannot run affected affected in JDBC batch
They asked PostgreSQL project about their case:
- https://www.postgresql.org/message-id/4139340.1676050723%40sss.pgh.pa.us
- Reply is essentially:
- Intentional change
- You need to fix your code
- That is unsupported and was just not properly detected before
Only known case affecting us is ant create.database task. Specifically the DROP database run before (re-)creating the database:
ERROR: DROP DATABASE cannot be executed within a pipeline
Earlier issue 45385 had to add an ALTER DATABASE just before the DROP DATABASE to work about an incompatibility of Amazon RDS (managed postgres).
As this intentionally ran ALTER and DROP database in a single transaction it now triggers that new restriction.
Given that we need to have both that RDS workaround + also work with the new PostgreSQL restriction run those two statements in two separate transactions.
That allows side-effect:
- Owner of database changes to superuser 'postgres'
- Only in case the "drop database" part fails
Testing done (all okay):
- pg10 and pg12 (latest minor versions) 2*ant create.database
- pg12 Local developer sharing db-user across various databases
- pg12 check 'drop database' fails
- have open connection to the database being droppped/recreated
- fails as expected, side-effect (owner change) happens, closing connection and re-doing, works again
- RDS12+RDS14
- 2*ant create.database
- also verified that both still require the "alter table" workaround today
- Note: RDS itself does not yet have this behavior change, so only doing regression testing here
- try (jobs_to_run=SIMPLE as fix only affecting create.db)
- merged run
- https://cicd.openbravo.com/view/modules/job/mod-merged-new-stack/641
- Running against cicd with pg11 as that has latest pg minor version which requires the fix
TODO:
- dbsm testsuite (to see if more cases on top of this one are found)
- Not a review/merge blocker