[PostGIS] #5790: Postgis-functions in Materialized views fail in Postgres 17 due to not fully qualified search path

[PostGIS]

#5790: Postgis-functions in Materialized views fail in Postgres 17 due to not
fully qualified search path
----------------------+---------------------------
  Reporter:  maxbo    |      Owner:  robe
      Type:  defect   |     Status:  closed
  Priority:  high     |  Milestone:  PostGIS 3.4.4
 Component:  postgis  |    Version:  3.4.x
Resolution:  fixed    |   Keywords:
----------------------+---------------------------
Comment (by robe):

 @landry,

 I have one additional thought about this, given that the issue sounds
 intermittent at least in psql (always fails pn pyschopg), it just occurred
 to me that ST_EstimatedExtent is very sensitive to vacuum analyze process.
 I would really avoid using it in a materialized view if you can.  My
 thought is maybe depending on the frequency of your updates/delete/inserts
 to this table, it could be all over the place and even at times perhaps
 just return garbage.  I haven't dug deep into the function to see what it
 is doing.

 But based on your error message, I gather it is failing here -
 https://gitea.osgeo.org/postgis/postgis/src/commit/3ec6fdb951aaec20db3fc35ee274752eeb15d07c/postgis/gserialized_estimate.c#L2493

 @pramsey have any thoughts on this why a geometry data type would suddenly
 turn invalid?


 To get a more predictable answer, and perhaps it might even solve your
 issue, try redoing your materialized view as
 below, though you will suffer a performance penalty.  Depending on the
 size of your public.pictures and the number of cpus you have, the
 performance penalty may be acceptable and the behavior would be more
 predictable

 {{{
 CREATE MATERIALIZED VIEW test_grid AS
 SELECT row_number() OVER() AS id,  g.geom
 FROM
    (SELECT ST_SetSRID(ST_Extent(geom), 4326) AS ext FROM public.pictures)
 AS t
    CROSS JOIN LATERAL ST_SquareGrid(
         0.1,
         t.ext
     ) AS g;
 }}}
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5790#comment:13>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.