Обсуждение: BUG #16534: autovacuum_autoanalyze not working on latitude(earth) expression

The following bug has been logged on the website:

Bug reference:      16534
Logged by:          Martin Winkel
Email address:      martin@tiogatours.nl
PostgreSQL version: 12.3
Operating system:   Windows 10 x64 / Ubuntun 20.04 x64
Description:

I have indexes on on the latitude() and longitude() values of an
earth-column. I can analyze these tables, but autoanalyze fails. Because
autoanalyze fails, it will keep trying to analyze these tables.

I created a test-script. After this script, check the postgresql log, you
will find the error attachted below.

TEST SCRIPT:

CREATE EXTENSION cube;
CREATE EXTENSION earthdistance;
    
CREATE TABLE earthdisttest
(
    id serial NOT NULL,
    gps earth NOT NULL,
    PRIMARY KEY (id)
);
    
CREATE INDEX idx_earthtest
    ON earthdisttest USING btree
    (latitude(gps) NULLS LAST);
    
    
ALTER TABLE earthdisttest SET (autovacuum_vacuum_scale_factor = 0.0);
ALTER TABLE earthdisttest SET (autovacuum_vacuum_threshold = 3);
ALTER TABLE earthdisttest SET (autovacuum_analyze_scale_factor = 0.0);
ALTER TABLE earthdisttest SET (autovacuum_analyze_threshold = 3);

INSERT INTO earthdisttest (gps) VALUES (ll_to_earth(10,30)),
(ll_to_earth(1,59)), (ll_to_earth(43,12)), (ll_to_earth(-10,23));


ERROR

2020-07-10 15:59:17.464 CEST [39988] ERROR:  function
cube_ll_coord(public.earth, integer) does not exist at character 18
2020-07-10 15:59:17.464 CEST [39988] HINT:  No function matches the given
name and argument types. You might need to add explicit type casts.
2020-07-10 15:59:17.464 CEST [39988] QUERY:  SELECT CASE WHEN
cube_ll_coord($1, 3)/earth() < -1 THEN -90::float8 WHEN cube_ll_coord($1,
3)/earth() > 1 THEN 90::float8 ELSE degrees(asin(cube_ll_coord($1,
3)/earth())) END
2020-07-10 15:59:17.464 CEST [39988] CONTEXT:  SQL function "latitude"
during inlining
    automatic analyze of table "test.public.earthdisttest"

PG Bug reporting form <noreply@postgresql.org> writes:
> I have indexes on on the latitude() and longitude() values of an
> earth-column. I can analyze these tables, but autoanalyze fails. Because
> autoanalyze fails, it will keep trying to analyze these tables.

Yeah, the earthdistance module is not really up to production standards.
It fails to worry about search path effects, so its references to cube
functions will fail if cube isn't in the search path, which it probably
won't be during autovacuum.

Possibly at some point somebody will care enough to patch the module
to attach "SET search_path" clauses to all the SQL functions.  But
generally, people who want this kind of thing in production are probably
going to want PostGIS, so it's not surprising that earthdistance is
an unloved backwater :-(

            regards, tom lane

Hello

> Possibly at some point somebody will care enough to patch the module
> to attach "SET search_path" clauses to all the SQL functions.

I tried. But such idea was rejected because will prevent function inlining, confuses relocated extensions, and fix only
thisextension.
 
https://www.postgresql.org/message-id/flat/15530-3dc5f66205393dc0%40postgresql.org

regards, Sergei

I don't fully understand the matter, but how I read it, it is a
problem between extensions calling each other's functions? How is this
only an issue when the background workers are autovacuuming? When I
manually analyze the table, no errors whatsoever. Is this because of
the search path which is set when I execute the query from pgadmin?

Regards, Martin Winkel

________________________________
Van: Sergei Kornilov <sk@zsrv.org>
verzonden: vrijdag 10 juli 2020 21:58
Aan: Tom Lane; martin@tiogatours.nl
Cc: pgsql-bugs@lists.postgresql.org
Onderwerp: Re: BUG #16534: autovacuum_autoanalyze not working on
latitude(earth) expression


Hello

> Possibly at some point somebody will care enough to patch the module
> to attach "SET search_path" clauses to all the SQL functions.

I tried. But such idea was rejected because will prevent function
inlining, confuses relocated extensions, and fix only this extension.
https://www.postgresql.org/message-id/flat/15530-3dc5f66205393dc0%40postgresql.org

regards, Sergei