PostgreSQL 17 regression failures with spgist indexes

[robe]

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.

[robe]

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

Commit 2af07e2f749a9208ca1ed84fa1d8fe0e75833288 by jdavis

Fix search_path to a safe value during maintenance operations.

While executing maintenance operations (ANALYZE, CLUSTER, REFRESH
MATERIALIZED VIEW, REINDEX, or VACUUM), set search_path to
'pg_catalog, pg_temp' to prevent inconsistent behavior.

Functions that are used for functional indexes, in index expressions,
or in materialized views and depend on a different search path must be
declared with CREATE FUNCTION ... SET search_path='...'.

This change was previously committed as 05e1737351, then reverted in
commit 2fcc7ee7af because it was too late in the cycle.

Preparation for the MAINTAIN privilege, which was previously reverted
due to search_path manipulation hazards.

Discussion: https://postgr.es/m/d4ccaf3658cb3c281ec88c851a09733cd9482f22.camel@j-davis.com
Discussion: https://postgr.es/m/E1q7j7Y-000z1H-Hr%40gemulon.postgresql.org
Discussion: https://postgr.es/m/e44327179e5c9015c8dda67351c04da552066017.camel%40j-davis.com
Reviewed-by: Greg Stark, Nathan Bossart, Noah Misch

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

[Regina Obe <lr@…>]

In 0a5d454/git:

See if setting the search path explicitly of our qnodes, fixes #5687

[robe]

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.

[robe]

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

[robe]

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.

[Regina Obe <lr@…>]

In d0193e2/git:

FIX for PostgreSQL 17 regression failure on spgist
For script installs, don't rely on search_path
to determine schema postgis is installed.

Closes #5687 for PostGIS 3.5.0

[Regina Obe <lr@…>]

In f49fa42/git:

Fix boolean casing
Closes #5687 for PostGIS 3.5.0

[pramsey]

🎉 !

[robe]

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.

[Regina Obe <lr@…>]

In 77bc5c2/git:

Revise postgis_get_full_version_schema() to not
rely on search_path
Closes #5687 for PostGIS 3.4.3

[Regina Obe <lr@…>]

In f09a1793/git:

Fix regression failures against PG17
Closes #5687 for PostGIS 3.3.7
Closes #5756 for PostGIS 3.3.7