Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0051718Openbravo ERPA. Platformpublic2023-02-28 12:262023-05-23 12:59
Reportershuehner 
Assigned Toshuehner 
PriorityimmediateSeveritycriticalReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionPR23Q2 
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
Summary0051718: 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
depends on backport 0051920PR23Q1.1 closed shuehner install.source fails with "DROP DATABASE cannot be executed within a pipeline" with postgres latest minor releases 
depends on backport 0051921PR22Q4.3 closed shuehner install.source fails with "DROP DATABASE cannot be executed within a pipeline" with postgres latest minor releases 
depends on backport 0052405PR21Q3 closed shuehner install.source fails with "DROP DATABASE cannot be executed within a pipeline" with postgres latest minor releases 
depends on backport 0052441PR20Q4 closed jarmendariz install.source fails with "DROP DATABASE cannot be executed within a pipeline" with postgres latest minor releases 
caused by design defect 0045385PR21Q1 closed shuehner Cannot run and create.database twice against AWS RDS database: ERROR: must be owner of database openbravo 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2023-02-28 12:26shuehnerNew Issue
2023-02-28 12:26shuehnerAssigned To => shuehner
2023-02-28 12:26shuehnerModules => Core
2023-02-28 12:26shuehnerTriggers an Emergency Pack => No
2023-02-28 12:27shuehnerRelationship addedcaused by 0045385
2023-03-02 15:24hgbotNote Added: 0147241
2023-03-16 09:46shuehnerPriorityurgent => immediate
2023-03-16 09:46shuehnerSeveritymajor => critical
2023-03-20 12:18shuehnerStatusnew => scheduled
2023-03-21 11:52hgbotNote Added: 0147748
2023-03-21 11:52hgbotResolutionopen => fixed
2023-03-21 11:52hgbotStatusscheduled => closed
2023-03-21 11:52hgbotFixed in Version => PR23Q2
2023-03-21 11:52hgbotNote Added: 0147749
2023-05-10 10:52shuehnerIssue cloned0052405
2023-05-10 10:52shuehnerRelationship addeddepends on 0052405
2023-05-12 20:52axelmercadoRelationship addeddepends on 0052441
2023-05-23 12:59eugeniIssue Monitored: eugeni

Notes
(0147241)
hgbot   
2023-03-02 15:24   
Merge Request created: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/832 [^]
(0147748)
hgbot   
2023-03-21 11:52   
Merge request merged: https://gitlab.com/openbravo/product/openbravo/-/merge_requests/832 [^]
(0147749)
hgbot   
2023-03-21 11:52   
Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/product/openbravo [^]
Changeset: a78c697368b6a3ea2556ea7bdd3d6a0306730bfc
Author: Stefan Huehner <stefan.huehner@openbravo.com>
Date: 20-03-2023 11:31:52
URL: https://gitlab.com/openbravo/product/openbravo/-/commit/a78c697368b6a3ea2556ea7bdd3d6a0306730bfc [^]

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.

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
---