Project:
| View Issue Details[ Jump to Notes ] | [ Issue History ] [ Print ] | |||||||
| ID | ||||||||
| 0038179 | ||||||||
| Type | Category | Severity | Reproducibility | Date Submitted | Last Update | |||
| defect | [Openbravo ERP] A. Platform | major | have not tried | 2018-03-20 16:53 | 2018-03-23 21:22 | |||
| Reporter | alostale | View Status | public | |||||
| Assigned To | alostale | |||||||
| Priority | immediate | Resolution | fixed | Fixed in Version | 3.0PR18Q2 | |||
| Status | closed | Fix in branch | Fixed in SCM revision | 044cc9ca3e7f | ||||
| Projection | none | ETA | none | Target Version | ||||
| OS | Any | Database | PostgreSQL | Java version | ||||
| OS Version | Database version | Ant version | ||||||
| Product Version | SCM revision | |||||||
| Review Assigned To | caristu | |||||||
| Web browser | ||||||||
| Modules | Core | |||||||
| Regression level | ||||||||
| Regression date | ||||||||
| Regression introduced in release | ||||||||
| Regression introduced by commit | ||||||||
| Triggers an Emergency Pack | No | |||||||
| Summary | 0038179: cannot restore pg dump | |||||||
| Description | It is not possible to restore a PostgreSQL database dump. Affected PostgreSQL versions (all of them released on 2018-03-01): * 10.3+ * 9.6.8+ * 9.5.12+ * 9.4.17+ * 9.3.22+ Note this to be reproduced, dump needs to be acquired with one of those versions of higher. Dumps obtained with older versions can be properly imported. More detailed explanations: https://stackoverflow.com/questions/49380321/pg-restore-cant-import-data-if-table-has-a-constraint-with-a-function-calling [^] https://www.postgresql.org/message-id/152153826367.11956.8092048336300020216%40wrigleys.postgresql.org [^] | |||||||
| Steps To Reproduce | In an Openbravo appliance: 1. Update pg: sudo apt-get update && sudo apt-get dist-upgrade -> check it is at least in 9.3.22 2. Dump DB: pg_dump -h localhost -p 5432 -F c -b -v -f test.dmp openbravo -U tad 3. Drop openbravo DB and create it again. 4. Import DB: pg_restore -d openbravo -U tad -v test.dmp -h localhost -> Check logs: pg_restore: processing data for table "public.ad_tab" pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 14059; 0 4295695 TABLE DATA ad_tab tad pg_restore: [archiver (db)] COPY failed for table "ad_tab": ERROR: function instr(character varying, character varying, integer) does not exist LINE 1: SELECT instr($1, $2, 1) ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. QUERY: SELECT instr($1, $2, 1) CONTEXT: PL/pgSQL function public.instr(character varying,character varying) line 4 at assignment COPY ad_tab, line 1: "100 0 0 Y 2016-09-02 09:59:59.438222 0 2016-09-02 09:59:59.438222 0 Table Define tables that Openbra..." Data is not imported for failing tables: ad_tab, ad_window... | |||||||
| Proposed Solution | Workaround In instances where the fix for this issue is not applied yet, it is possible to dump + restore following one of these two workarounds: 1. To obtain a dump that can be restored without problem * before dumping DB, if patch for this issue is not applied, execute the following script [1] 2. To restore a dump that was obtained without applying 1st workaround: * Restore schema without data adding to pg_restore --schema-only option [2] * Run the following script [1] * Restore data only adding to pg_restore --data-only --disable-triggers options [2] --- [1] https://github.com/alostale/ob-scripts/blob/master/sql/fixes/pg-schema-fix.sql [^] [2] https://www.postgresql.org/docs/9.3/static/app-pgrestore.html [^] | |||||||
| Tags | No tags attached. | |||||||
| Attached Files | ||||||||
 Relationships [ Relation Graph ]
[ Dependency Graph ]
|
|||||||||||||||||||||||||||||
|
|||||||||||||||||||||||||||||
 Relationships |
