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

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0038190
TypeCategorySeverityReproducibilityDate SubmittedLast Update
backport[Openbravo ERP] A. Platformmajorhave not tried2018-03-20 16:532018-03-22 11:35
ReporteralostaleView Statuspublic 
Assigned Toalostale 
PriorityimmediateResolutionfixedFixed in Version3.0PR18Q1.1
StatusclosedFix in branchFixed in SCM revisione866c1dfc8a3
ProjectionnoneETAnoneTarget Version3.0PR18Q1.1
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

0038190: 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 ]
blocks defect 0038179 closedalostale cannot restore pg dump 

-  Notes
(0103423)
hgbot (developer)
2018-03-22 10:03

 Repository: erp/backports/3.0PR18Q1.1
Changeset: e866c1dfc8a3a3a0116ffcefcee43b124d7dc911
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.0PR18Q1.1/rev/e866c1dfc8a3a3a0116ffcefcee43b124d7dc911 [^]

fixed bug 38190: 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
---
(0103424)
caristu (developer)
2018-03-22 10:08

 Code reviewed + tested

- 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.0PR18Q1.1
2018-03-22 10:03 hgbot Checkin
2018-03-22 10:03 hgbot Note Added: 0103423
2018-03-22 10:03 hgbot Status scheduled => resolved
2018-03-22 10:03 hgbot Resolution open => fixed
2018-03-22 10:03 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/backports/3.0PR18Q1.1/rev/e866c1dfc8a3a3a0116ffcefcee43b124d7dc911 [^]
2018-03-22 10:08 caristu Note Added: 0103424
2018-03-22 10:08 caristu Status resolved => closed
2018-03-22 10:08 caristu Fixed in Version => 3.0PR18Q1.1
2018-03-22 11:35 caristu Proposed Solution updated

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker