Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0035775Openbravo ERPY. DBSourceManagerpublic2017-04-12 15:052017-06-02 07:31
ReporterAugustoMauch 
Assigned ToAugustoMauch 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR17Q3 
Merge Request Status
Review Assigned Toalostale
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0035775: Triggers that are excluded from the model are not disabled during update.database in PostgreSQL
DescriptionTriggers are disabled during part of the update.database process (there are disabled here [1] and reenabled here [2]). In PostgreSQL only the triggers not excluded from the model are disabled [3] while in Oracle all the user triggers are disabled [4].

One consequence of not disabling all the triggers is that some might no longer be valid after updating the model. For instance, the Audit Trail triggers are excluded from the model, meaning that they will not be disabled during update.database, and that may result in the update.database failing.

[1] https://code.openbravo.com/erp/devel/dbsm-main/file/f5fc17d37524/src/org/openbravo/ddlutils/task/AlterDatabaseDataAll.java#l219 [^]
[2] https://code.openbravo.com/erp/devel/dbsm-main/file/f5fc17d37524/src/org/openbravo/ddlutils/task/AlterDatabaseDataAll.java#l247 [^]
[3] https://code.openbravo.com/erp/devel/dbsm-main/file/f5fc17d37524/src/org/apache/ddlutils/platform/postgresql/PostgreSqlPlatform.java#l371 [^]
[4] https://code.openbravo.com/erp/devel/dbsm-main/file/f5fc17d37524/src/org/apache/ddlutils/platform/oracle/Oracle8Platform.java#l252 [^]
Steps To ReproduceAs System Admin, in an activated instance:
- Open the Tables and Columns window
- Select the AD_COLUMN table. Set to true its Fully Audited flag.
- Run the Update Audit Trail Infrastructure process to create the audit trigger in ad_column.
- Apply the patch attached to the issue (it removes the ALLOWED_CROSS_ORG_LINK column from AD_COLUMN).
- Run update.database. The following exception will be thrown:

     [java] 71928 INFO - Updating Application Dictionary data...
     [java] 71957 ERROR - Error while deleting from the database
     [java] org.postgresql.util.PSQLException: ERROR: record "old" has no field "allowed_cross_org_link"
     [java] Where: SQL statement "SELECT old.ALLOWED_CROSS_ORG_LINK"
     [java] PL/pgSQL function au_ad_column_trg() line 1558 at assignment
     [java] at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
     [java] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
     [java] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
     [java] at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:622)
     [java] at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:472)
     [java] at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:429)
     [java] at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
     [java] at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
     [java] at org.apache.ddlutils.platform.PlatformImplBase.delete(PlatformImplBase.java:2301)
     [java] at org.apache.ddlutils.platform.PlatformImplBase.alterData(PlatformImplBase.java:818)
     [java] at org.openbravo.ddlutils.task.AlterDatabaseDataAll.doExecute(AlterDatabaseDataAll.java:231)
     [java] at org.openbravo.ddlutils.task.BaseDatabaseTask.execute(BaseDatabaseTask.java:86)
     [java] at org.openbravo.ddlutils.task.AlterDatabaseJava.main(AlterDatabaseJava.java:57)
