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

On Fri, Oct 6, 2017 at 9:50 AM, Hans Buschmann <buschmann(at)nidsa(dot)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