single user mode -P option is ignored

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

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);

looks like this code is called every time

Note:

I was not able to compile PostgreSQL 9.5 on Fedora 29 :-/

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.