Re: BUG #14844: Failure/Duplicate key value with ALTER DATABASE set search_path

The issue occured on my development machine (Win 10 x64, seems not relevant).

I switched the whole cluster form 9.6.5 to 10.0.

There is only one PG cluster on the machine, managed as a Windows Service (no 2 different PG versions in parallel).

For the moment, I still use the superuser account.

I have 2 (or more) different databases (let them name db1 and db2) used in 2 totally uncorrelated applications through php.

Under 9.6.5, I dumped each database with 2 separate runs of pg_dump -U postgres -d db1 -Fc -f db1.dmp (respective. db2.dmp).

I shut down the cluster, renamed the old DATA folder to _OLD, renamed the expanded postgres binaries to _OLD and expanded the new 10. binaries.

I created the cluster with

initdb --pgdata=<data_path> -U postgres -A md5 -W --encoding=UTF-8 --data-checksums --lc-messages=C --lc-collate=C

and started the cluster as a service.

By mistake I missed to copy my database defaults (nothing special) to the new DATA_Path, so the first restore took place with the defaults of a fresh installation of 10.0.

I created the first database with:

create database db1 template=template0 encoding 'UTF8' lc_collate='C';

then I restored the first database db1 with pg_restore without issues.

After pg_restore I executed the alter database db1 set search_path xxx,xxx,xxx statement in psql, connected to db1 (in psql) and did an analyze (the regular procedure).

Then I realized my mistake with the .conf files, copied them to DATA_PATH and restarted the cluster throuph services.

Then I created and restored the second database db2 with pg_restore without issues.
As with db1 I did the alter database set search_path statement and the analyze, but did not check the outcome of these commands (whether the search path was set when connecting, I only listed my tables with schema qualified \dt schema.*).

I tested the first db1 through its php application, all went fine.

This all was done yesterday. Today I wanted to check the second applicatiion through php, but got no data.

Through this writing I verified again that the alter database set search_path was entered correctly:

postgres=# ALTER DATABASE db2 SET search_path TO public,xxx1,xxx2;
ALTER DATABASE
postgres=# \c db2

Then I tried to repeat and verify the alter database set search_path command, but it failed as described above.

(all commands where issued manually, but cut/pasted from 2 proven command files specific for the databases, used already 2 times for migration to 9.5 and 9.6))

To summarize:

I restored 2 databases into the same cluster, each with some individual schemas.
The alter database statements where issued when connected to postgres without error messages.
The second set search_path to db2 was executed, but is not respected and applied (when connecting to db2, search_path is still default!)
The search_path for the second db2 cannot be changed any more (the reported error).

I did not try to change the search_path for the first db1 for keeping my application running.

I have not examined the catalog relations due to limited knowledge.

My main goal is to provide the necessary information to debug, also for other users.

Thank you for investigating.