dump/restore of tiger geocoder extension fails

[strk]

The dump contains a few tiger geocoder functions that cannot be restored:

246; 3079 46281 EXTENSION - postgis_tiger_geocoder 
3629; 0 0 COMMENT - EXTENSION postgis_tiger_geocoder 
...
292; 1255 46570 FUNCTION tiger create_census_base_tables() docker
290; 1255 46295 FUNCTION tiger get_geocode_setting(text) docker
3632; 0 0 COMMENT tiger FUNCTION get_geocode_setting(setting_name text) docker
289; 1255 46286 FUNCTION tiger install_geocode_settings() docker
293; 1255 46571 FUNCTION tiger loader_generate_census_script(text[], text) docker
3633; 0 0 COMMENT tiger FUNCTION loader_generate_census_script(param_states text[], os text) docker
291; 1255 46296 FUNCTION tiger set_geocode_setting(text, text) docker
3634; 0 0 COMMENT tiger FUNCTION set_geocode_setting(setting_name text, setting_value text) docker
312; 1255 46696 FUNCTION tiger topology_load_tiger(character varying, character varying, character varying) docker
3635; 0 0 COMMENT tiger FUNCTION topology_load_tiger(toponame character varying, region_type character varying, region_id character varying) docker

Functions creation fails as already existing (due to earlier CREATE EXTENSION, I'd think):

pg_restore: [archiver (db)] could not execute query: ERROR:  function "create_census_base_tables" already exists with same argument types
pg_restore: [archiver (db)] could not execute query: ERROR:  function "get_geocode_setting" already exists with same argument types
pg_restore: [archiver (db)] could not execute query: ERROR:  function "install_geocode_settings" already exists with same argument types
pg_restore: [archiver (db)] could not execute query: ERROR:  function "loader_generate_census_script" already exists with same argument types
pg_restore: [archiver (db)] could not execute query: ERROR:  function "topology_load_tiger" already exists with same argument types

The dump was taken and restored from and to a 9.3/2.1.8 setup

[strk]

Note that the database started as a 2.1.7 and was soft-upgraded (ALTER EXTENSION .. UPDATE) to 2.1.8.

[strk]

Another error is in duplicated value in a supposed primary key value (table not tagged as belonging to extension?):

pg_restore: [archiver (db)] COPY failed for table "geocode_settings": ERROR:  duplicate key value violates unique constraint "geocode_settings_pkey"
DETAIL:  Key (name)=(debug_geocode_address) already exists.

[strk]

According to David Fetter this might be a PostgreSQL issue. The restore is being run on:

 PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

I cannot tell where the dump has been run

[strk]

And this is the other:

ERROR:  function "set_geocode_setting" already exists with same argument types

For a total of 7 objects, as confirmed by pg_restore:

WARNING: errors ignored on restore: 7

[strk]

Script to reproduce, which can be tested with different versions:

#!/bin/sh                                                                       
                                                                                
VER='2.1.3'                                                                     
if test -n "$1"; then                                                           
  VER="$1"                                                                      
fi                                                                              
                                                                                
dropdb tt                                                                       
createdb tt                                                                     
cat<<EOF | psql tt                                                              
create extension postgis version '$VER';                                        
create extension fuzzystrmatch;                                                 
create extension postgis_tiger_geocoder version '$VER';                         
EOF                                                                             
pg_dump -Fc tt > tt.dump                                                        
dropdb tt                                                                       
createdb tt                                                                     
pg_restore -d tt tt.dump 

For me it fails with a single error in 2.1.3, 2.1.8dev and 2.1.9dev (duplicate 'debug_geocode_address' key in geocode_settings_pkey)

[strk]

Note that the script dumps and reloads into the same system with the same versions installed (PostgreSQL 9.3.6 here)

[robe]

Two issues going on here:

1) Is it possible this database started off with installing tiger geocoder with scripts and then got converted to EXTENSION using the unpackaged routine? It's been a while since I tested the

FROM unpackaged route so that might be out of date

2) the duplicate key thing with set_geocode_setting. I thought I had fixed that in 2.1, but it's possible I didn't or only fixed in 2.2 series. So I'll check on that.

[strk]

You can actually see the script in ​https://trac.osgeo.org/postgis/ticket/3430#comment:5 -- the database is newly created.

[strk]

I'm trying to reproduce this now but I can't. Guess it depends on the fact that what the "restore" phase does depends on what's available on the system. Now that I cannot reproduce I have "default version" of tiger_geocoder_extension being "2.3.0dev". Will get back to 2.2 series as a second attempt.

[strk]

also works with default_version=2.2.2dev.

It takes default_version=2.1.9dev to reproduce, from "2.1.3" to "2.1.9dev".

SET
CREATE EXTENSION
CREATE EXTENSION
CREATE EXTENSION
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3344; 0 51783822 TABLE DATA geocode_settings strk
pg_restore: [archiver (db)] COPY failed for table "geocode_settings": ERROR:  duplicate key value violates unique constraint "geocode_settings_pkey"
DETAIL:  Key (name)=(debug_geocode_address) already exists.
CONTEXT:  COPY geocode_settings, line 1
WARNING: errors ignored on restore: 1

[strk]

Also from 2.1.9dev to 2.1.9dev