BUG #15530: unable restore index on function from earthdistance contrib
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t68808psql -h localhost -U postgresBuilt from patchset v2 (message #2), July 27, 2026 at 07:47 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t68808_2 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t68808_2 && git checkout t68808_2Patchset v2 (message #2) is on t68808_2
The following bug has been logged on the website:
Bug reference: 15530
Logged by: Sergei Kornilov
Email address: sk@zsrv.org
PostgreSQL version: 11.1
Operating system: Linux Debian
Description:
Hello
We have some immutable language SQL functions in earthdistance contrib
module. So we can build indexes on this functions. But it is unable to
restore such indexes from dump due search_path restrictions in pg_dump.
Simple reproducer is:
SELECT pg_catalog.set_config('search_path', '', false);
CREATE EXTENSION IF NOT EXISTS cube WITH SCHEMA public;
CREATE EXTENSION IF NOT EXISTS earthdistance WITH SCHEMA public;
CREATE TABLE public.points (
id integer NOT NULL,
name character varying(255) NOT NULL,
lng double precision NOT NULL,
lat double precision NOT NULL
);
CREATE INDEX points_coordinates ON public.points USING gist
(public.ll_to_earth(lat, lng));
With error on last line:
ERROR: type "earth" does not exist
LINE 1: ...ians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth
^
QUERY: SELECT
cube(cube(cube(earth()*cos(radians($1))*cos(radians($2))),earth()*cos(radians($1))*sin(radians($2))),earth()*sin(radians($1)))::earth
CONTEXT: SQL function "ll_to_earth" during inlining
Seems to be broken in all supported versions. I checked on fresh HEAD, 11.1
and 10.6.
How can we fix this? Extension is marked as relocatable
regards, Sergei
Hello
I think we can fix this issue by marking SQL language functions "set
search_path from current". Both "alter extension" and "create extension"
provided correct search_path and we can save it by native "set from
current" feature. Please see attached patch.
regards, Sergei
"Sergei" == Sergei Kornilov <sk@zsrv.org> writes:
Sergei> Hello
Sergei> I think we can fix this issue by marking SQL language functions
Sergei> "set search_path from current".
Unfortunately using SET on sql-language functions prevents inlining.
--
Andrew (irc:RhodiumToad)
Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
"Sergei" == Sergei Kornilov <sk@zsrv.org> writes:
Sergei> I think we can fix this issue by marking SQL language functions
Sergei> "set search_path from current".
Unfortunately using SET on sql-language functions prevents inlining.
Yeah. Another problem is that SET FROM CURRENT will freeze the
search_path at the current setting, which means it'll likely
fail again if either extension in question gets relocated.
Even if this were a perfect solution, it only fixes things for
earthdistance, whereas the same problem exists for any extension
containing SQL or PL functions. So I wish we had a more general
answer ... not that I know what that should look like.
regards, tom lane