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
