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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0051920
TypeCategorySeverityReproducibilityDate SubmittedLast Update
backport[Openbravo ERP] A. Platformcriticalhave not tried2023-02-28 12:262023-03-21 17:29
ReportershuehnerView Statuspublic 
Assigned Toshuehner 
PriorityimmediateResolutionfixedFixed in VersionPR23Q1.1
StatusclosedFix in branchFixed in SCM revision
ProjectionnoneETAnoneTarget VersionPR23Q1.1
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

0051920: 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
(0147750)
hgbot (developer)
2023-03-21 11:58

 Merge Request created: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/841 [^]
(0147783)
hgbot (developer)
2023-03-21 17:29

 Merge request merged: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/841 [^]
(0147784)
hgbot (developer)
2023-03-21 17:29

 Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/product/openbravo [^]
Changeset: b46665ef152aed561fd6f15fcf9a84949074607b
Author: Stefan Huehner <stefan.huehner@openbravo.com>
Date: 21-03-2023 11:56:35
URL: https://gitlab.com/openbravo/product/openbravo/-/commit/b46665ef152aed561fd6f15fcf9a84949074607b [^]

Fixes ISSUE-51920: 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
---

- Issue History
Date Modified Username Field Change
2023-03-20 12:18 shuehner Type defect => backport
2023-03-20 12:18 shuehner Target Version => PR23Q1.1
2023-03-21 11:58 hgbot Note Added: 0147750
2023-03-21 17:29 hgbot Resolution open => fixed
2023-03-21 17:29 hgbot Status scheduled => closed
2023-03-21 17:29 hgbot Note Added: 0147783
2023-03-21 17:29 hgbot Fixed in Version => PR23Q1.1
2023-03-21 17:29 hgbot Note Added: 0147784

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker