Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0052405
TypeCategorySeverityReproducibilityDate SubmittedLast Update
backport[Openbravo ERP] A. Platformmajorhave not tried2023-05-10 10:522023-05-10 13:25
ReportershuehnerView Statuspublic 
Assigned Toshuehner 
PriorityurgentResolutionfixedFixed in VersionPR21Q3.8
StatusclosedFix in branchFixed in SCM revision
ProjectionnoneETAnoneTarget VersionPR21Q3
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned To
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0052405: install.source fails with "DROP DATABASE cannot be executed within a pipeline" with postgres latest minor releases

DescriptionLatest minor releases for PostgreSQL for major versions 11,12,13,14,15 include a change causing install.source to always fail.

Specific PG versions including this change: 15.2, 14.7, 13.10, 12.14, and 11.19

Error message shown is:
<workspace>/src-db/database/build-create.xml:104: org.postgresql.util.PSQLException: ERROR: DROP DATABASE cannot be executed within a pipeline

Cause:
In issue 45385 we added code to workaround an incompatibility of Amazon RDS (compared to standard PostgreSQL).
Specifically adding an (optional) alter database owner change, just before the drop database.

Postgres change https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=f48aa5df4e030ab75bdc2ca5fc480c4a830cf5f3 [^]
now rejects some commands when being run in an already open transaction (and instead require an explicit commit)

This bug-report from another project is hit by the same change:
https://www.postgresql.org/message-id/4139340.1676050723%40sss.pgh.pa.us [^]
discussion in there shows that is an intended behavior change (as that was just unsupported before, just not detected).

So looks like we need to workaround this on our side.

Note:
This issue is triggered even when database does not exist (so nothing to drop).
Just having the alter+drop in 1 transaction to be executed is enough to trigger the issue.
Steps To ReproduceBasline: postgresql 11 or higher in latest minor version (at least the ones specified above)
ant install.source

Observe it failing with error message specified above.
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]
blocks defect 0051718 closedshuehner install.source fails with "DROP DATABASE cannot be executed within a pipeline" with postgres latest minor releases 

-  Notes
(0149550)
shuehner (administrator)
2023-05-10 10:54

 Scheduled extra backport for 21Q3: Used in CI in try-upgrade job for install.source of "old version".
21Q3 is used there with PostgreSQL version=14 (as support for it was only added in 21Q3)
(0149551)
hgbot (developer)
2023-05-10 11:16

 Merge Request created: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/878 [^]
(0149559)
hgbot (developer)
2023-05-10 13:25

 Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/product/openbravo [^]
Changeset: 804c1bace6bf3ea9bb2c8be53c873613c84008fc
Author: Stefan Huehner <stefan.huehner@openbravo.com>
Date: 10-05-2023 11:07:37
URL: https://gitlab.com/openbravo/product/openbravo/-/commit/804c1bace6bf3ea9bb2c8be53c873613c84008fc [^]

Fixes ISSUE-52405: 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.

One case where this affect 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

---
M src-db/database/build-create.xml
---
(0149560)
hgbot (developer)
2023-05-10 13:25

 Merge request merged: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/878 [^]

- Issue History
Date Modified Username Field Change
2023-05-10 10:52 shuehner New Issue
2023-05-10 10:52 shuehner Assigned To => shuehner
2023-05-10 10:52 shuehner Modules => Core
2023-05-10 10:52 shuehner Triggers an Emergency Pack => No
2023-05-10 10:52 shuehner Issue generated from 0051718
2023-05-10 10:52 shuehner Relationship added blocks 0051718
2023-05-10 10:54 shuehner Note Added: 0149550
2023-05-10 10:54 shuehner Type defect => backport
2023-05-10 11:16 hgbot Note Added: 0149551
2023-05-10 13:25 hgbot Resolution open => fixed
2023-05-10 13:25 hgbot Status new => closed
2023-05-10 13:25 hgbot Fixed in Version => PR21Q3.8
2023-05-10 13:25 hgbot Note Added: 0149559
2023-05-10 13:25 hgbot Note Added: 0149560

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker