Project:
| View Issue Details[ Jump to Notes ] | [ Issue History ] [ Print ] | |||||||
| ID | ||||||||
| 0038191 | ||||||||
| Type | Category | Severity | Reproducibility | Date Submitted | Last Update | |||
| backport | [Openbravo ERP] A. Platform | major | have not tried | 2018-03-20 16:53 | 2018-03-22 11:35 | |||
| Reporter | alostale | View Status | public | |||||
| Assigned To | alostale | |||||||
| Priority | immediate | Resolution | fixed | Fixed in Version | 3.0PR17Q4.2 | |||
| Status | closed | Fix in branch | Fixed in SCM revision | bcdaca2d9fb0 | ||||
| Projection | none | ETA | none | Target Version | 3.0PR17Q4.2 | |||
| 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 | 0038191: 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 |
|
|
(0103419) hgbot (developer) 2018-03-22 09:54 |
Repository: erp/backports/3.0PR17Q4.2 Changeset: 559616b29b38b5c83261413e2c0439f150619ff7 Author: Asier Lostalé <asier.lostale <at> openbravo.com> Date: Wed Mar 21 13:34:05 2018 +0100 URL: http://code.openbravo.com/erp/backports/3.0PR17Q4.2/rev/559616b29b38b5c83261413e2c0439f150619ff7 [^] fixed bug 38191: 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 --- |
|
(0103420) hgbot (developer) 2018-03-22 09:56 |
Repository: erp/devel/dbsm-main Changeset: 77c8c16158914c47ab32667e0eed32e75c79a30d 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/77c8c16158914c47ab32667e0eed32e75c79a30d [^] related to bug 38191: 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 --- |
|
(0103421) hgbot (developer) 2018-03-22 09:56 |
Repository: erp/devel/dbsm-main Changeset: bcdaca2d9fb066e3ea9ded8cda6c4c62a42952c6 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/bcdaca2d9fb066e3ea9ded8cda6c4c62a42952c6 [^] fixed bug 38191: 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 --- |
|
(0103425) caristu (developer) 2018-03-22 10:20 |
Code reviewed + tested OK. |
|
Notes |
|
Issue History |
|||
| Date Modified | Username | Field | Change |
| 2018-03-21 13:17 | alostale | Type | defect => backport |
| 2018-03-21 13:17 | alostale | Target Version | => 3.0PR17Q4.2 |
| 2018-03-22 09:54 | hgbot | Checkin | |
| 2018-03-22 09:54 | hgbot | Note Added: 0103419 | |
| 2018-03-22 09:54 | hgbot | Status | scheduled => resolved |
| 2018-03-22 09:54 | hgbot | Resolution | open => fixed |
| 2018-03-22 09:54 | hgbot | Fixed in SCM revision | => http://code.openbravo.com/erp/backports/3.0PR17Q4.2/rev/559616b29b38b5c83261413e2c0439f150619ff7 [^] |
| 2018-03-22 09:56 | hgbot | Checkin | |
| 2018-03-22 09:56 | hgbot | Note Added: 0103420 | |
| 2018-03-22 09:56 | hgbot | Checkin | |
| 2018-03-22 09:56 | hgbot | Note Added: 0103421 | |
| 2018-03-22 09:56 | hgbot | Fixed in SCM revision | http://code.openbravo.com/erp/backports/3.0PR17Q4.2/rev/559616b29b38b5c83261413e2c0439f150619ff7 [^] => http://code.openbravo.com/erp/devel/dbsm-main/rev/bcdaca2d9fb066e3ea9ded8cda6c4c62a42952c6 [^] |
| 2018-03-22 10:20 | caristu | Note Added: 0103425 | |
| 2018-03-22 10:20 | caristu | Status | resolved => closed |
| 2018-03-22 10:20 | caristu | Fixed in Version | => 3.0PR17Q4.2 |
| 2018-03-22 11:35 | caristu | Proposed Solution updated | |
|
Issue History |
| Copyright © 2000 - 2009 MantisBT Group |
 |