Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0043527Openbravo ERPA. Platformpublic2020-03-19 21:072020-04-16 11:26
Reportermtaal 
Assigned Toalostale 
PrioritynormalSeveritymajorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionpiFixed in VersionPR20Q3 
Merge Request Status
Review Assigned Tocaristu
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0043527: Dbsourcemanager MaterializedView support: if MV depends on other MV order of execution can be wrong
DescriptionI have a MV which definition has a select statement on another MV (see [1]). Postgresql requires that the referenced MV already exists and will throw an error [2].

[1]
 SELECT r_1.obrtmd_org_id,
            r_1.year,
            r_1.month,
            r_1.day,
            r_1.dateordered,
            sum(r_1.ticket_cnt) AS ticket_cnt,
            sum(r_1.line_gross_amount) AS line_gross_amount,
            sum(r_1.qtyordered) AS qty
           FROM obrts_order_stats_pr_dt_mv r_1

[2]
[createreportingdatabase] 4965 [main] WARN org.apache.ddlutils.platform.PGStandardBatchEvaluator - SQL Command failed with: ERROR: relation "obrts_order_stats_pr_dt_mv" does not exist
[createreportingdatabase] Position: 1194
Steps To ReproduceCreate a MV which depends another MV and then dbsourcemanager
Proposed SolutionUse module dependencies to find the order of MV creation. This is however not always sufficient. Other idea (given by AL) to ignore exceptions and just repeat failed MV creation in a loop until all MV are created or no remaining MV succeeds.
Additional Information
TagsNo tags attached.
Relationships
related to feature request 00420423.0PR20Q1 closed AugustoMauch Add support of materialized views 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2020-03-19 21:07mtaalNew Issue
2020-03-19 21:07mtaalAssigned To => alostale
2020-03-19 21:07mtaalModules => Core
2020-03-19 21:07mtaalTriggers an Emergency Pack => No
2020-03-19 21:07mtaalIssue Monitored: mtaal
2020-03-30 08:38alostaleNote Added: 0118861
2020-04-09 10:25hgbotCheckin
2020-04-09 10:25hgbotNote Added: 0119112
2020-04-09 10:25hgbotStatusnew => resolved
2020-04-09 10:25hgbotResolutionopen => fixed
2020-04-09 10:25hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/5e3802cd030a0a27b3d2bfb6c050bcc8bf375bea [^]
2020-04-10 10:12alostaleReview Assigned To => caristu
2020-04-16 11:26caristuNote Added: 0119214
2020-04-16 11:26caristuStatusresolved => closed
2020-04-16 11:26caristuFixed in Version => 3.0PR20Q3
2021-09-21 14:35alostaleRelationship addedrelated to 0042042

Notes
(0118861)
alostale   
2020-03-30 08:38   
MR: https://gitlab.com/openbravo/product/dbsm/-/merge_requests/8 [^]
(0119112)
hgbot   
2020-04-09 10:25   
Repository: erp/devel/pi
Changeset: 5e3802cd030a0a27b3d2bfb6c050bcc8bf375bea
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Thu Apr 09 10:24:45 2020 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/5e3802cd030a0a27b3d2bfb6c050bcc8bf375bea [^]

fixed BUG-43527: creation of materialized view depedending on another MV fails

Creation of materialized views depending on another materialized view
failed in case the dependency was not previously created.

Treating materialized views in the same way regular views are treated
adding FORCE = TRUE option so that it retries failed executions.

---
M src-db/database/lib/dbsourcemanager.jar
---
(0119214)
caristu   
2020-04-16 11:26   
Reviewed + tested OK