Re: four template0 databases after vacuum
| От | David G. Johnston |
|---|---|
| Тема | Re: four template0 databases after vacuum |
| Дата | |
| Msg-id | CAKFQuwYCh7hhTOqQbtd6jA+aP06q4Yfaxe1H9gpYfP6yE5W5BA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: four template0 databases after vacuum (Melvin Davidson <melvin6925@gmail.com>) |
| Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: