AddGeometryColumn, AddRasterColumn, AddOverview.. relies on table being in search_path for non-schema calls
|Reported by:||robe||Owned by:||robe|
Description (last modified by )
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:2 by , 8 years ago
|Summary:||AddGeometryColumn relies on table being in search_path → AddGeometryColumn, AddRasterColumn, AddOverview.. relies on table being in search_path for non-schema calls|