Thread: 9.6.7 -> 9.6.8 analyze worker behaviour changed?

9.6.7 -> 9.6.8 analyze worker behaviour changed?

From
Marius Vaičiulis
Date:

Hi,

 

Updated our server from 9.6.7 to 9.6.8 (PostgreSQL 9.6.8 on x86_64-pc-linux-gnu (Debian 9.6.8-1.pgdg80+1), compiled by gcc (Debian 4.9.2-10+deb8u1) 4.9.2, 64-bit), and not really funny messages started to appear in the logs:

 

2018-03-08 12:59:06 EET     ERROR:  relation "spatial_ref_sys" does not exist at character 23

2018-03-08 12:59:06 EET     QUERY:  SELECT proj4text FROM spatial_ref_sys WHERE srid = 4326 LIMIT 1

2018-03-08 12:59:06 EET     CONTEXT:  automatic analyze of table "gpt_v2.customer101.t_customer"

 

That table (not the only one) uses the functional indexes, one of which uses postgis functions, so the failing call comes from within postgis function written in C. No changes were noticed in selects, updates, no errors except that. I did some kind of workaround to make that problem go away by doing ALTER FUNCTION … SET SEARCH_PATH TO public; for every postgis function used in the indexes. public is where postgis extension is located (from long time ago).

 

Not sure what to do next, is this a bug, is my realization considered to be a bug, or workaround is a bug? Maybe some configuration parameter was (were) introduced, that I have missed?

 

Thanks for any input.

Re: 9.6.7 -> 9.6.8 analyze worker behaviour changed?

From
David Rowley
Date:
On 9 March 2018 at 00:45, Marius Vaičiulis <marius@vaiciulis.com> wrote:
> 2018-03-08 12:59:06 EET     ERROR:  relation "spatial_ref_sys" does not
> exist at character 23
>
> 2018-03-08 12:59:06 EET     QUERY:  SELECT proj4text FROM spatial_ref_sys
> WHERE srid = 4326 LIMIT 1
>
> 2018-03-08 12:59:06 EET     CONTEXT:  automatic analyze of table
> "gpt_v2.customer101.t_customer"
>
> That table (not the only one) uses the functional indexes, one of which uses
> postgis functions, so the failing call comes from within postgis function
> written in C. No changes were noticed in selects, updates, no errors except
> that. I did some kind of workaround to make that problem go away by doing
> ALTER FUNCTION … SET SEARCH_PATH TO public; for every postgis function used
> in the indexes. public is where postgis extension is located (from long time
> ago).
>
> Not sure what to do next, is this a bug, is my realization considered to be
> a bug, or workaround is a bug? Maybe some configuration parameter was (were)
> introduced, that I have missed?

Most likely due to [1].

analyze gathers statistics on expression indexes, so this is executing
your function and the change made in [1] means the search_path is not
what it used to be in 9.6.7.

It does seem pretty questionable that there's a functional index on a
function which is executing a query to the database. If the result of
that query change it could render the index corrupt. Such a function
should most likely be marked as VOLATILE and therefore not indexable.
I guess PostGIS assume this table must be completely static for them
to think they'd get away with doing that.

[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e170b8c8c62064a16837c9838ef7a168fa9c9506

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services