Openbravo Issue Tracking System - Openbravo ERP |
| View Issue Details |
|
| ID | Project | Category | View Status | Date Submitted | Last Update |
| 0030397 | Openbravo ERP | A. Platform | public | 2015-07-21 13:06 | 2015-09-01 15:37 |
|
| Reporter | alostale | |
| Assigned To | alostale | |
| Priority | urgent | Severity | major | Reproducibility | have not tried |
| Status | closed | Resolution | fixed | |
| Platform | | OS | 5 | OS Version | |
| Product Version | | |
| Target Version | 3.0PR15Q4 | Fixed in Version | 3.0PR15Q4 | |
| Merge Request Status | |
| Review Assigned To | marvintm |
| OBNetwork customer | No |
| Web browser | |
| Modules | Core |
| Support ticket | |
| Regression level | |
| Regression date | |
| Regression introduced in release | |
| Regression introduced by commit | |
| Triggers an Emergency Pack | No |
|
| Summary | 0030397: DB inconsistencies in PG9.5 (alpha1) |
| Description | Exporting database in PostgreSQL 9.5 results in formal changes.
See attached a diff with the list of all changes.
There are 2 types of changes:
1. Changes in check constraints
===============================
For example:
- <check name="M_INOUT_CURRENCY_CHECK"><![CDATA[((FREIGHTAMT = 0) OR (FREIGHTAMT IS NULL)) OR (FREIGHT_CURRENCY_ID IS NOT NULL)]]></check>
+ <check name="M_INOUT_CURRENCY_CHECK"><![CDATA[(FREIGHTAMT = 0) OR (FREIGHTAMT IS NULL) OR (FREIGHT_CURRENCY_ID IS NOT NULL)]]></check>
They occur for check constraints like: "expr1 OR expr2 OR expr3"
Previous to 9.5 they where internally stored as "(((expr1) OR (expr2)) OR (expr2))"
but in 9.5 they are "((expr1) OR (expr2) OR (expr3))"
This can be tested with:
create table test (a text, b text, c text);
ALTER TABLE test ADD CONSTRAINT test_chk CHECK (a is not null or b is not null or c is not null);
select version(), consrc, pg_get_constraintdef(oid, true) from pg_constraint where conname ='test_chk';
version | consrc | pg_get_constraintdef
----------+-----------------------------------------------------------+---------------------------------------------------------
8.4.22 | (((a IS NOT NULL) OR (b IS NOT NULL)) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL)
9.2.11 | (((a IS NOT NULL) OR (b IS NOT NULL)) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL)
9.3.7 | (((a IS NOT NULL) OR (b IS NOT NULL)) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL)
9.5alpha1 | ((a IS NOT NULL) OR (b IS NOT NULL) OR (c IS NOT NULL)) | CHECK (a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL)
2. Changes in views
===================
Some views are changed in two ways
2.1 Some numeric values enclosed between brackets
-------------------------------------------------
- <view name="M_RM_SHIPMENT_PICK_EDIT"><![CDATA[SELECT ... CASE WHEN sd.m_locator_id IS NULL THEN (-1) * ol.qtyordered ...]]></view>
+ <view name="M_RM_SHIPMENT_PICK_EDIT"><![CDATA[SELECT ... CASE WHEN sd.m_locator_id IS NULL THEN '-1' * ol.qtyordered ...]]></view>
Internally the query was in previous versions:
... WHEN sd.m_locator_id IS NULL THEN (-1)::numeric * ol.qtyordered ...
and in 9.5 is:
... WHEN sd.m_locator_id IS NULL THEN '-1'::integer::numeric * ol.qtyordered ...
2.2 Some ORDER BY fields enclosed between brackets
--------------------------------------------------
- <view name="FIN_DOUBTFUL_DEBT_V"><![CDATA[SELECT ... GROUP BY ... trunc(now()) - trunc(ps.duedate) ... ]]></view>
+ <view name="FIN_DOUBTFUL_DEBT_V"><![CDATA[SELECT ... GROUP BY ... (trunc(now()) - trunc(ps.duedate)) ... ]]></view>
It is also caused by how the view is internally translated and stored.
|
| Steps To Reproduce | -Install source PostgreSQL 9.5
-Set all modules (type='M') in development
-Export database
-> See there are formal changes |
| Proposed Solution | 1. Changes in check constraints
===============================
Instead of retrieving check constrains SQL from pg_constraint.consrc, use the pg_get_constraintdef function, which looks stable over the latest versions.
This will imply an initial massive change in many constrains (in most of the cases it will consist on removing unneeded brackets).
2. Changes in views
===================
Fix the parser to take into account this case. |
| Additional Information | |
| Tags | No tags attached. |
| Relationships | | related to | defect | 0028684 | | closed | alostale | Doing pg_dump+pg_restore + export.database leads to a few check constraint being exported wrongly | | related to | feature request | 0036911 | | closed | alostale | support PostgreSQL 10 |
|
| Attached Files |  formal-changes-pg95.diff (53,131) 2015-07-21 13:08
https://issues.openbravo.com/file_download.php?file_id=8294&type=bug
 issue-30397.export (24,458) 2015-07-22 09:34
