Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0052405Openbravo ERPA. Platformpublic2023-05-10 10:522023-05-10 13:25
Reportershuehner 
Assigned Toshuehner 
PriorityurgentSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionPR21Q3Fixed in VersionPR21Q3.8 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0052405: 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.
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
blocks defect 0051718 closed shuehner install.source fails with "DROP DATABASE cannot be executed within a pipeline" with postgres latest minor releases 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2023-05-10 10:52shuehnerNew Issue
2023-05-10 10:52shuehnerAssigned To => shuehner
2023-05-10 10:52shuehnerModules => Core
2023-05-10 10:52shuehnerTriggers an Emergency Pack => No
2023-05-10 10:52shuehnerIssue generated from0051718
2023-05-10 10:52shuehnerRelationship addedblocks 0051718
2023-05-10 10:54shuehnerNote Added: 0149550
2023-05-10 10:54shuehnerTypedefect => backport
2023-05-10 11:16hgbotNote Added: 0149551
2023-05-10 13:25hgbotResolutionopen => fixed
2023-05-10 13:25hgbotStatusnew => closed
2023-05-10 13:25hgbotFixed in Version => PR21Q3.8
2023-05-10 13:25hgbotNote Added: 0149559
2023-05-10 13:25hgbotNote Added: 0149560

Notes
(0149550)
shuehner   
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   
2023-05-10 11:16   
Merge Request created: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/878 [^]
(0149559)
hgbot   
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   
2023-05-10 13:25   
Merge request merged: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/878 [^]