Re: four template0 databases after vacuum

On Sun, Feb 7, 2016 at 8:14 AM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

> With regards to Karsten's thought, here is a query to find any pg_catalog
> indexes that are corrupt.
>
> SELECT n.nspname as schema,
> i.relname as table,
> i.indexrelname as index,
> i.idx_scan,
> i.idx_tup_read,
> i.idx_tup_fetch,
> CASE WHEN idx.indisprimary
> THEN 'pkey'
> WHEN idx.indisunique
> THEN 'uidx'
> ELSE 'idx'
> END AS type,
> pg_get_indexdef(idx.indexrelid),
> CASE WHEN idx.indisvalid
> THEN 'valid'
> ELSE 'INVALID'
> END as statusi,
> pg_relation_size(quote_ident(n.nspname)|| '.' ||
> quote_ident(i.relname)) as size_in_bytes,
> pg_size_pretty(pg_relation_size(quote_ident(n.nspname)|| '.' ||
> quote_ident(i.relname))) as size
> FROM pg_stat_all_indexes i
> JOIN pg_class c ON (c.oid = i.relid)
> JOIN pg_namespace n ON (n.oid = c.relnamespace)
> JOIN pg_index idx ON (idx.indexrelid = i.indexrelid )
> WHERE n.nspname = 'pg_catalog'
> AND NOT idx.indisvalid
> ORDER BY 1, 2, 3;
>
>
This only returns "invalid" indexes but couldn't some forms of corruption
result in errors without the "indisvalid" flag being removed?

Given that OID is a PK I don't see how there isn't some form of corruption
going on here.

Aside from their presence indicating that something is wrong, somewhere,
the ​fact that there are four of these template0's doesn't seem that
problematic. They neither depend upon nor are depended upon within the
cluster. Though I am a bit curious how "createdb -T template0" still
works. I guess it must ignore the extra records during its search...

I've trimmed a lot here but both up and down thread two observations seem
meaningful when considering that as far as PostgreSQL is concerned only one
of the 4 template0s is even visible. There isn't enough use of "ctid" in
the other queries to confirm that they are all talking about the same
physical entry.

1)
​"""
​But, the age of three template0 did not change. Only [one] of 4 template0
had successfully changed the age young.
"""
and 2)
"""
12772 | (36,25) | template0 | 10 | 6 | C | C |
t | f | -1 | 12772 | 2412920847
| 1663 | {=c/pos
tgres,postgres=CTc/postgres}
12772 | (36,26) | template0 | 10 | 6 | C | C |
t | f | -1 | 12772 | 2264969019
| 1663 | {=c/pos
tgres,postgres=CTc/postgres}
​"""

Note that the first row recognizes that the maximum "​datlastsysoid" has
significantly increased compared to what the other three rows report (all
identical to the second record).

(36,25) seems to be in play while all of others, while present, are simply
ignored.

Now, that said, if not every part of the system (like
autovacuum-calculations) truly is ignoring them then that discrepancy is
quite likely to cause a problem.

The question is whether its worth any effort exploring this further or
should we just advise that the system is presently in an inconsistent state
and that said inconsistency needs to be corrected by any means possible -
in this case the obvious answer is DELETE FROM pg_database WHERE oid = #
AND ctid != (36,25);followed by a REINDEX on pg_database.

I do not suggest this myself - I would only do this myself if a hacker
agreed.

The rest of it hints to a potential bug or improvement, somewhere, but I
suspect the cost/benefit of finding it is going to be prohibitive based
upon a single report on an unpatched release. But that is coming from a
non-hacker.

David J.