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.