https://issues.openbravo.com/file_download.php?file_id=8296&type=bug |
|
| Issue History |
| Date Modified | Username | Field | Change |
| 2015-07-21 13:06 | alostale | New Issue | |
| 2015-07-21 13:06 | alostale | Assigned To | => platform |
| 2015-07-21 13:06 | alostale | OBNetwork customer | => No |
| 2015-07-21 13:06 | alostale | Modules | => Core |
| 2015-07-21 13:06 | alostale | Triggers an Emergency Pack | => No |
| 2015-07-21 13:07 | alostale | Status | new => scheduled |
| 2015-07-21 13:08 | alostale | File Added: formal-changes-pg95.diff | |
| 2015-07-21 13:14 | alostale | Description Updated | bug_revision_view_page.php?rev_id=9065#r9065 |
| 2015-07-21 13:23 | alostale | Description Updated | bug_revision_view_page.php?rev_id=9066#r9066 |
| 2015-07-21 13:28 | alostale | Description Updated | bug_revision_view_page.php?rev_id=9067#r9067 |
| 2015-07-21 13:32 | alostale | Proposed Solution updated | |
| 2015-07-21 18:26 | shuehner | Issue Monitored: shuehner | |
| 2015-07-22 08:26 | alostale | Description Updated | bug_revision_view_page.php?rev_id=9068#r9068 |
| 2015-07-22 08:30 | alostale | Description Updated | bug_revision_view_page.php?rev_id=9069#r9069 |
| 2015-07-22 08:31 | alostale | Description Updated | bug_revision_view_page.php?rev_id=9070#r9070 |
| 2015-07-22 08:32 | alostale | Relationship added | related to 0028684 |
| 2015-07-22 09:04 | alostale | Note Added: 0079013 | |
| 2015-07-22 09:34 | alostale | File Added: issue-30397.export | |
| 2015-07-22 09:38 | alostale | Note Added: 0079017 | |
| 2015-07-22 09:38 | alostale | Note Edited: 0079017 | bug_revision_view_page.php?bugnote_id=0079017#r9072 |
| 2015-08-10 15:00 | alostale | Review Assigned To | => marvintm |
| 2015-08-10 15:31 | hgbot | Checkin | |
| 2015-08-10 15:31 | hgbot | Note Added: 0079285 | |
| 2015-08-10 15:31 | hgbot | Status | scheduled => resolved |
| 2015-08-10 15:31 | hgbot | Resolution | open => fixed |
| 2015-08-10 15:31 | hgbot | Fixed in SCM revision | => http://code.openbravo.com/erp/devel/pi/rev/12f4733ed9554cc0b525c62c8c8383839084af5e [^] |
| 2015-08-10 15:31 | hgbot | Checkin | |
| 2015-08-10 15:31 | hgbot | Note Added: 0079286 | |
| 2015-08-10 15:31 | hgbot | Checkin | |
| 2015-08-10 15:31 | hgbot | Note Added: 0079287 | |
| 2015-08-10 15:31 | hgbot | Checkin | |
| 2015-08-10 15:31 | hgbot | Note Added: 0079288 | |
| 2015-08-10 15:31 | hgbot | Checkin | |
| 2015-08-10 15:31 | hgbot | Note Added: 0079289 | |
| 2015-08-10 15:32 | hgbot | Checkin | |
| 2015-08-10 15:32 | hgbot | Note Added: 0079290 | |
| 2015-08-10 15:32 | hgbot | Checkin | |
| 2015-08-10 15:32 | hgbot | Note Added: 0079292 | |
| 2015-08-11 11:25 | alostale | Assigned To | platform => alostale |
| 2015-08-11 11:31 | hgbot | Checkin | |
| 2015-08-11 11:31 | hgbot | Note Added: 0079307 | |
| 2015-08-11 11:31 | hgbot | Checkin | |
| 2015-08-11 11:31 | hgbot | Note Added: 0079308 | |
| 2015-08-11 11:31 | hgbot | Checkin | |
| 2015-08-11 11:31 | hgbot | Note Added: 0079309 | |
| 2015-08-18 14:08 | hgbot | Checkin | |
| 2015-08-18 14:08 | hgbot | Note Added: 0079409 | |
| 2015-08-20 23:16 | hudsonbot | Checkin | |
| 2015-08-20 23:16 | hudsonbot | Note Added: 0079538 | |
| 2015-09-01 15:37 | marvintm | Status | resolved => closed |
| 2015-09-01 15:37 | marvintm | Fixed in Version | => 3.0PR15Q4 |
| 2017-09-21 12:40 | alostale | Relationship added | related to 0036911 |
|
Notes |
|
|
|
Tested:
* Try pg 9.5
* Try standard (pg 9.1 + ora)
* Issue 0028684:
* pg 9.5: create.database - pg_dump - pg_restore - export.database
* pg 9.3: create.database - pg_dump - pg_restore - export.database
* pg 9.3: create.database - pg_dump - pg 9.5: pg_restore - export.database |
|
|
|
|
Attached patch (issue-30397.export) for dbsm fixing all these formal changes.
Awaiting for PG9.5 to be released as stable before applying it, as new changes might come.
Together with this patch it will be required to:
* Export database with new dbsm to commit formal changes in check constraints
* Do the same for retail
* Accept as false positive API changes detected due to these formal changes in check constraints
|
|
|
|
(0079285)
|
|
hgbot
|
|
2015-08-10 15:31
|
|
Repository: erp/devel/pi
Changeset: 12f4733ed9554cc0b525c62c8c8383839084af5e
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Mon Aug 10 15:25:29 2015 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/12f4733ed9554cc0b525c62c8c8383839084af5e [^]
fixed bug 30397: DB inconsistencies in PG9.5
Fixes:
* Changes in check constraint expressions, these expressions are now read
from db using pg_get_constraintdef function
* Changes in views
---
M src-db/database/lib/dbsourcemanager.jar
---
|
|
|
|
(0079286)
|
|
hgbot
|
|
2015-08-10 15:31
|
|
Repository: erp/devel/dbsm-main
Changeset: a233ff96c0943640408d5398a17fe1100f7fa763
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Wed Jul 22 09:08:59 2015 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/a233ff96c0943640408d5398a17fe1100f7fa763 [^]
related to bug 30397: DB inconsistencies in PG9.5
Fixed changes in check constraints.
OR expressions were grouped differently in pg9.5 than in previous versions.
Fixed by getting check constraint definition with pg_get_constraintdef instead
of directly from pg_constraint.consrc. After this change, some of the modifications
performed in PostgreSqlCheckTranslation are no longer required.
---
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlCheckTranslation.java
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlModelLoader.java
---
|
|
|
|
(0079287)
|
|
hgbot
|
|
2015-08-10 15:31
|
|
Repository: erp/devel/dbsm-main
Changeset: 093c3398e1012b1e81e94830a8939860a1e24b78
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Wed Jul 22 09:12:56 2015 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/093c3398e1012b1e81e94830a8939860a1e24b78 [^]
related to bug 30397: DB inconsistencies in PG9.5
Fixed changes of numeric constants in views.
When the view contains a numeric constant, in previous versions the query was
internally translated to:
... WHEN sd.m_locator_id IS NULL THEN (-1)::numeric * ol.qtyordered ...
and in 9.5 is:
... WHEN sd.m_locator_id IS NULL THEN '-1'::integer::numeric * ol.qtyordered ...
This case has been taken into account in PostgreSQLStandarization to standarize
in both cases to the same SQL.
---
M src/org/apache/ddlutils/platform/postgresql/PostgreSQLStandarization.java
---
|
|
|
|
(0079288)
|
|
hgbot
|
|
2015-08-10 15:31
|
|
Repository: erp/devel/dbsm-main
Changeset: 093c3398e1012b1e81e94830a8939860a1e24b78
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Wed Jul 22 09:12:56 2015 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/093c3398e1012b1e81e94830a8939860a1e24b78 [^]
related to bug 30397: DB inconsistencies in PG9.5
Fixed changes of numeric constants in views.
When the view contains a numeric constant, in previous versions the query was
internally translated to:
... WHEN sd.m_locator_id IS NULL THEN (-1)::numeric * ol.qtyordered ...
and in 9.5 is:
... WHEN sd.m_locator_id IS NULL THEN '-1'::integer::numeric * ol.qtyordered ...
This case has been taken into account in PostgreSQLStandarization to standarize
in both cases to the same SQL.
---
M src/org/apache/ddlutils/platform/postgresql/PostgreSQLStandarization.java
---
|
|
|
|
(0079289)
|
|
hgbot
|
|
2015-08-10 15:31
|
|
Repository: erp/devel/dbsm-main
Changeset: 1132de7285415bd74eb0dffe88e518daadb302cf
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Wed Jul 22 09:15:58 2015 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/1132de7285415bd74eb0dffe88e518daadb302cf [^]
related to bug 30397: DB inconsistencies in PG9.5
Fixed brackets in views for some GROUP BY fields.
Starting from PostgreSQL 9.5, some fields in GROUP BY are enclosed between
brackets. These brackets are removed to keep the same formatting as in previous
versions.
Example of a modification before this changeset:
- <view name="FIN_DOUBTFUL_DEBT_V"><![CDATA[SELECT ... GROUP BY ... trunc(now()) - trunc(ps.duedate) ... ]]></view>
+ <view name="FIN_DOUBTFUL_DEBT_V"><![CDATA[SELECT ... GROUP BY ... (trunc(now()) - trunc(ps.duedate)) ... ]]></view>
---
M src/org/apache/ddlutils/platform/postgresql/PostgreSQLStandarization.java
---
|
|
|
|
(0079290)
|
|
hgbot
|
|
2015-08-10 15:32
|
|
Repository: erp/devel/dbsm-main
Changeset: 5a8caa93da114c2407081ef191beb3fe81c67360
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Wed Jul 22 09:32:02 2015 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/5a8caa93da114c2407081ef191beb3fe81c67360 [^]
related to bug 30397, related to bug 28684: DB inconsistencies in PG9.5
Ignoring test cases created for issue 0028684 because they do not apply anymore.
Now code for checks are read differently so the modifications previously tested
don't apply anymore.
---
M src-test/src/org/openbravo/dbsm/test/model/CheckConstraints.java
---
|
|
|
|
(0079292)
|
|
hgbot
|
|
2015-08-10 15:32
|
|
Repository: erp/devel/dbsm-main
Changeset: 15a0ac2c7460f7b42cdfc713686b1716233ab269
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Wed Jul 22 09:33:37 2015 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/15a0ac2c7460f7b42cdfc713686b1716233ab269 [^]
related to bug 30397: DB inconsistencies in PG9.5
Added test cases covering PostgreSQL standardization inconsistencies
---
M src-test/src/org/openbravo/dbsm/test/model/ModelSuite.java
A src-test/src/org/openbravo/dbsm/test/model/Pg95SqlStandardization.java
---
|
|
|
|
(0079307)
|
|
hgbot
|
|
2015-08-11 11:31
|
|
|
|
|
(0079308)
|
|
hgbot
|
|
2015-08-11 11:31
|
|
|
|
|
(0079309)
|
|
hgbot
|
|
2015-08-11 11:31
|
|
Repository: erp/pmods/org.openbravo.retail.posterminal
Changeset: b0597f72e055ea0af181a080fefd26c1f6798140
Author: Asier Lostalé <asier.lostale <at> openbravo.com>
Date: Tue Aug 11 11:29:27 2015 +0200
URL: http://code.openbravo.com/erp/pmods/org.openbravo.retail.posterminal/rev/b0597f72e055ea0af181a080fefd26c1f6798140 [^]
related to bug 30397: formal changes in check constraints
---
M src-db/database/model/modifiedTables/AD_ORG.xml
M src-db/database/model/tables/OBPOS_APPLICATIONS.xml
M src-db/database/model/tables/OBPOS_APP_PAYMENT.xml
M src-db/database/model/tables/OBPOS_APP_PAYMENT_TYPE.xml
---
|
|
|
|
(0079409)
|
|
hgbot
|
|
2015-08-18 14:08
|
|
Repository: erp/devel/api-checks
Changeset: bff3f7b287180c40bbb2a9c82b7c3d08a89fd5dd
Author: Stefan Hühner <stefan.huehner <at> openbravo.com>
Date: Tue Aug 18 14:08:11 2015 +0200
URL: http://code.openbravo.com/erp/devel/api-checks/rev/bff3f7b287180c40bbb2a9c82b7c3d08a89fd5dd [^]
Reapply cset: 6e1622fdf389 after q3 release
Original commit message:
changeset: 636:6e1622fdf389
user: Asier Lostalé <asier.lostale@openbravo.com>
date: Mon Aug 10 15:30:25 2015 +0200
related to bug 30397: DB inconsistencies in PG9.5
API change false positive: formal changes in check constraint definitions
---
M model/modules/org.openbravo.client.application/src-db/database/model/tables/OBUIAPP_PARAMETER.xml
M model/modules/org.openbravo.client.application/src-db/database/model/tables/OBUIAPP_REPORT.xml
M model/modules/org.openbravo.client.myob/src-db/database/model/tables/OBKMO_WIDGET_CLASS.xml
M model/modules/org.openbravo.client.querylist/src-db/database/model/tables/OBCQL_QUERY_COLUMN.xml
M model/modules/org.openbravo.utility.cleanup.log/src-db/database/model/tables/OBULOG_CONFIG.xml
M model/src-db/database/model/tables/AD_MODULE.xml
M model/src-db/database/model/tables/AD_MODULE_INSTALL.xml
M model/src-db/database/model/tables/AD_ORG.xml
M model/src-db/database/model/tables/AD_ORGINFO.xml
M model/src-db/database/model/tables/AD_ORGTYPE.xml
M model/src-db/database/model/tables/AD_ORG_ACCTSCHEMA.xml
M model/src-db/database/model/tables/AD_PREFERENCE.xml
M model/src-db/database/model/tables/AD_PROCESS_REQUEST.xml
M model/src-db/database/model/tables/AD_TABLE.xml
M model/src-db/database/model/tables/AD_WINDOW.xml
M model/src-db/database/model/tables/C_BANK.xml
M model/src-db/database/model/tables/C_BANKACCOUNT.xml
M model/src-db/database/model/tables/C_BANKSTATEMENTLINE.xml
M model/src-db/database/model/tables/C_BPARTNER.xml
M model/src-db/database/model/tables/C_BP_BANKACCOUNT.xml
M model/src-db/database/model/tables/C_CASHLINE.xml
M model/src-db/database/model/tables/C_DEBT_PAYMENT.xml
M model/src-db/database/model/tables/C_GLITEM.xml
M model/src-db/database/model/tables/C_INVOICELINE.xml
M model/src-db/database/model/tables/C_ORDERLINE.xml
M model/src-db/database/model/tables/C_TAX.xml
M model/src-db/database/model/tables/FIN_FINACC_TRANSACTION.xml
M model/src-db/database/model/tables/FIN_FINANCIAL_ACCOUNT.xml
M model/src-db/database/model/tables/FIN_FINANCIAL_ACCOUNT_ACCT.xml
M model/src-db/database/model/tables/FIN_PAYMENT.xml
M model/src-db/database/model/tables/GL_JOURNAL.xml
M model/src-db/database/model/tables/GL_JOURNALLINE.xml
M model/src-db/database/model/tables/MA_GLOBALUSE.xml
M model/src-db/database/model/tables/MA_SEQUENCEPRODUCT.xml
M model/src-db/database/model/tables/MA_WRPHASEPRODUCT.xml
M model/src-db/database/model/tables/M_INOUT.xml
M model/src-db/database/model/tables/M_INOUTLINE.xml
M model/src-db/database/model/tables/M_INVENTORYLINE.xml
M model/src-db/database/model/tables/M_LC_RECEIPT.xml
M model/src-db/database/model/tables/M_LC_TYPE.xml
M model/src-db/database/model/tables/M_MOVEMENTLINE.xml
M model/src-db/database/model/tables/M_PRODUCT.xml
M model/src-db/database/model/tables/M_PRODUCTIONLINE.xml
M model/src-db/database/model/tables/M_RAPPEL_SCALE.xml
M model/src-db/database/model/tables/M_STORAGE_DETAIL.xml
M model/src-db/database/model/tables/M_STORAGE_PENDING.xml
M model/src-db/database/model/tables/M_TRANSACTION.xml
M model/src-db/database/model/tables/S_TIMEEXPENSELINE.xml
---
|
|
|
|
|
|