Opened 7 months ago

Closed 7 months ago

Last modified 3 months ago

#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 robe, 7 months ago

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

comment:2 by robe, 7 months ago

Owner: changed from pramsey to robe

comment:3 by Regina Obe <lr@…>, 7 months ago

Resolution: fixed
Status: newclosed

In 0a5d454/git:

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

comment:4 by robe, 7 months ago

Resolution: fixed
Status: closedreopened

comment:5 by robe, 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.

Last edited 7 months ago by robe (previous) (diff)

comment:6 by robe, 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 robe, 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:8 by Regina Obe <lr@…>, 7 months ago

Resolution: fixed
Status: reopenedclosed

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

comment:9 by Regina Obe <lr@…>, 7 months ago

In f49fa42/git:

Fix boolean casing
Closes #5687 for PostGIS 3.5.0

comment:10 by pramsey, 7 months ago

🎉 !

comment:11 by robe, 7 months ago

Milestone: PostGIS 3.5.0PostGIS 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:12 by Regina Obe <lr@…>, 7 months ago

In 77bc5c2/git:

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

comment:13 by robe, 3 months ago

Milestone: PostGIS 3.4.3PostGIS 3.3.7

comment:14 by Regina Obe <lr@…>, 3 months ago

In f09a1793/git:

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

Note: See TracTickets for help on using tickets.