Re: BUG #15460: Error while creating index or constraint

On Mon, Oct 29, 2018 at 12:53 PM Peter Geoghegan <pg@bowt.ie> wrote:
> Is it possible for you to provide me with a dump of the data, or a
> minimal test case? You can provided it to me off-list if that's
> easier, possibly using Google drive or something. I realize that the
> amount of data involved here isn't trivial, so this may be difficult.

It might also be helpful to see if amcheck shows any problems with the
indexes you can create without error. You could run:

CREATE EXTENSION amcheck;

SELECT bt_index_check(index => c.oid, heapallindexed => true),
               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'
-- Don't check temp tables, which may be from another session:
AND c.relpersistence != 't'
-- Function may throw an error when this is omitted:
AND c.relkind = 'i' AND i.indisready AND i.indisvalid
ORDER BY c.relpages DESC;

Hopefully this won't find any problems/raise any errors, but if it
does then they'll likely be relevant. It might take several minutes to
run the query, or maybe even several hour if there is lots of data.
You may have to use your own judgement when it comes to determining
what's worth checking, and what you don't have time to check (e.g.,
add something extra to the where clause, so that only some indexes get
checked). I'm suggesting this because it isn't very much work, and may
shed some light on the problem. Though the likelihood is that it won't
find anything wrong.

-- 
Peter Geoghegan