[BUGS] BUG #14844: Failure/Duplicate key value with ALTER DATABASE setsearch_path

The following bug has been logged on the website:

Bug reference:      14844
Logged by:          Hans Buschmann
Email address:      buschmann@nidsa.net
PostgreSQL version: 10.0
Operating system:   Windows x64
Description:


After pg_dump/pg_restore of individual databases from 9.6.5 to 10.0 I have
to set the search path for each database according to the schemas used (the
searchpath is not restored with pg_restore).

This is done with a cmd like 

ALTER DATABASE cpsdb SET search_path TO public,part1,part2,part3;

In the application I do not handle the search_path, but need access to all
mentioned schemas.

For one database this alter database cmd succeeded, the application works.

For the second database I also entered the command, but the search_path is
not set in the application.

When trying to change or reset the search path the following error occurs
(PG 10.0):

didisdb=# ALTER DATABASE xxxdb SET search_path TO public,xxxo,xxxp;
ERROR:  duplicate key value violates unique constraint
"pg_db_role_setting_databaseid_rol_index"
DETAIL:  Key (setdatabase, setrole)=(19084, 0) already exists.

Even resetting the search_path with 

alter database xxxb set search_path to "$user", public;
ERROR:  duplicate key value violates unique constraint
"pg_db_role_setting_databaseid_rol_index"
DETAIL:  Key (setdatabase, setrole)=(19084, 0) already exists.

produces the same error.
I never experienced duplicate key violations in postgres catalog
relations.

PS: setting the search_path with set in the psql session works, but is not
permanent...

What am I missing here?




--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs