PostGIS 2.5 not regressing cleanly in PostgreSQL 9.4

[robe]

Debbie's 9.4 runs are failing on PostGIS 2.5.

This seems to have started after r17528 (that's the last successful run) on Jun 13th. It's hard to tell which commit after caused the issue since it caused other problems with other PostgreSQL versions that were cleaned up by later commits.

failures are below

 ctors .. ok 
 curvetoline .. ok 
 dump .. ok 
 dumppoints .. ok 
 empty .. ok 
 estimatedextent .. failed (diff expected obtained: /var/lib/jenkins/workspace/postgis/tmp/2_5_pg9.4w64/test_41_diff)
-----------------------------------------------------------------------------
--- estimatedextent_expected	2019-06-28 17:30:18.282838964 +0000
+++ /var/lib/jenkins/workspace/postgis/tmp/2_5_pg9.4w64/test_41_out	2019-07-27 17:30:56.923315007 +0000
@@ -6,32 +6,17 @@
 WARNING:  stats for "t.g" do not exist
 #877.2.deprecated|
 WARNING:  stats for "t.g" do not exist
-WARNING:  stats for "t.g" do not exist
-WARNING:  stats for "t.g" do not exist
-WARNING:  stats for "t.g" do not exist
 #877.3||||
 #877.4|-10.15000|20.15000|-50.40000|30.40000
 #877.5|-10.15000|20.15000|-50.40000|30.40000
 WARNING:  stats for "c1.g" do not exist
-WARNING:  stats for "c1.g" do not exist
-WARNING:  stats for "c1.g" do not exist
-WARNING:  stats for "c1.g" do not exist
 #3391.1||||
 WARNING:  stats for "c2.g" do not exist
-WARNING:  stats for "c2.g" do not exist
-WARNING:  stats for "c2.g" do not exist
-WARNING:  stats for "c2.g" do not exist
 #3391.2||||
 WARNING:  stats for "p.g" do not exist
-WARNING:  stats for "p.g" do not exist
-WARNING:  stats for "p.g" do not exist
-WARNING:  stats for "p.g" do not exist
 #3391.3||||
 #3391.4|0.00|1.00|0.00|1.00
 WARNING:  stats for "c2.g" do not exist
-WARNING:  stats for "c2.g" do not exist
-WARNING:  stats for "c2.g" do not exist
-WARNING:  stats for "c2.g" do not exist
 #3391.5||||
 #3391.6|0.00|1.00|0.00|1.00
 #3391.7|0.00|1.00|0.00|1.00
@@ -41,30 +26,15 @@
 #3391.11|-1.00|0.00|-1.00|0.00
 #3391.12|-1.01|2.02|-1.01|2.02
 WARNING:  stats for "p.g" do not exist
-WARNING:  stats for "p.g" do not exist
-WARNING:  stats for "p.g" do not exist
-WARNING:  stats for "p.g" do not exist
 #3391.13||||
 WARNING:  stats for "p.g" do not exist
-WARNING:  stats for "p.g" do not exist
-WARNING:  stats for "p.g" do not exist
-WARNING:  stats for "p.g" do not exist
 #3391.14||||
 WARNING:  stats for "c1.g" do not exist
-WARNING:  stats for "c1.g" do not exist
-WARNING:  stats for "c1.g" do not exist
-WARNING:  stats for "c1.g" do not exist
 #3391.15||||
 WARNING:  stats for "c1.g" do not exist
-WARNING:  stats for "c1.g" do not exist
-WARNING:  stats for "c1.g" do not exist
-WARNING:  stats for "c1.g" do not exist
 #3391.16||||
 #3391.17|0.00|1.00|0.00|1.00
 WARNING:  stats for "p.g" do not exist
-WARNING:  stats for "p.g" do not exist
-WARNING:  stats for "p.g" do not exist
-WARNING:  stats for "p.g" do not exist
 #3391.18||||
 #3391.19|0.00|1.00|0.00|1.00
 #3391.20|0.00|1.00|0.00|1.00
-----------------------------------------------------------------------------
 forcecurve .. ok 
 geography .. ok 
 geometric_median .. ok 
 in_geohash .. ok 
 in_gml .. ok 
 in_kml .. ok 
 in_encodedpolyline .. ok 
 iscollection .. ok 
 legacy .. ok 
 long_xact .. ok 
 lwgeom_regress .. ok 
 measures .. ok 
 minimum_bounding_circle .. ok 
 normalize .. ok 
 operators .. ok 
 orientation .. ok 
 out_geometry .. ok 
 out_geography .. ok 
 polygonize .. ok 
 polyhedralsurface .. ok 
 postgis_type_name .. ok 
 quantize_coordinates .. ok 
 regress .. ok 
 regress_bdpoly .. ok 
 regress_gist_index_nd .. ok 
 regress_index .. ok 
 regress_index_nulls .. ok 
 regress_management .. ok 
 regress_selectivity .. ok 
 regress_lrs .. ok 
 regress_ogc .. ok 
 regress_ogc_cover .. ok 
 regress_ogc_prep .. ok 
 regress_proj .. ok 
 relate .. ok 
 remove_repeated_points .. ok 
 removepoint .. ok 
 reverse .. ok 
 setpoint .. ok 
 simplify .. ok 
 simplifyvw .. ok 
 size .. ok 
 snaptogrid .. ok 
 split .. ok 
 sql-mm-serialize .. ok 
 sql-mm-circularstring .. ok 
 sql-mm-compoundcurve .. ok 
 sql-mm-curvepoly .. ok 
 sql-mm-general .. ok 
 sql-mm-multicurve .. ok 
 sql-mm-multisurface .. ok 
 swapordinates .. ok 
 summary .. ok 
 temporal .. ok 
 tickets .. ok 
 twkb .. ok 
 typmod .. ok 
 wkb .. ok 
 wkt .. ok 
 wmsservers .. ok 
 knn .. ok 
 hausdorff .. ok 
 regress_buffer_params .. ok 
 offsetcurve .. ok 
 relatematch .. ok 
 isvaliddetail .. ok 
 sharedpaths .. ok 
 snap .. ok 
 node .. ok 
 unaryunion .. ok 
 clean .. ok 
 relate_bnr .. ok 
 delaunaytriangles .. ok 
 clipbybox2d .. ok 
 subdivide .. ok 
 voronoi .. ok 
 minimum_clearance .. ok 
 oriented_envelope .. ok 
 in_geojson .. ok 
 mvt .. ok 
 geobuf .. ok 
 mvt_jsonb .. ok 
 regress_sfcgal .. ok 
 sfcgal/empty .. ok 
 sfcgal/geography .. ok 
 sfcgal/legacy .. ok 
 sfcgal/measures .. ok 
 sfcgal/regress_ogc_prep .. ok 
 sfcgal/regress_ogc .. ok 
 sfcgal/regress .. ok 
 sfcgal/tickets .. failed (diff expected obtained: /var/lib/jenkins/workspace/postgis/tmp/2_5_pg9.4w64/test_132_diff)
-----------------------------------------------------------------------------
--- sfcgal/tickets_expected	2019-07-02 09:49:06.918850303 +0000
+++ /var/lib/jenkins/workspace/postgis/tmp/2_5_pg9.4w64/test_132_out	2019-07-27 17:31:52.062722324 +0000
@@ -151,9 +151,6 @@
 WARNING:  stats for "t.g" do not exist
 #877.2.deprecated|
 WARNING:  stats for "t.g" do not exist
-WARNING:  stats for "t.g" do not exist
-WARNING:  stats for "t.g" do not exist
-WARNING:  stats for "t.g" do not exist
 #877.3||||
 #877.4|-10.15000|20.15000|-50.40000|30.40000
 #877.5|-10.15000|20.15000|-50.40000|30.40000
-----------------------------------------------------------------------------
 sfcgal/concave_hull .. ok 
 sfcgal/wmsservers .. ok 
 sfcgal/approximatemedialaxis .. ok 
 uninstall .  /var/lib/jenkins/workspace/postgis/branches/2.5/regress/00-regress-install/share/contrib/postgis/uninstall_sfcgal.sql
  /var/lib/jenkins/workspace/postgis/branches/2.5/regress/00-regress-install/share/contrib/postgis/uninstall_postgis.sql
. ok (4336)

Looks like might just be number of warnings output perhaps because of change of number of runs or something.

[pramsey]

We support 9.4 on 2.5?

Anyways, the errors are the ones coming from trying to reconcile the different behaviour of CTEs between 12 and pre-12, since as of 12 we are now seeing the optimizer peeking into the CTE and pulling stuff out, whereas before that didn't happen. I pulled those tests apart and put the CTE stuff into subqueries to try and get equivalent behaviour which seems to have mostly worked. This commit tagged doesn't seem to have anything to do w/ the problem you are reporting here…

[pramsey]

Yuh, I get the same effect, it looks like 9.4 has slightly different behaviour from 9.5 in terms of how it executes things like

select '#877.3',
  round(st_xmin(e.e)::numeric, 5), round(st_xmax(e.e)::numeric, 5),
  round(st_ymin(e.e)::numeric, 5), round(st_ymax(e.e)::numeric, 5)
from ( select ST_EstimatedExtent('t','g') as e ) e;

9.4 manages to execute it, running ST_EstimatedExtent just once, while 9.5/9.6/10/11/12 run it four times (and gets four notices as a result). I'm not sure what we do except excise those tests, we cannot unfortunately use the MATERIALIZE keyword in the older versions to force 12 to have the same behaviour as the older ones.

[pramsey]

In 17658:

Deal with differing behaviour of subquery planning between Pg versions.
References #4464

[pramsey]

In 17659:

Harmonize calls so tests return same results on multiple Pg versions
References #4464