From: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
---|---|
To: | Marius Vaičiulis <marius(at)vaiciulis(dot)com> |
Cc: | PostgreSQL mailing lists <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: 9.6.7 -> 9.6.8 analyze worker behaviour changed? |
Date: | 2018-03-08 14:28:58 |
Message-ID: | CAKJS1f9yzhB=RDUQ4R7sWaV=E811e6Bv-vdNYu30rXawZgdcmw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
On 9 March 2018 at 00:45, Marius Vaičiulis <marius(at)vaiciulis(dot)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.
--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2018-03-08 15:10:34 | BUG #15102: Performance problem when doing join, index are not used |
Previous Message | Marius Vaičiulis | 2018-03-08 11:45:42 | 9.6.7 -> 9.6.8 analyze worker behaviour changed? |