Обсуждение: [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

buschmann@nidsa.net writes:
> 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.

I tried to reproduce this, but could not.  Can you reconstruct the
exact sequence of commands you issued to get here?
        regards, tom lane


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

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.

On Fri, Oct 6, 2017 at 9:50 AM, Hans Buschmann <buschmann@nidsa.net> wrote:
> 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.

Since you're on Postgres 10, it should be pretty easy to install
amcheck [1]. Once you've done so, please let us know what error, if
any, the following query raises:

SELECT bt_index_check(c.oid), c.relname, c.relpages
FROM pg_index i
JOIN pg_opclass op ON i.indclass[0] = op.oid
JOIN pg_am am ON op.opcmethod = am.oid
JOIN pg_class c ON i.indexrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE am.amname = 'btree' AND n.nspname = 'pg_catalog'
AND c.relpersistence != 't'
AND i.indisready AND i.indisvalid;

I would also try the same query without the "n.nspname = 'pg_catalog",
if that doesn't take too long -- that will run the same tests on all
of your indexes, not just those on catalog tables.

If these queries do throw an error, the next step is probably to take
the information from the error message, and use that to drill down to
the level of individual corrupt B-Tree pages. From there, you may be
able to show us items on the page using pageinspect's bt_page_items().

I'm not all that confident that this will be interesting, because
amcheck doesn't currently look at heap pages, which could easily be
the only place that is observably corrupt (there is a planned
enhancement to do something there too). It's an easy to run smoke
test, though.

[1] https://www.postgresql.org/docs/10/static/amcheck.html
-- 
Peter Geoghegan


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

I run the above query (also without restricting to pg_catalog) under superuser postgres in both dbs.
There was no error.

The databases are on SSD, no problems with storage so far (checksums are enabled).

I only did standard SQL/utility commands to create, restore, set_search_path and analyze the 2 databases one after another without any error.

The first error that occurred was that the second alter database set search_path was executed without error, but had no effect!
Then afterwords other alter database set search_path commands gave the error of duplicate key.

The procedure is quite easy and straight forward: only create/restore/set_search_path/analyze 2 databases which have objects in other schemas as public.

The database size is about  1 GB in PG_DATA each.
If necessary, I can repeat the procedure on another machine, but please give me some time.

Thank you

Hans Buschmann