Re: Duplicate entries in pg_depend after REINDEX CONCURRENTLY

On Tue, Nov 05, 2019 at 06:26:56PM -0500, Bruce Momjian wrote:
> Are there any bad effects of this bug on PG 12?

Not that I could guess, except a bloat of pg_depend... The more you
issue REINDEX CONCURRENTLY on an index, the more duplicated entries
accumulate in pg_depend as the dependencies of the old index are
passed to the new one, say:
=# create table aa (a int);
CREATE TABLE
=# create index aai on aa(a);
CREATE INDEX
=# select count(pg_describe_object(classid, objid, objsubid))
from pg_depend
where classid = 'pg_class'::regclass AND
objid in ('aai'::regclass);
count
-------
1
(1 row)
=# reindex index concurrently aai;
REINDEX
=# reindex index concurrently aai;
REINDEX
=# select count(pg_describe_object(classid, objid, objsubid))
from pg_depend
where classid = 'pg_class'::regclass AND
objid in ('aai'::regclass);
count
-------
3
(1 row)

After that, if for example one drops a column the rebuilt index
depends on or just drops the index, then all the duplicated entries
get removed as well with the index. Note that we have also cases
where it is legit to have multiple entries in pg_depend. For example
take the case of one index which lists two times the same column.
--
Michael