Обсуждение: single user mode -P option is ignored
Hi
some customer has PostgreSQL 9.5.5. He cannot to login to database due broken index
When I use single mode against to this database I got a error
start transaction
could not open critical system index 2679
could not open critical system index 2679
Unfortunately option -E doesn't show anything
Regards
Pavel
Pavel Stehule <pavel.stehule@gmail.com> writes:
> some customer has PostgreSQL 9.5.5. He cannot to login to database due
> broken index
> When I use single mode against to this database I got a error
> start transaction
> could not open critical system index 2679
I can't reproduce this. Removing file 2679 (pg_index_indexrelid_index)
from an otherwise-OK database leads to
$ postgres --single busted
FATAL: could not open file "base/78215/2679": No such file or directory
as expected, but
$ postgres --single -P busted
PostgreSQL stand-alone backend 9.5.15
backend>
and all seems well, in particular I can successfully do
backend> reindex index pg_index_indexrelid_index;
and then things are back to normal.
So either this is something we fixed since 9.5.5, or your summary
of the problem is missing many critical details.
regards, tom lane
On 2018-Dec-19, Tom Lane wrote: > Pavel Stehule <pavel.stehule@gmail.com> writes: > > some customer has PostgreSQL 9.5.5. He cannot to login to database due > > broken index > > When I use single mode against to this database I got a error > > start transaction > > could not open critical system index 2679 > > I can't reproduce this. Judging by the "start transaction" string, I wonder if Pavel is using bootstrap mode rather than standard single mode. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
st 19. 12. 2018 v 18:07 odesílatel Alvaro Herrera <alvherre@2ndquadrant.com> napsal:
On 2018-Dec-19, Tom Lane wrote:
> Pavel Stehule <pavel.stehule@gmail.com> writes:
> > some customer has PostgreSQL 9.5.5. He cannot to login to database due
> > broken index
> > When I use single mode against to this database I got a error
> > start transaction
> > could not open critical system index 2679
>
> I can't reproduce this.
Judging by the "start transaction" string, I wonder if Pavel is using
bootstrap mode rather than standard single mode.
I used parameters --single and -P
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
st 19. 12. 2018 v 17:59 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> some customer has PostgreSQL 9.5.5. He cannot to login to database due
> broken index
> When I use single mode against to this database I got a error
> start transaction
> could not open critical system index 2679
I can't reproduce this. Removing file 2679 (pg_index_indexrelid_index)
from an otherwise-OK database leads to
$ postgres --single busted
FATAL: could not open file "base/78215/2679": No such file or directory
as expected, but
$ postgres --single -P busted
PostgreSQL stand-alone backend 9.5.15
backend>
and all seems well, in particular I can successfully do
backend> reindex index pg_index_indexrelid_index;
and then things are back to normal.
So either this is something we fixed since 9.5.5, or your summary
of the problem is missing many critical details.
I got different error message "could not open critical system index". I checked files, and related files was there.
I think so this issue is related to some damage of some system catalog - maybe pg_class, pg_attribute, ..
I got some diagnostics, but it is running on win, and the connect there is pretty slow
static void
load_critical_index(Oid indexoid, Oid heapoid)
{
<-->Relation<-->ird;
<-->/*
<--> * We must lock the underlying catalog before locking the index to avoid
<--> * deadlock, since RelationBuildDesc might well need to read the catalog,
<--> * and if anyone else is exclusive-locking this catalog and index they'll
<--> * be doing it in that order.
<--> */
<-->LockRelationOid(heapoid, AccessShareLock);
<-->LockRelationOid(indexoid, AccessShareLock);
<-->ird = RelationBuildDesc(indexoid, true);
<-->if (ird == NULL)
<--><-->elog(PANIC, "could not open critical system index %u", indexoid);
<-->ird->rd_isnailed = true;
<-->ird->rd_refcnt = 1;
<-->UnlockRelationOid(indexoid, AccessShareLock);
<-->UnlockRelationOid(heapoid, AccessShareLock);
load_critical_index(Oid indexoid, Oid heapoid)
{
<-->Relation<-->ird;
<-->/*
<--> * We must lock the underlying catalog before locking the index to avoid
<--> * deadlock, since RelationBuildDesc might well need to read the catalog,
<--> * and if anyone else is exclusive-locking this catalog and index they'll
<--> * be doing it in that order.
<--> */
<-->LockRelationOid(heapoid, AccessShareLock);
<-->LockRelationOid(indexoid, AccessShareLock);
<-->ird = RelationBuildDesc(indexoid, true);
<-->if (ird == NULL)
<--><-->elog(PANIC, "could not open critical system index %u", indexoid);
<-->ird->rd_isnailed = true;
<-->ird->rd_refcnt = 1;
<-->UnlockRelationOid(indexoid, AccessShareLock);
<-->UnlockRelationOid(heapoid, AccessShareLock);
looks like this code is called every time
Note:
I was not able to compile PostgreSQL 9.5 on Fedora 29 :-/
regards, tom lane
Pavel Stehule <pavel.stehule@gmail.com> writes:
> st 19. 12. 2018 v 17:59 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
>> So either this is something we fixed since 9.5.5, or your summary
>> of the problem is missing many critical details.
> I got different error message "could not open critical system index". I
> checked files, and related files was there.
Hmm ... looking at the relcache code, it appears that could only happen
if the pg_class entry for that index couldn't be found. Which probably
means you've suffered catastrophic damage to pg_class --- there's no
good reason to assume that only that one row has been clobbered.
Reindexing isn't going to get you out of that :-(
regards, tom lane
st 19. 12. 2018 v 19:01 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> st 19. 12. 2018 v 17:59 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
>> So either this is something we fixed since 9.5.5, or your summary
>> of the problem is missing many critical details.
> I got different error message "could not open critical system index". I
> checked files, and related files was there.
Hmm ... looking at the relcache code, it appears that could only happen
if the pg_class entry for that index couldn't be found. Which probably
means you've suffered catastrophic damage to pg_class --- there's no
good reason to assume that only that one row has been clobbered.
Reindexing isn't going to get you out of that :-(
I found this index in pg_class file . But that is all what I can say - I don't know if this table is readable or not.
regards, tom lane