Proposed SolutionDisable all the triggers, even the ones excluded from the model
Additional Information
TagsNo tags attached.
Relationships
Attached Filesdiff deleteColumn.diff (8,795) 2017-04-12 15:29
https://issues.openbravo.com/file_download.php?file_id=10674&type=bug
Issue History
Date ModifiedUsernameFieldChange
2017-04-12 15:05AugustoMauchNew Issue
2017-04-12 15:05AugustoMauchAssigned To => platform
2017-04-12 15:05AugustoMauchModules => Core
2017-04-12 15:05AugustoMauchTriggers an Emergency Pack => No
2017-04-12 15:29AugustoMauchFile Added: deleteColumn.diff
2017-04-12 15:29AugustoMauchAssigned Toplatform => AugustoMauch
2017-04-12 15:29AugustoMauchStatusnew => scheduled
2017-04-25 16:12AugustoMauchNote Added: 0096160
2017-05-25 14:52AugustoMauchReview Assigned To => alostale
2017-05-25 14:55hgbotCheckin
2017-05-25 14:55hgbotNote Added: 0096804
2017-05-25 14:55hgbotStatusscheduled => resolved
2017-05-25 14:55hgbotResolutionopen => fixed
2017-05-25 14:55hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/dbsm-main/rev/248c39121a3bfba20c0dc621c8a8dc7d6e71aa84 [^]
2017-05-25 14:55hgbotCheckin
2017-05-25 14:55hgbotNote Added: 0096805
2017-05-25 14:55hgbotCheckin
2017-05-25 14:55hgbotNote Added: 0096806
2017-05-25 14:55hgbotCheckin
2017-05-25 14:55hgbotNote Added: 0096807
2017-05-25 14:55hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/dbsm-main/rev/248c39121a3bfba20c0dc621c8a8dc7d6e71aa84 [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/715929ba6e4cf50633870e4d98bb04097a5f1bcb [^]
2017-05-25 14:55hgbotCheckin
2017-05-25 14:55hgbotNote Added: 0096808
2017-05-25 14:55hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/dbsm-main/rev/715929ba6e4cf50633870e4d98bb04097a5f1bcb [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/312e5d89376ca0001248f49b642991d8bf5feb37 [^]
2017-05-25 14:57hgbotCheckin
2017-05-25 14:57hgbotNote Added: 0096809
2017-05-25 14:57hgbotFixed in SCM revisionhttp://code.openbravo.com/erp/devel/dbsm-main/rev/312e5d89376ca0001248f49b642991d8bf5feb37 [^] => http://code.openbravo.com/erp/devel/pi/rev/2ea6fb210371eb9731922e891302983780143774 [^]
2017-05-29 17:43hudsonbotCheckin
2017-05-29 17:43hudsonbotNote Added: 0096990
2017-06-02 07:31alostaleNote Added: 0097087
2017-06-02 07:31alostaleStatusresolved => closed
2017-06-02 07:31alostaleFixed in Version => 3.0PR17Q3

Notes
(0096160)
AugustoMauch   
2017-04-25 16:12   
Development pushed to this repo: https://code.openbravo.com/erp/devel/dbsm-disable-excluded-triggers/ [^]
(0096804)
hgbot   
2017-05-25 14:55   
Repository: erp/devel/dbsm-main
Changeset: 248c39121a3bfba20c0dc621c8a8dc7d6e71aa84
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Wed Apr 19 15:48:12 2017 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/248c39121a3bfba20c0dc621c8a8dc7d6e71aa84 [^]

Fixes issue 35775: Trigger excluded from the model are also disabled/enabled

The problem was that in PostgreSQL, the user triggers not included in the model were not disabled/enabled by the Platform.disableAllTrigger and Platform.enableAllTriggers method respectively.

To fix this, the list of tables with triggers to disable/enable is built using this query:

select distinct relname
from pg_trigger trg
left join pg_class tbl on trg.tgrelid = tbl.oid
where tgisinternal = false order by relname

Then the triggers of those tables are disabled/enabled by executing a statement like:

ALTER TABLE <tableName> DISABLE/ENABLE TRIGGER USER;

---
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlPlatform.java
---
(0096805)
hgbot   
2017-05-25 14:55   
Repository: erp/devel/dbsm-main
Changeset: 1e0bfac9be7231a884440f851229e2967324eaa3
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Wed Apr 19 16:05:21 2017 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/1e0bfac9be7231a884440f851229e2967324eaa3 [^]

Related with issue 35775: Adds a test to check all user triggers are disabled

The test checks that between two Platform.disableAllTriggers() and Platform.enableAllTriggers() statements, user triggers are disabled, even if they are not part of the model.

---
A src-test/model/excludeFilter/BASE_MODEL_WITH_COUNT_AND_TRIGGER.xml
A src-test/src/org/openbravo/dbsm/test/model/CheckTriggerDisablement.java
---
(0096806)
hgbot   
2017-05-25 14:55   
Repository: erp/devel/dbsm-main
Changeset: ce21eb0be0cc2f2954a9567f6887f1fa5cab9cee
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Tue May 23 11:20:50 2017 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/ce21eb0be0cc2f2954a9567f6887f1fa5cab9cee [^]

Related with issue 35775: Applies code review comments

The following changes have been done:
- The commands to enable/disable all the user triggers are now executed in a batch. This requires having access to the SQLBuilder.printEndOfStatement method, whose visibility has been changed from protected to public
- Duplicity between the PostgreSQL and Oracle version has been removed by moving up the implementation to the PlatformImplBase class. Now the subclasses only need to specify the SQL command that must be used to generate the queries that will disable/enable the user triggers
- Try with resources is used instead of manually closing the connection
- System.out statements have been removed

---
M src/org/apache/ddlutils/platform/PlatformImplBase.java
M src/org/apache/ddlutils/platform/SqlBuilder.java
M src/org/apache/ddlutils/platform/oracle/Oracle8Platform.java
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlPlatform.java
---
(0096807)
hgbot   
2017-05-25 14:55   
Repository: erp/devel/dbsm-main
Changeset: 715929ba6e4cf50633870e4d98bb04097a5f1bcb
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Tue May 23 11:33:53 2017 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/715929ba6e4cf50633870e4d98bb04097a5f1bcb [^]

Fixes issue 35775: Applies code review comments

The following changes have been done on CheckTriggerDisablement:
- printStackTrace has been removed
- try with resources is used
- a proper assertion message is shown if the assertion fail
- exception is not cached, so that the test fails when it is thrown

---
M src-test/src/org/openbravo/dbsm/test/model/CheckTriggerDisablement.java
---
(0096808)
hgbot   
2017-05-25 14:55   
Repository: erp/devel/dbsm-main
Changeset: 312e5d89376ca0001248f49b642991d8bf5feb37
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Thu May 25 14:45:07 2017 +0200
URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/312e5d89376ca0001248f49b642991d8bf5feb37 [^]

Fixes issue 35775: Triggers excluded from model are also disabled during build

Now all the user triggers, even the ones excluded from the model, are disabled during the build, and reenabled right after.

---
M src/org/apache/ddlutils/platform/PlatformImplBase.java
M src/org/apache/ddlutils/platform/SqlBuilder.java
M src/org/apache/ddlutils/platform/oracle/Oracle8Platform.java
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlPlatform.java
A src-test/model/excludeFilter/BASE_MODEL_WITH_COUNT_AND_TRIGGER.xml
A src-test/src/org/openbravo/dbsm/test/model/CheckTriggerDisablement.java
---
(0096809)
hgbot   
2017-05-25 14:57   
Repository: erp/devel/pi
Changeset: 2ea6fb210371eb9731922e891302983780143774
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Thu May 25 14:52:15 2017 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/2ea6fb210371eb9731922e891302983780143774 [^]

Fixes issue 35775: Triggers excluded from model are also disabled during build

Now all the user triggers, even the ones excluded from the model, are disabled during the build, and reenabled right after.

---
M src-db/database/lib/dbsourcemanager.jar
---
(0096990)
hudsonbot   
2017-05-29 17:43   
A changeset related to this issue has been promoted main and to the
Central Repository, after passing a series of tests.

Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/1ee70113bdc4 [^]
Maturity status: Test
(0097087)
alostale   
2017-06-02 07:31   
code reviewed + tested as a project

https://docs.google.com/spreadsheets/d/16IX_hJ1CaacoqvD1DirB1ALLZHPlDvsIcufLpHN2E0g/edit#gid=0 [^]