[postgis-tickets] [PostGIS] #4086: Constraint violation loading tiger_data schema from backup

[PostGIS]

#4086: Constraint violation loading tiger_data schema from backup
----------------------------+---------------------------
 Reporter:  bpanulla        |      Owner:  robe
     Type:  defect          |     Status:  new
 Priority:  medium          |  Milestone:  PostGIS 2.4.5
Component:  tiger geocoder  |    Version:  2.4.x
 Keywords:                  |
----------------------------+---------------------------
 After loading the Tiger Geocoder in a staging database I moved it to my
 main development database on the same server via pg_dump/pg_restore.

 The restore generated constraint errors trying to load table zcta_all:

 {{{
 pg_restore: [archiver (db)] Error from TOC entry 5776; 0 1025631 TABLE
 DATA zcta5_all dbadmin
 pg_restore: [archiver (db)] COPY failed for table "zcta5_all": ERROR:  new
 row for relation "zcta5_all" violates check constraint
 "enforce_geotype_the_geom"
 DETAIL:  Failing row contains (1, 39, 43451, B5, G6350, S, 63411475,
 157689, +41.3183010, -083.6174935, N,
 0103000020AD100000010000001600000014950D6B2AEB54C0C80A7E1B62AA44...).
 CONTEXT:  COPY zcta5_all, line 1: "1    39      43451   B5      G6350   S
 63411475        157689  +41.3183010     -083.6174935    N
 0103000020AD10000001000000160000001..."
 }}}

 Seems as though the tiger.zcta5 table has a constraint enforcing type of
 objects:

 {{{
 CONSTRAINT enforce_geotype_the_geom CHECK ((geometrytype(the_geom) =
 'MULTIPOLYGON'::text)
     OR  (the_geom IS NULL)),
 }}}

 Most of the records from the 2017 load are POLYGON type, with a handful
 GEOMETRYCOLLECTION.

 This constraint seems to be created on the tiger.zcta5 table by the CREATE
 EXTENSION for tiger_geocoder

 Dropping the constraint allowed the restore to proceed normally.

 Discovered on Ubuntu 16.04 with PostGIS installed via package

 POSTGIS="2.4.4 r16526" PGSQL="100" GEOS="3.5.0-CAPI-1.9.0 r4084"
 PROJ="Rel. 4.9.2, 08 September 2015" GDAL="GDAL 1.11.3, released
 2015/09/16" LIBXML="2.9.3" LIBJSON="0.11.99" LIBPROTOBUF="1.2.1" RASTER

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4086>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.