Opened 9 years ago

Closed 8 years ago

#3486 closed defect (fixed)

function st_within - e

Reported by: giau81 Owned by: robe
Priority: medium Milestone: PostGIS 2.3.0
Component: postgis Version: 2.2.x
Keywords: st_within, st_Contains, search_path Cc:

Description

st_within(geometry, geometry)/st_Contains(geometry, geometry) call 'SELECT $1 && $2 AND _ST_Contains($2,$1)' without setting public schema to _ST_Contains function.

Using "set search_path = schema_name", if schema_name is not "public", the functions st_within(geometry, geometry)/st_Contains(geometry, geometry) don't work, because they call schema_name._ST_Contains($2,$1) instead of public._ST_Contains($2,$1).

This problem happens during db restoring processes. We solve this issue modifying the function like this: 'SELECT $1 && $2 AND public._ST_Contains($2,$1)'

Change History (4)

comment:1 by robe, 9 years ago

Summary: function st_withinfunction st_within - e

I feel your pain and its on my radar right now. I would like to get a script in at least for PostGIS 2.2.2 to set search paths.

See this ticket #3076

My solution is to just set the search_path of all functions to what is chosen at create time. Working on a script to do that. At the very least it will be included as a stand-alone, but I really would like to get it in as part of the extension install process so people don't have to remember to

1) create extension 2) run the script 3) then reload their data

If its part of extension then it will just work with just restoring your backup.

comment:2 by robe, 9 years ago

Owner: changed from pramsey to robe

comment:3 by robe, 9 years ago

Milestone: PostGIS 2.2.2PostGIS 2.3.0

Sadly this will not be fixed by #3490 which will fix others.

My plan to put search_path on all functions can't be done on functions that rely on SQL Inlining to to use spatial index. Putting ALTER FUNCTION .. search_path on these destroys the inlining behavior.

comment:4 by robe, 8 years ago

Resolution: fixed
Status: newclosed

In 15041:

schema qualify function and operator calls in geometry and geography functions

Closes #3496
Closes #3494
Closes #3486
Closes #3076

Note: See TracTickets for help on using tickets.