Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0051920Openbravo ERPA. Platformpublic2023-02-28 12:262023-03-21 17:29
Reportershuehner 
Assigned Toshuehner 
PriorityimmediateSeveritycriticalReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionPR23Q1.1Fixed in VersionPR23Q1.1 
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
Summary0051920: 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-03-20 12:18shuehnerTypedefect => backport
2023-03-20 12:18shuehnerTarget Version => PR23Q1.1
2023-03-21 11:58hgbotNote Added: 0147750
2023-03-21 17:29hgbotResolutionopen => fixed
2023-03-21 17:29hgbotStatusscheduled => closed
2023-03-21 17:29hgbotNote Added: 0147783
2023-03-21 17:29hgbotFixed in Version => PR23Q1.1
2023-03-21 17:29hgbotNote Added: 0147784

Notes
(0147750)
hgbot   
2023-03-21 11:58   
Merge Request created: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/841 [^]
(0147783)
hgbot   
2023-03-21 17:29   
Merge request merged: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/841 [^]
(0147784)
hgbot   
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
---