[postgis-tickets] [PostGIS] #3080: PostgreSQL 9.5 regress failure on st_reclass

[PostGIS]

#3080: PostgreSQL 9.5 regress failure on st_reclass
--------------------+-------------------------------------------------------
 Reporter:  robe    |       Owner:  dustymugs    
     Type:  defect  |      Status:  new          
 Priority:  high    |   Milestone:  PostGIS 2.2.0
Component:  raster  |     Version:  trunk        
 Keywords:          |  
--------------------+-------------------------------------------------------

Comment(by robe):

 Okay lots of discussion going on in this thread on pgsql bugs.  It sounds
 like this was an intentional change and one they are hesitant to revert.

 Change here:
 http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f4abd0241de20d5d6a79b84992b9e88603d44134

 That said we may need to change the definition of a lot of functions (that
 we marked as IMMUTABLE) to prevent serious degradation of performance.

 Key comment from Tom:

 ----

 {{{
 "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
 > This doesn't seem like a solution...if the flattened version of an all
 > constants invocation cannot be run only once where it could if it was
 not
 > flattened I would say the we've introduced a likely (and actual)
 > performance regression that punishes current valid and idiomatic code.
 I
 > haven't gone back and devised the entire reasoning for, and potential
 > benefit of, the flattening but both this and likely functions returning
 > composites are being negatively affected by this change.

 Well, it improves some queries and perhaps punishes others, but I should
 think the overall effect would generally be a win.  Even in the case given
 here, it's far from clear that allowing flattening is a performance loss;
 the end result would have been a query containing only constants at run
 time, which in most scenarios would be a Good Thing.

 As for claiming that this is broken and should be reverted: nyet.  In the
 first place, there is certainly no PG documentation anywhere that promises
 single evaluation of a function written in the manner shown here.  We do,
 on the other hand, say that OFFSET 0 creates an optimization fence; so
 I see nothing wrong with my recommendation.  In the second place, this was
 a HEAD-only change, and we certainly do not promise than the planner never
 changes behavior in major version updates.

                         regards, tom lane


 }}}

 ----


 Only fix thus far to prevent this from running for each value in select
 (or telling everyone to start using CTE for this kind of thing), is to set
 these functions as VOLATILE.  Even making this STABLE or ramping up the
 cost for my silly example doesn't prevent the notice from repeating
 multiple times.  Although this change was only trapped by raster, I think
 we really need to do performance metrics and really study our code base to
 determine the full impact of this change.  I suspect it will make some
 people's production systems very slow (raster, geography, topology
 whatever)

-- 
Ticket URL: <http://trac.osgeo.org/postgis/ticket/3080#comment:5>
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.