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

4

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.

answered Jun 25, 2018 at 20:34

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

neeh Over a year ago

Excellent, that solved the issue. Good to know. Thanks klin!

brauliobo Over a year ago

does this still work with postgresql 14? even with the code above the error persists

brauliobo Over a year ago

ah it worked after I did on the master instance

Collectives™ on Stack Overflow

Foreign database request fails when custom postgres extensions are used

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related