Обсуждение: 9.6.7 -> 9.6.8 analyze worker behaviour changed?

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