Context Navigation

← Previous Ticket
Next Ticket →

#1708 closed defect (fixed)

postgis_restore not converting ndims and srid constraints to st_...

Reported by:	robe	Owned by:	strk
Priority:	medium	Milestone:	PostGIS 2.0.1
Component:	build	Version:	master
Keywords:	history	Cc:

Description

Strk - my tables that use the deprecated ndims, srid constraints checks won't load without me first installing legacy_minimal.sql

Attached is an example backup. I'm using ActiveState Perl64 if that makes a difference.

my script looks like this:

set PGPORT=5434
set PGHOST=localhost
set PGUSER=postgres
set PGPASSWORD=something
set THEDB=test
set PGINSTALL=C:\Program Files\PostgreSQL\9.1
set PGBIN=%PGINSTALL%\bin\
set PGLIB=%PGINSTALL%\lib\
set PATH=%PATH%;%PGBIN%
perl utils/postgis_restore.pl "restore_test.backup" | psql %THEDB% 2> errors_with_test.txt

The error file is empty.

Attachments (1)

restore_test.backup (89.1 KB ) - added by robe 12 years ago.: postgresql 9.1 backup file

Download all attachments as: .zip

Change History (8)

by robe, 12 years ago

Attachment:	restore_test.backup added

postgresql 9.1 backup file

comment:1 by strk, 12 years ago

Status:	new → assigned

comment:2 by strk, 12 years ago

Confirmed for ndims, not for st_srid. Could you please write the output from postgis_restore.pl into a file and then grep CHECK in it ?

My output:

$ grep CHECK backup.sql 
    CONSTRAINT enforce_dims_the_geom CHECK ((ndims(the_geom) = 2)),
    CONSTRAINT enforce_geotype_the_geom CHECK (((geometrytype(the_geom) = 'MULTIPOLYGON'::text) OR (the_geom IS NULL))),
    CONSTRAINT enforce_srid_the_geom CHECK ((st_srid(the_geom) = 2249))
]}}

You can see "enforce_srid_the_geom" CHECK was correctly rewritten

comment:3 by strk, 12 years ago

Resolution:	→ fixed
Status:	assigned → closed

Fixed with r9637

comment:4 by robe, 12 years ago

Keywords:	history added

comment:5 by robe, 12 years ago

Resolution:	fixed
Status:	closed → reopened

This is still not working even using the utils from PostGIS 2.1.0.

Though I wonder if its you are assuming something about the name of the the geometry column or your script is getting tripped up by inherited tables.

Here is one case that didn't come in right: (The ones that are right I had changed in the old db so can't be sure if those ones are working right or not)

