#4464 closed defect (fixed)

PostGIS 2.5 not regressing cleanly in PostgreSQL 9.4

Reported by: robe Owned by: pramsey
Priority: blocker Milestone: PostGIS 2.5.3
Component: postgis Version: 2.5.x
Keywords: Cc:

Description (last modified by 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.

Change History (6)

comment:1 Changed 10 months ago by robe

Description: modified (diff)

comment:2 Changed 10 months ago by 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...

comment:3 Changed 10 months ago by 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.

comment:4 Changed 10 months ago by pramsey

In 17658:

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

comment:5 Changed 10 months ago by pramsey

In 17659:

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

comment:6 Changed 10 months ago by pramsey

Resolution: fixed
Status: newclosed
Note: See TracTickets for help on using tickets.