Creating both topology and tiger extension in same session contend search_path

[strk]

See ​https://github.com/postgis/docker-postgis/issues/288

The problem is that ALTER DATABASE does not change the reset_val in pg_settings, which is used by extensions to fetch the "current" value of search_path:

test_issue288=# create extension postgis_tiger_geocoder;
CREATE EXTENSION
test_issue288=# select setting, boot_val, reset_val from pg_catalog.pg_settings WHERE name = 'search_path';
-[ RECORD 1 ]---------------------
setting   | "$user", public, tiger
boot_val  | "$user", public
reset_val | "$user", public

You can see the setting field is updated, but not the reset_val, which is instead only updated on next reconnection.

[strk]

Maybe we should completely STOP changing search_path and let users deal with that. What do you think Regina ? It might also help spot security issues in functions

[strk]

Thanks to Myon on IRC I now know we could use this:

select setconfig[1] 
from pg_catalog.pg_db_role_setting 
where 
 setdatabase = (select oid from pg_database where datname = current_database() )
 and setrole = 0;

The above query will give the last value set via ALTER DATABASE w/out the need of reconnecting.

[Sandro Santilli <strk@…>]

In 3dea38a/git:

Do not undo search_path changes done by pgis exts in single session

References #5125 in master branch (3.3.0dev)

[strk]

I've implemented the above idea and pushed to master branch. Do you think it should be backported ?

[robe]

sure

[ImreSamu]

I am testing the master branch with my local updated docker

• postgis/postgis:14-master
  • ENV POSTGIS_GIT_HASH 6a6cc54bdf1315da8e8c8401575c179aa8727daf

• with the "old" and the new patched "initdb-postgis.sh"

And I receive this log with CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder;

postgres=# CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;
CREATE EXTENSION
postgres=# CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder;
ERROR:  column "c" does not exist
LINE 10:  SELECT regexp_replace(c, '^search_path=', '')
                                ^
QUERY:  WITH settings AS (
		SELECT unnest(setconfig) config
		FROM pg_catalog.pg_db_role_setting
		WHERE setdatabase = (
			SELECT oid
			FROM pg_catalog.pg_database
			WHERE datname = current_database()
		) and setrole = 0
	)
	SELECT regexp_replace(c, '^search_path=', '')
	FROM settings WHERE c like 'search_path=%'
CONTEXT:  PL/pgSQL function postgis_extension_addtosearchpath(character varying) line 7 at SQL statement
postgres=# 

I have received the same docker-postgis CI/CD error log "Build docker image for 14-master variant default" ( with the new "initdb-postgis.sh" ) :

​https://github.com/postgis/docker-postgis/runs/5883910585?check_suite_focus=true

And replicated with local docker environment: (with the new patched "initdb-postgis.sh")

git clone https://github.com/postgis/docker-postgis.git
cd docker-postgis
make update
make build-14-master
make test-14-master

[robe]

Broke winnie too noted - here - #5126

[Regina Obe <lr@…>]

In cbf5283/git:

Fix search path function. References #5125 and #5126 for PostGIS 3.3.0

[ImreSamu]

confirmed;

The latest cbf5283/git patch fixed the postgis/postgis:14-master test.

Thanks!

[strk]

Sorry for the problem, concerning that CI did not catch this. I guess adding run_test.pl support for tiger could help with CI. Time to backport ?

[strk]

CI support for tiger is staged here: ​https://gitlab.com/postgis/postgis/-/merge_requests/80

[Regina Obe <lr@…>]

In 4df09520/git:

Change signature of AddToSearchPath, reference #5150 for PostGIS 3.2.2
Fix search path function, references #5125 for PostGIS 3.2.2

[Regina Obe <lr@…>]

In b175c26/git:

Numerous fixes to helper functions.
References #5150
References #5125
References #5155 for PostGIS 3.1.6

[robe]

I think this is already fixed.