|
Notes |
|
|
(0103410) hgbot (developer) 2018-03-22 08:53 |
Repository: erp/devel/dbsm-main Changeset: 9934be2bef8c98ad567dc54dda4a4a2b3b38dfd8 Author: Asier Lostalé <asier.lostale <at> openbravo.com> Date: Wed Mar 21 13:22:36 2018 +0100 URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/9934be2bef8c98ad567dc54dda4a4a2b3b38dfd8 [^] related to bug 38179: added test cases --- M src-test/src/org/openbravo/dbsm/test/model/Functions.java M src-test/src/org/openbravo/dbsm/test/model/recreation/PLCode.java A src-test/model/functions/SIMPLE_FUNCTION.xml --- |
|
(0103411) hgbot (developer) 2018-03-22 08:53 |
Repository: erp/devel/dbsm-main Changeset: 268200257d1c3924e1298d899505ae5a42d6db1e Author: Asier Lostalé <asier.lostale <at> openbravo.com> Date: Wed Mar 21 13:25:23 2018 +0100 URL: http://code.openbravo.com/erp/devel/dbsm-main/rev/268200257d1c3924e1298d899505ae5a42d6db1e [^] fixed bug 38179: cannot restore pg dump When creating/updating functions in PostgreSQL, explicitily set search_path so pg_dump + pg_restore is able to execute them in case its needed (ie. in check constraints or indexes). First time functions are updated search_path is added. In case function already has it because of it was added by dbsm or manually, it is not modified, allowing in this way manual changes to it in case it is required. --- M src/org/apache/ddlutils/alteration/ModelComparator.java M src/org/apache/ddlutils/model/Function.java M src/org/apache/ddlutils/platform/postgresql/PostgreSqlBuilder.java M src/org/apache/ddlutils/platform/postgresql/PostgreSqlModelLoader.java --- |
|
(0103412) hgbot (developer) 2018-03-22 08:53 |
Repository: erp/devel/pi Changeset: 044cc9ca3e7fa108c11034c364468749f112d50c Author: Asier Lostalé <asier.lostale <at> openbravo.com> Date: Wed Mar 21 13:34:05 2018 +0100 URL: http://code.openbravo.com/erp/devel/pi/rev/044cc9ca3e7fa108c11034c364468749f112d50c [^] fixed bug 38179: cannot restore pg dump When creating/updating functions in PostgreSQL, explicitily set search_path so pg_dump + pg_restore is able to execute them in case its needed (ie. in check constraints or indexes). First time functions are updated search_path is added. In case function already has it because of it was added by dbsm or manually, it is not modified, allowing in this way manual changes to it in case it is required. In addition, PL functions created in prescript that invoke other functions that are not in pg_catalog schema do also define search_path. --- M src-db/database/lib/dbsourcemanager.jar M src-db/database/model/prescript-PostgreSql.sql --- |
|
(0103422) caristu (developer) 2018-03-22 09:59 |
Code reviewed + tested OK. |
|
(0103471) hudsonbot (developer) 2018-03-23 21:22 |
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/42e5233f5207 [^] Maturity status: Test |
|
Notes |
|
Issue History |
|||
| Date Modified | Username | Field | Change |
| 2018-03-20 16:53 | alostale | New Issue | |
| 2018-03-20 16:53 | alostale | Assigned To | => platform |
| 2018-03-20 16:53 | alostale | Modules | => Core |
| 2018-03-20 16:53 | alostale | Triggers an Emergency Pack | => No |
| 2018-03-20 16:54 | alostale | Assigned To | platform => alostale |
| 2018-03-20 16:55 | alostale | Description Updated | View Revisions |
| 2018-03-20 18:01 | shuehner | Issue Monitored: shuehner | |
| 2018-03-20 19:24 | Practics | Issue Monitored: Practics | |
| 2018-03-21 13:17 | alostale | Review Assigned To | => caristu |
| 2018-03-21 13:17 | alostale | Status | new => scheduled |
| 2018-03-22 08:53 | hgbot | Checkin | |
| 2018-03-22 08:53 | hgbot | Note Added: 0103410 | |
| 2018-03-22 08:53 | hgbot | Checkin | |
| 2018-03-22 08:53 | hgbot | Note Added: 0103411 | |
| 2018-03-22 08:53 | hgbot | Status | scheduled => resolved |
| 2018-03-22 08:53 | hgbot | Resolution | open => fixed |
| 2018-03-22 08:53 | hgbot | Fixed in SCM revision | => http://code.openbravo.com/erp/devel/dbsm-main/rev/268200257d1c3924e1298d899505ae5a42d6db1e [^] |
| 2018-03-22 08:53 | hgbot | Checkin | |
| 2018-03-22 08:53 | hgbot | Note Added: 0103412 | |
| 2018-03-22 08:53 | hgbot | Fixed in SCM revision | http://code.openbravo.com/erp/devel/dbsm-main/rev/268200257d1c3924e1298d899505ae5a42d6db1e [^] => http://code.openbravo.com/erp/devel/pi/rev/044cc9ca3e7fa108c11034c364468749f112d50c [^] |
| 2018-03-22 09:00 | alostale | Proposed Solution updated | |
| 2018-03-22 09:59 | caristu | Note Added: 0103422 | |
| 2018-03-22 09:59 | caristu | Status | resolved => closed |
| 2018-03-22 09:59 | caristu | Fixed in Version | => 3.0PR18Q2 |
| 2018-03-23 21:22 | hudsonbot | Checkin | |
| 2018-03-23 21:22 | hudsonbot | Note Added: 0103471 | |
| 2018-04-18 10:00 | ngarcia | Relationship added | has duplicate 0036420 |
| 2018-04-18 10:00 | ngarcia | Relationship deleted | has duplicate 0036420 |
| 2019-03-05 13:55 | alostale | Relationship added | related to 0040317 |
| 2019-03-05 14:04 | alostale | Relationship added | related to 0029943 |
|
Issue History |
| Copyright © 2000 - 2009 MantisBT Group |
 |