Opened 4 years ago

Closed 4 years ago

#3495 closed defect (fixed)

AddGeometryColumn, AddRasterColumn, AddOverview.. relies on table being in search_path for non-schema calls

Reported by: robe Owned by: robe
Priority: blocker Milestone: PostGIS 2.2.2
Component: postgis Version: 2.2.x
Keywords: Cc:

Description (last modified by robe)

Okay discovered an isssue with my search_path setting logic. The way our AddGeometryColumn? works. The version that doesn't take schema requires the table with geometry column being added has schema set.

I discovered this issue with

CREATE SCHEMA postgis VERSION "2.3.0dev";
CREATE EXTENSION postgis SCHEMA postgis;
ALTER DATABASE workshop_1
  SET search_path = public, postgis;

-- and then using shp2pgsql to restore
shp2pgsql -I -D -s 26986 data/MBTA_ARC mbta_lines | psql

ALTER TABLE
ERROR:  Table 'mbta_lines' does not occur in the search_path
CONTEXT:  SQL statement "SELECT AddGeometryColumn('',$1,$2,$3,$4,$5,$6,$7)"
PL/pgSQL function addgeometrycolumn(character varying,character varying,character varying,integer,character varying,integer,boolean) line 5 at SQL statement
ERROR:  current transaction is aborted, commands ignored until end of transaction block
invalid command \.
ERROR:  syntax error at or near "GREEN"
LINE 1: GREEN C D 7 4.58386095740e+002 01050000206A69000001000000010...
        ^
ROLLBACK
ERROR:  relation "mbta_lines" does not exist

however this works okay -specifying schema of table

-- and then using shp2pgsql to restore
shp2pgsql -I -D -s 26986 data/MBTA_ARC public.mbta_lines | psql

So I guess we've got to exclude all these from my search_path logic I put in on #3490. Eventually I think we'll need to force everyone to install postgis in same schema and schema qualify at least these ones and relationship ones that rely on users schema to be in search path to work well.

Change History (5)

comment:1 Changed 4 years ago by robe

Description: modified (diff)

comment:2 Changed 4 years ago by robe

Summary: AddGeometryColumn relies on table being in search_pathAddGeometryColumn, AddRasterColumn, AddOverview.. relies on table being in search_path for non-schema calls

comment:3 Changed 4 years ago by robe

(In [14762]) Cleanup addition of search_path for upgrade scripts exclude addgeometry*, addraster*, addoverview* from seach_path function setting. These in calls that don't take schema use the current search_path to find table. References #3495 for 2.3 (trunk)

comment:4 Changed 4 years ago by robe

Owner: changed from pramsey to robe

comment:5 Changed 4 years ago by robe

Resolution: fixed
Status: newclosed

(In [14763]) exclude addgeometry*, addraster*, addoverview* from seach_path function setting. These in calls that don't take schema use the current search_path to find table. Closes #3495 for 2.2

Note: See TracTickets for help on using tickets.