CREATE TABLE assessing.parceltime_2005
(
-- Inherited from table parceltime:  pid character(10) NOT NULL,
-- Inherited from table parceltime:  cm_id character(10),
-- Inherited from table parceltime:  st_num character varying(10),
-- Inherited from table parceltime:  st_name character varying(75),
-- Inherited from table parceltime:  name_suf character varying(20),
-- Inherited from table parceltime:  unit_num character varying(10),
-- Inherited from table parceltime:  zipcode character varying(10),
-- Inherited from table parceltime:  ptype integer,
-- Inherited from table parceltime:  lu character varying(5),
-- Inherited from table parceltime:  exmpt_code character varying(20),
-- Inherited from table parceltime:  ownocc character varying(1),
-- Inherited from table parceltime:  owner character varying(150),
-- Inherited from table parceltime:  address character varying(150),
-- Inherited from table parceltime:  city_state character varying(75),
-- Inherited from table parceltime:  mail_zip character varying(10),
-- Inherited from table parceltime:  landval bigint,
-- Inherited from table parceltime:  bldgval bigint,
-- Inherited from table parceltime:  totalval bigint,
-- Inherited from table parceltime:  gross_tax numeric(31,15),
-- Inherited from table parceltime:  legal_area bigint,
-- Inherited from table parceltime:  year_built integer,
-- Inherited from table parceltime:  year_remod integer,
-- Inherited from table parceltime:  gross_area bigint,
-- Inherited from table parceltime:  area bigint,
-- Inherited from table parceltime:  num_floors numeric(31,15),
-- Inherited from table parceltime:  class character varying(15),
-- Inherited from table parceltime:  r_bldg_stl character varying(50),
-- Inherited from table parceltime:  r_roof_typ character varying(50),
-- Inherited from table parceltime:  r_ext_fin character varying(100),
-- Inherited from table parceltime:  total__rms integer,
-- Inherited from table parceltime:  r_bdrms integer,
-- Inherited from table parceltime:  r_full_bth integer,
-- Inherited from table parceltime:  r_half_bth integer,
-- Inherited from table parceltime:  r_kitch integer,
-- Inherited from table parceltime:  r_heat_typ character varying(1),
-- Inherited from table parceltime:  r_ac character varying(1),
-- Inherited from table parceltime:  r_fplace integer,
-- Inherited from table parceltime:  s_num_bldg integer,
-- Inherited from table parceltime:  s_bldg_sty character varying(5),
-- Inherited from table parceltime:  s_unit_res integer,
-- Inherited from table parceltime:  s_unit_com integer,
-- Inherited from table parceltime:  s_unit_rc integer,
-- Inherited from table parceltime:  s_ext_fin character varying(2),
-- Inherited from table parceltime:  u_base_flr integer,
-- Inherited from table parceltime:  u_num_prk integer,
-- Inherited from table parceltime:  u_corner character varying(1),
-- Inherited from table parceltime:  u_orient character varying(2),
-- Inherited from table parceltime:  u_num_rms integer,
-- Inherited from table parceltime:  u_num_beds integer,
-- Inherited from table parceltime:  u_bths integer,
-- Inherited from table parceltime:  u_half_bth integer,
-- Inherited from table parceltime:  u_kitch character varying(2),
-- Inherited from table parceltime:  u_heat character varying(2),
-- Inherited from table parceltime:  u_ac character varying(1),
-- Inherited from table parceltime:  u_fplaces integer,
-- Inherited from table parceltime:  xcoord numeric(31,15),
-- Inherited from table parceltime:  oldycoord numeric(31,15),
-- Inherited from table parceltime:  the_geom geometry,
-- Inherited from table parceltime:  the_point geometry,
-- Inherited from table parceltime:  entrytime timestamp without time zone NOT NULL DEFAULT now(),
-- Inherited from table parceltime:  pid_year smallint NOT NULL,
-- Inherited from table parceltime:  neighborhood character varying(50),
-- Inherited from table parceltime:  wpd character varying(20),
-- Inherited from table parceltime:  old_geom geometry,
-- Inherited from table parceltime:  councildist character varying(15),
-- Inherited from table parceltime:  ycoord numeric(31,15),
-- Inherited from table parceltime:  manualupdate boolean DEFAULT false,
-- Inherited from table parceltime:  st_num_longer character varying(30),
-- Inherited from table parceltime:  nstnum_start integer,
-- Inherited from table parceltime:  nstnum_end integer,
-- Inherited from table parceltime:  censustract character(7),
-- Inherited from table parceltime:  zone character varying(70),
-- Inherited from table parceltime:  zipcode9 character varying(10),
-- Inherited from table parceltime:  geom_merc geometry,
  CONSTRAINT pid_fy_pk PRIMARY KEY (pid, pid_year),
  CONSTRAINT enforce_dims_old_geom CHECK (ndims(old_geom) = 2),
  CONSTRAINT enforce_dims_the_point CHECK (st_ndims(the_point) = 2),
  CONSTRAINT enforce_geotype_geom_merc CHECK (geometrytype(geom_merc) = 'MULTIPOLYGON'::text OR geom_merc IS NULL),
  CONSTRAINT enforce_geotype_old_geom CHECK (geometrytype(old_geom) = 'MULTIPOLYGON'::text OR old_geom IS NULL),
  CONSTRAINT enforce_geotype_the_point CHECK (geometrytype(the_point) = 'POINT'::text OR the_point IS NULL),
  CONSTRAINT enforce_srid_geom_merc CHECK (st_srid(geom_merc) = 900913),
  CONSTRAINT enforce_srid_old_geom CHECK (srid(old_geom) = 2249),
  CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 2249),
  CONSTRAINT enforce_srid_the_point CHECK (st_srid(the_point) = 2249),
  CONSTRAINT pid_year_cc CHECK (pid_year = 2005::smallint)
)
INHERITS (assessing.parceltime)
WITH (
  OIDS=TRUE
);

