Skip to content

Instantly share code, notes, and snippets.

@fphilipe
Created Mar 12, 2015
Embed
What would you like to do?
Unqualified function calls break postgres_fdw compatibiliy
dropdb --if-exists postgis_test_1
dropdb --if-exists postgis_test_2
createdb postgis_test_1
createdb postgis_test_2
# Set up DB 1:
cat <<SQL | psql postgis_test_1
CREATE EXTENSION postgis;
CREATE VIEW test_geometry AS
SELECT ST_Distance(
'POINT(0 0)'::geometry,
'POINT(1 1)'::geometry
) AS distance;
CREATE VIEW test_geography1 AS
SELECT ST_Distance(
'POINT(0 0)'::geography,
'POINT(1 1)'::geography
) AS distance;
-- ST_Distance calls _ST_Distance, thus call that directly with same arguments
-- to see if it works:
CREATE VIEW test_geography2 AS
SELECT _ST_Distance(
'POINT(0 0)'::geography,
'POINT(1 1)'::geography,
0::float,
true
) AS distance;
SQL
# Set up DB 2:
cat <<SQL | psql postgis_test_2
CREATE EXTENSION postgres_fdw;
CREATE SERVER test_one FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'postgis_test_1');
CREATE USER MAPPING FOR "$(whoami)" SERVER test_one;
CREATE FOREIGN TABLE test_geometry (distance float) SERVER test_one;
CREATE FOREIGN TABLE test_geography1 (distance float) SERVER test_one;
CREATE FOREIGN TABLE test_geography2 (distance float) SERVER test_one;
SQL
# Query the foreign tables in DB 2:
# This works as expected:
echo 'SELECT * FROM test_geometry;' | psql postgis_test_2
# This fails because of unqualified function call to _ST_Distance inside
# ST_Distance(geography, geography).
echo 'SELECT * FROM test_geography1;' | psql postgis_test_2
# ERROR: function _st_distance(public.geography, public.geography, numeric, boolean) does not exist
# HINT: No function matches the given name and argument types. You might need to add explicit type casts.
# CONTEXT: SQL function "st_distance" during startup
# This fails because spatial_ref_sys is not found since public is not in the
# search_path when using postgres_fdw.
echo 'SELECT * FROM test_geography2;' | psql postgis_test_2
# ERROR: relation "spatial_ref_sys" does not exist
# Altering the search_path of function ST_Distance to include public fixes the
# problem:
echo 'ALTER FUNCTION ST_Distance(geography, geography) SET search_path TO pg_catalog, public;' | psql postgis_test_1
# This works now:
echo 'SELECT * FROM test_geography1;' | psql postgis_test_2
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment