Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue DetailsJump to Notes ] Issue History ] Print ]
ID
0038179
TypeCategorySeverityReproducibilityDate SubmittedLast Update
defect[Openbravo ERP] A. Platformmajorhave not tried2018-03-20 16:532018-03-23 21:22
ReporteralostaleView Statuspublic 
Assigned Toalostale 
PriorityimmediateResolutionfixedFixed in Version3.0PR18Q2
StatusclosedFix in branchFixed in SCM revision044cc9ca3e7f
ProjectionnoneETAnoneTarget Version
OSAnyDatabasePostgreSQLJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Tocaristu
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0038179: cannot restore pg dump

DescriptionIt 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 ReproduceIn 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 SolutionWorkaround
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 [^]
TagsNo tags attached.
Attached Files

- Relationships Relation Graph ] Dependency Graph ]
depends on backport 00381903.0PR18Q1.1 closedalostale cannot restore pg dump 
depends on backport 00381913.0PR17Q4.2 closedalostale cannot restore pg dump 
related to defect 0040317 closedalostale can't restore pg dump of a retail instance 
related to feature request 0029943 closedalostale support to define DB functions volatility 

-  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

- 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


Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker