#5687 closed defect (fixed)
PostgreSQL 17 regression failures with spgist indexes
Reported by: | robe | Owned by: | robe |
---|---|---|---|
Priority: | blocker | Milestone: | PostGIS 3.3.7 |
Component: | postgis | Version: | 3.4.x |
Keywords: | Cc: |
Description
We've got some PG17 regression failures coming, mostly around our index check tests
15:37:02 regress/core/regress_spgist_index_2d .. failed (diff expected obtained: /var/lib/jenkins/workspace/postgis/tmp/3_5_pg17w64/test_126_diff) 15:37:03 ----------------------------------------------------------------------------- 15:37:03 --- ./regress/core/regress_spgist_index_2d_expected 2023-09-12 16:48:00.450733931 +0000 15:37:03 +++ /var/lib/jenkins/workspace/postgis/tmp/3_5_pg17w64/test_126_out 2024-03-05 20:37:03.604434128 +0000 15:37:03 @@ -1,12 +1,13 @@ 15:37:03 -<< |3880|Seq Scan|3880|Index Scan 15:37:03 -&< |21321|Seq Scan|21321|Index Scan 15:37:03 -&& |31766|Seq Scan|31766|Index Scan 15:37:03 -&> |21321|Seq Scan|21321|Index Scan 15:37:03 ->> |3880|Seq Scan|3880|Index Scan 15:37:03 -~= |206|Seq Scan|206|Index Scan 15:37:03 -~ |7568|Seq Scan|7568|Index Scan 15:37:03 -@ |7568|Seq Scan|7568|Index Scan 15:37:03 -&<||21321|Seq Scan|21321|Index Scan 15:37:03 -<<||3661|Seq Scan|3661|Index Scan 15:37:03 -|>>|3661|Seq Scan|3661|Index Scan 15:37:03 -|&>|21321|Seq Scan|21321|Index Scan 15:37:03 +ERROR: Unable to determine 'postgis' install schema 15:37:03 +<< |3880|Seq Scan|3880|Seq Scan 15:37:03 +&< |21321|Seq Scan|21321|Seq Scan 15:37:03 +&& |31766|Seq Scan|31766|Seq Scan 15:37:03 +&> |21321|Seq Scan|21321|Seq Scan 15:37:03 +>> |3880|Seq Scan|3880|Seq Scan 15:37:03 +~= |206|Seq Scan|206|Seq Scan 15:37:03 +~ |7568|Seq Scan|7568|Seq Scan 15:37:03 +@ |7568|Seq Scan|7568|Seq Scan 15:37:03 +&<||21321|Seq Scan|21321|Seq Scan 15:37:03 +<<||3661|Seq Scan|3661|Seq Scan 15:37:03 +|>>|3661|Seq Scan|3661|Seq Scan 15:37:03 +|&>|21321|Seq Scan|21321|Seq Scan 15:37:03 ----------------------------------------------------------------------------- 15:37:03 regress/core/regress_spgist_index_3d .. failed (diff expected obtained: /var/lib/jenkins/workspace/postgis/tmp/3_5_pg17w64/test_127_diff) 15:37:05 ----------------------------------------------------------------------------- 15:37:05 --- ./regress/core/regress_spgist_index_3d_expected 2023-09-12 16:48:00.454733920 +0000 15:37:05 +++ /var/lib/jenkins/workspace/postgis/tmp/3_5_pg17w64/test_127_out 2024-03-05 20:37:05.296429133 +0000 15:37:05 @@ -1,4 +1,5 @@ 15:37:05 -&/&|26619|Seq Scan|26619|Index Scan 15:37:05 -@>>|4677|Seq Scan|4677|Index Scan 15:37:05 -<<@|4677|Seq Scan|4677|Index Scan 15:37:05 -~==|199|Seq Scan|199|Index Scan 15:37:05 +ERROR: Unable to determine 'postgis' install schema 15:37:05 +&/&|26619|Seq Scan|26619|Seq Scan 15:37:05 +@>>|4677|Seq Scan|4677|Seq Scan 15:37:05 +<<@|4677|Seq Scan|4677|Seq Scan 15:37:05 +~==|199|Seq Scan|199|Seq Scan 15:37:05 ----------------------------------------------------------------------------- 15:37:05 regress/core/regress_spgist_index_nd .. failed (diff expected obtained: /var/lib/jenkins/workspace/postgis/tmp/3_5_pg17w64/test_128_diff) 15:37:06 ----------------------------------------------------------------------------- 15:37:06 --- ./regress/core/regress_spgist_index_nd_expected 2023-09-12 16:48:00.454733920 +0000 15:37:06 +++ /var/lib/jenkins/workspace/postgis/tmp/3_5_pg17w64/test_128_out 2024-03-05 20:37:06.320426110 +0000 15:37:06 @@ -1,4 +1,5 @@ 15:37:06 -&&&|180502|Seq Scan|180502|Index Scan 15:37:06 -~~ |39682|Seq Scan|39682|Index Scan 15:37:06 -@@ |39682|Seq Scan|39682|Index Scan 15:37:06 -~~=|480|Seq Scan|480|Index Scan 15:37:06 +ERROR: Unable to determine 'postgis' install schema 15:37:06 +&&&|180502|Seq Scan|180502|Seq Scan 15:37:06 +~~ |39682|Seq Scan|39682|Seq Scan 15:37:06 +@@ |39682|Seq Scan|39682|Seq Scan 15:37:06 +~~=|480|Seq Scan|480|Seq Scan 15:37:06 -----------------------------------------------------------------------------
This has been happening possibly since 2024-03-01, but hard to say cause master branch has been failing since around then too because of topology issues. The first real specific PG17 regress failure I see like this is 2024-03-05. and prior to 2024-03-01 we have greens everywhere.
Change History (14)
comment:1 by , 7 months ago
comment:2 by , 7 months ago
Owner: | changed from | to
---|
comment:4 by , 7 months ago
Resolution: | fixed |
---|---|
Status: | closed → reopened |
comment:5 by , 7 months ago
I'm having trouble building PG17 under mingw and reverted 0a5d454 as it caused other issues and is not the culprit since the regress_index and regress_nd_index which use the same function are working fine.
Some observations in the gist_index code, there is no logic to try to find location of postgis extension, yet I see this code in spgist. I'm assuming that is where it's failing - https://git.osgeo.org/gitea/postgis/postgis/src/branch/master/postgis/gserialized_spgist_2d.c#L295
I see similar in the spgist_3d.
The initialize cache eventually gets to postgisConstants which then does a search for postgis extension schema https://git.osgeo.org/gitea/postgis/postgis/src/branch/master/libpgcommon/lwgeom_pg.c#L69
Not sure why we have a config function for these and it's not needed for the gist one.
comment:6 by , 7 months ago
Okay just occurred to me that for our regression tests, we are normally not testing with CREATE extension but just with the scripts.
So it's not failing on the get_extension_schema but instead on
https://git.osgeo.org/gitea/postgis/postgis/src/branch/master/libpgcommon/lwgeom_pg.c#L135
The postgis_get_full_version_schema() call.
Though nothing I can see in those areas changed in PG17
comment:7 by , 7 months ago
Okay I think I got the issue now what's wrong.
The postgis_get_full_version_schema() function, tries to find the oid of the postgis_full_version() function and it uses, postgresql's FuncnameGetCandidates to try to find that schema. However FuncnameGetCandidates function only searches the schemas in the search_path for said function. So since the code wiped out public (the most likely location of PostGIS install), it can't find the schema anymore.
comment:11 by , 7 months ago
Milestone: | PostGIS 3.5.0 → PostGIS 3.4.3 |
---|
reopening until I backport to at least 3.4.3. Doesn't look like this change has been pushed to other stable branches as I would have expected, but we probably should have it for at least 3.4.3.
I assume there is no harm in pushing to all stable branches since it only affects installs not using extension.
comment:13 by , 3 months ago
Milestone: | PostGIS 3.4.3 → PostGIS 3.3.7 |
---|
Okay I think I pinpointed. Seems ot have started going south after this patch
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=2af07e2f749a9208ca1ed84fa1d8fe0e75833288
Which sounds like something they will want to backport, so we might have to fix up other branches, before this gets applied to supported PostgreSQL versions