comment:6 by robe, 12 years ago

Okay i think it might be there inherited table tripping up the script because I found another table with no inheritance and non-standard named geometry columns and the constraints for those were correctly renamed.

comment:7 by robe, 12 years ago

Resolution:	→ fixed
Status:	reopened → closed

I'm going to close this out. Maybe it was a bug with pg 9.2beta4 pg_restore loading into 9.1 database. I just reloaded my data with 9.2RC1 on my new 9.2 instance and now this looks right:

CREATE TABLE assessing.parceltime_2005
(
-- Inherited from table assessing.parceltime:  pid character(10) NOT NULL,
-- Inherited from table assessing.parceltime:  cm_id character(10),
-- Inherited from table assessing.parceltime:  st_num character varying(10),
-- Inherited from table assessing.parceltime:  st_name character varying(75),
-- Inherited from table assessing.parceltime:  name_suf character varying(20),
-- Inherited from table assessing.parceltime:  unit_num character varying(10),
-- Inherited from table assessing.parceltime:  zipcode character varying(10),
-- Inherited from table assessing.parceltime:  ptype integer,
-- Inherited from table assessing.parceltime:  lu character varying(5),
-- Inherited from table assessing.parceltime:  exmpt_code character varying(20),
-- Inherited from table assessing.parceltime:  ownocc character varying(1),
-- Inherited from table assessing.parceltime:  owner character varying(150),
-- Inherited from table assessing.parceltime:  address character varying(150),
-- Inherited from table assessing.parceltime:  city_state character varying(75),
-- Inherited from table assessing.parceltime:  mail_zip character varying(10),
-- Inherited from table assessing.parceltime:  landval bigint,
-- Inherited from table assessing.parceltime:  bldgval bigint,
-- Inherited from table assessing.parceltime:  totalval bigint,
-- Inherited from table assessing.parceltime:  gross_tax numeric(31,15),
-- Inherited from table assessing.parceltime:  legal_area bigint,
-- Inherited from table assessing.parceltime:  year_built integer,
-- Inherited from table assessing.parceltime:  year_remod integer,
-- Inherited from table assessing.parceltime:  gross_area bigint,
-- Inherited from table assessing.parceltime:  area bigint,
-- Inherited from table assessing.parceltime:  num_floors numeric(31,15),
-- Inherited from table assessing.parceltime:  class character varying(15),
-- Inherited from table assessing.parceltime:  r_bldg_stl character varying(50),
-- Inherited from table assessing.parceltime:  r_roof_typ character varying(50),
-- Inherited from table assessing.parceltime:  r_ext_fin character varying(100),
-- Inherited from table assessing.parceltime:  total__rms integer,
-- Inherited from table assessing.parceltime:  r_bdrms integer,
-- Inherited from table assessing.parceltime:  r_full_bth integer,
-- Inherited from table assessing.parceltime:  r_half_bth integer,
-- Inherited from table assessing.parceltime:  r_kitch integer,
-- Inherited from table assessing.parceltime:  r_heat_typ character varying(1),
-- Inherited from table assessing.parceltime:  r_ac character varying(1),
-- Inherited from table assessing.parceltime:  r_fplace integer,
-- Inherited from table assessing.parceltime:  s_num_bldg integer,
-- Inherited from table assessing.parceltime:  s_bldg_sty character varying(5),
-- Inherited from table assessing.parceltime:  s_unit_res integer,
-- Inherited from table assessing.parceltime:  s_unit_com integer,
-- Inherited from table assessing.parceltime:  s_unit_rc integer,
-- Inherited from table assessing.parceltime:  s_ext_fin character varying(2),
-- Inherited from table assessing.parceltime:  u_base_flr integer,
-- Inherited from table assessing.parceltime:  u_num_prk integer,
-- Inherited from table assessing.parceltime:  u_corner character varying(1),
-- Inherited from table assessing.parceltime:  u_orient character varying(2),
-- Inherited from table assessing.parceltime:  u_num_rms integer,
-- Inherited from table assessing.parceltime:  u_num_beds integer,
-- Inherited from table assessing.parceltime:  u_bths integer,
-- Inherited from table assessing.parceltime:  u_half_bth integer,
-- Inherited from table assessing.parceltime:  u_kitch character varying(2),
-- Inherited from table assessing.parceltime:  u_heat character varying(2),
-- Inherited from table assessing.parceltime:  u_ac character varying(1),
-- Inherited from table assessing.parceltime:  u_fplaces integer,
-- Inherited from table assessing.parceltime:  xcoord numeric(31,15),
-- Inherited from table assessing.parceltime:  oldycoord numeric(31,15),
-- Inherited from table assessing.parceltime:  the_geom geometry,
-- Inherited from table assessing.parceltime:  the_point geometry,
-- Inherited from table assessing.parceltime:  entrytime timestamp without time zone NOT NULL DEFAULT now(),
-- Inherited from table assessing.parceltime:  pid_year smallint NOT NULL,
-- Inherited from table assessing.parceltime:  neighborhood character varying(50),
-- Inherited from table assessing.parceltime:  wpd character varying(20),
-- Inherited from table assessing.parceltime:  old_geom geometry,
-- Inherited from table assessing.parceltime:  councildist character varying(15),
-- Inherited from table assessing.parceltime:  ycoord numeric(31,15),
-- Inherited from table assessing.parceltime:  manualupdate boolean DEFAULT false,
-- Inherited from table :  st_num_longer character varying(30),
-- Inherited from table :  nstnum_start integer,
-- Inherited from table :  nstnum_end integer,
-- Inherited from table :  censustract character(7),
-- Inherited from table :  zone character varying(70),
-- Inherited from table :  zipcode9 character varying(10),
-- Inherited from table :  geom_merc geometry,
  CONSTRAINT enforce_dims_old_geom CHECK (st_ndims(old_geom) = 2),
  CONSTRAINT enforce_dims_the_point CHECK (st_ndims(the_point) = 2),
  CONSTRAINT enforce_geotype_geom_merc CHECK (geometrytype(geom_merc) = 'MULTIPOLYGON'::text OR geom_merc IS NULL),
  CONSTRAINT enforce_geotype_old_geom CHECK (geometrytype(old_geom) = 'MULTIPOLYGON'::text OR old_geom IS NULL),
  CONSTRAINT enforce_geotype_the_point CHECK (geometrytype(the_point) = 'POINT'::text OR the_point IS NULL),
  CONSTRAINT enforce_srid_geom_merc CHECK (st_srid(geom_merc) = 900913),
  CONSTRAINT enforce_srid_old_geom CHECK (st_srid(old_geom) = 2249),
  CONSTRAINT enforce_srid_the_geom CHECK (st_srid(the_geom) = 2249),
  CONSTRAINT enforce_srid_the_point CHECK (st_srid(the_point) = 2249),
  CONSTRAINT pid_year_cc CHECK (pid_year = 2005::smallint)
)
INHERITS (assessing.parceltime)
WITH (
  OIDS=TRUE
);

and I verified I didn't fix it in the old database yet. So I have no idea what the issue was before.

Note: See TracTickets for help on using tickets.

Download in other formats: