Foreign database request fails when custom postgres extensions are used

Question

Let's say we have 2 databases on 2 different servers:

A on https://A.com/db
B on https://B.com/db

On database A, a "city" table is created, this table uses the "earthdistance" extension:

CREATE EXTENSION "uuid-ossp"; CREATE EXTENSION "cube"; -- required by earthdistance CREATE EXTENSION "earthdistance"; CREATE TABLE "city" ( "id" UUID PRIMARY KEY DEFAULT uuid_generate_v1mc(), "name" VARCHAR(254) DEFAULT '', "lat" DOUBLE PRECISION NOT NULL, "lon" DOUBLE PRECISION NOT NULL ); CREATE INDEX "city_geo_idx" ON "city" USING gist(ll_to_earth(lat, lon)); INSERT INTO "city" VALUES(DEFAULT, 'Hong Kong', 22.313031, 114.170623);

On database B, a foreign reference to table "city" table from A is created:

CREATE EXTENSION "uuid-ossp"; CREATE EXTENSION "postgres_fdw"; CREATE SERVER "foreign_a" FOREIGN DATA WRAPPER "postgres_fdw" OPTIONS (host 'https://A.com/db', port '5432', dbname 'a'); CREATE USER MAPPING FOR "postgres" SERVER "foreign_a" OPTIONS (user 'postgres', password 'postgres'); CREATE FOREIGN TABLE "city" ( "id" UUID, "name" VARCHAR(254) DEFAULT '' ) SERVER "foreign_a" OPTIONS (schema_name 'public', table_name 'city');

At this stage, running a SELECT * FROM "city" returns the following error:

[2018-06-25 19:05:17] [42704] ERROR: type "earth" does not exist [2018-06-25 19:05:17] Where: Remote SQL command: SELECT id, name FROM public.city [2018-06-25 19:05:17] SQL function "ll_to_earth" during inlining

Adding the missing extensions on database B does not solve the problem:

CREATE EXTENSION "cube" SCHEMA "public"; CREATE EXTENSION "earthdistance" SCHEMA "public"; SELECT * FROM "city";

Again:

[2018-06-25 19:05:58] [42704] ERROR: type "earth" does not exist [2018-06-25 19:05:58] Where: Remote SQL command: SELECT id, name FROM public.city [2018-06-25 19:05:58] SQL function "ll_to_earth" during inlining

Any help is greatly appreciated!

klin · Accepted Answer · 2018-06-25 20:34:05Z

From the documentation:

In the remote sessions opened by postgres_fdw, the search_path parameter is set to just pg_catalog, so that only built-in objects are visible without schema qualification. (...) this can pose a hazard for functions that are executed on the remote server via triggers or rules on remote tables.

This applies to the function ll_to_earth() referencing to the type earth which cannot be found on the current search path. Unfortunately, postgres_fdw does not give you the opportunity to change remote search_path (it seems to be a weakness of the extension). You can resolve this by installing the extensions cube and earthdistance in pg_catalog.

-- on database A: DROP EXTENSION cube CASCADE; CREATE EXTENSION cube SCHEMA pg_catalog; CREATE EXTENSION earthdistance SCHEMA pg_catalog;

Warning. Many Postgres experts do not recommend installing extensions in pg_catalog. I personally also think that you should not experiment with pg_catalog. On the other hand, installing in the system catalog a proven extension officially distributed with Postgres is not a crime, especially since I do not see an alternative solution here.

Excellent, that solved the issue. Good to know. Thanks klin!
does this still work with postgresql 14? even with the code above the error persists

Collectives™ on Stack Overflow

Foreign database request fails when custom postgres extensions are used

1 Answer 1

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Linked

Related