Opened 8 years ago

Closed 7 years ago

Last modified 7 years ago

#3277 closed defect (fixed)

Raster import fails from different schema with constraint

Reported by: jczaplewski Owned by: Bborie Park
Priority: medium Milestone: PostGIS 2.2.2
Component: raster Version: 2.1.x
Keywords: raster, restore Cc:

Description

When a raster table is not in the schema public and has a raster_constraint_pixel_types constraint, the following error is thrown when importing from pg_dump:

ERROR:  function st_bandmetadata(public.raster, integer[]) does not exist
LINE 1:  SELECT array_agg(pixeltype)::text[] FROM st_bandmetadata($1...
                                                  ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:   SELECT array_agg(pixeltype)::text[] FROM st_bandmetadata($1, ARRAY[]::int[]); 
CONTEXT:  SQL function "_raster_constraint_pixel_types" during inlining

It seems that the issue is that postgres is unable to find the proper spatial functions, and indeed if you alter the line

   SET search_path = rasters, pg_catalog;

to

   SET search_path = public, rasters, pg_catalog;

the import of the dump works as expected. You can reproduce this error with the following example:

CREATE DATABASE test;
CREATE EXTENSION postgis;
CREATE SCHEMA rasters;

CREATE TABLE rasters.my_rasters (
  id serial NOT NULL,
  rast raster,
  CONSTRAINT enforce_pixel_types_rast CHECK ((public._raster_constraint_pixel_types(rast) = '{16BSI}'::text[]))
);

INSERT INTO rasters.my_rasters (rast) (
	SELECT ST_AsRaster(
		ST_GeomFromText(
			'LINESTRING(-108 30, -87 43)'
		, 4326),
		150, 150, '16BSI'
	)
);

pg_dump -C test > ~/Downloads/test.sql && dropdb test && psql < ~/Downloads/test.sql

My system and version information is as follows:

PostgreSQL 9.3.1 on x86_64-apple-darwin12.5.0, compiled by i686-apple-darwin11-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc. build 5658) (LLVM build 2336.11.00), 64-bit

POSTGIS="2.1.0 r11822" GEOS="3.3.8-CAPI-1.7.8" PROJ="Rel. 4.8.0, 6 March 2012" GDAL="GDAL 1.10.0, released 2013/04/24" LIBXML="2.9.1" LIBJSON="UNKNOWN" TOPOLOGY RASTER

Change History (4)

comment:1 by robe, 7 years ago

Milestone: PostGIS 2.1.9PostGIS 2.2.2

refer to #3490

comment:2 by robe, 7 years ago

Keywords: restore added

comment:3 by robe, 7 years ago

Resolution: fixed
Status: newclosed

(In [14752]) Script to set search path for raster and postgis functions To fix database restore issues and materialized views Closes #3490 Closes #3485 Closes #3277 Closes #3012

comment:4 by robe, 7 years ago

Note for 2.2.2 - one would need to run the script explicitly as noted in FAQ - http://postgis.net/docs/manual-2.2/RT_FAQ.html#faq_raster_data_not_restore

For PostGIS 2.3, this will be automatic and not require script running

Note: See TracTickets for help on using tickets.