pgsql-server/src/backend catalog/index.c comma ...

-----Original Message-----
From: Tom Lane

CVSROOT: /cvsroot
Module name: pgsql-server
Changes by: tgl@svr1.postgresql.org 03/09/19 16:57:42

Modified files:
src/backend/catalog: index.c
src/backend/commands: indexcmds.c

Log message:
Seems like a bad idea that REINDEX TABLE supports (or
thinks it does)
reindexing system tables without ignoring system
indexes,

Why ?

regards.
Hiroshi Inoue

"Hiroshi Inoue" <inoue@tpf.co.jp> writes:

Log message:
Seems like a bad idea that REINDEX TABLE supports (or
thinks it does)
reindexing system tables without ignoring system
indexes,

Why ?

I'd ask the question the other way: why would it be a good idea to allow
this in REINDEX TABLE and not in the other two cases? And did it really
work?

The REINDEX code is messy and fragile enough that I think we should do
whatever we can to simplify it.

regards, tom lane

"Hiroshi Inoue" <inoue@tpf.co.jp> writes:

Log message:
Seems like a bad idea that REINDEX TABLE supports (or
thinks it does)
reindexing system tables without ignoring system
indexes,

Why ?

I'd ask the question the other way: why would it be a good
idea to allow
this in REINDEX TABLE and not in the other two cases? And
did it really
work?

Yes. I would revert your change.

regards,
Hiroshi Inoue

"Hiroshi Inoue" <inoue@tpf.co.jp> writes:

I'd ask the question the other way: why would it be a good
idea to allow
this in REINDEX TABLE and not in the other two cases? And
did it really
work?

Yes. I would revert your change.

You didn't answer the first question: why's this a good idea?
It seems risky and of little value to try to support system
table reindexing without disabling system indexes.

Also, your assertion that it works doesn't convince me. That business
in reindex_table about doing two setRelhasindex() calls gave me the
willies. Why was that needed? "to keep consistency with WAL" isn't
enough commentary for code as strange as that. And having a
CommandCounterIncrement() that's executed in some cases and not others
is a recipe for bugs; we've been burnt by that before.

regards, tom lane

"Hiroshi Inoue" <inoue@tpf.co.jp> writes:

I'd ask the question the other way: why would it be a good
idea to allow
this in REINDEX TABLE and not in the other two cases? And
did it really
work?

Yes. I would revert your change.

You didn't answer the first question: why's this a good idea?
It seems risky and

of little value to try to support system
table reindexing without disabling system indexes.

Why could you determine it ? Is PostgreSQL your system ?
Because It is never of little value of cource, I supported it.
I intended to support on-line REINDEX from the first, I first
implemented REINDEX in standalone mode not in bootstrap(Jan's
idea) mode. I also intended to support on-line reindexing nailed
relations but I didn't have time to achive it.

Also, your assertion that it works doesn't convince me. That business
in reindex_table about doing two setRelhasindex() calls gave me the
willies. Why was that needed?

The setRelhasIndex() has no meaning to other backends, i.e the false state
is never visible to other backends.

"to keep consistency with WAL" isn't
enough commentary for code as strange as that. And having a
CommandCounterIncrement() that's executed in some cases and not others
is a recipe for bugs; we've been burnt by that before.

The code you are referring is to reindex pg_class relation. The code has
never
active unless an #ifdef is defined.

regards,
Hiroshi Inoue

"Hiroshi Inoue" <inoue@tpf.co.jp> writes:

Why could you determine it ? Is PostgreSQL your system ?

Well, if you prefer, we can have a discussion and vote about it on pghackers.
But you have not answered my questions. Why was the code set up to
allow live reindexing of system tables via REINDEX TABLE, but not via
REINDEX INDEX or REINDEX DATABASE? It seems to me that those three
cases ought to work equally well or badly. Let's remove the restriction
from all three, if it's not needed.

Also, your assertion that it works doesn't convince me. That business
in reindex_table about doing two setRelhasindex() calls gave me the
willies. Why was that needed?

The setRelhasIndex() has no meaning to other backends, i.e the false state
is never visible to other backends.

Then why bother? I agree that it's probably not needed for other
backends --- since you have an exclusive lock on the table, nobody else
should be trying to examine it. But then why do it at all? The only
thing you need is to force IsIgnoringSystemIndexes true locally, which
would seem to me a much more secure way of proceeding.

The goal that I am trying to accomplish here is to make the reindexing
code more visibly safe and correct. I'm not convinced that we still
need live-reindexing capability in 7.4 (I think the btree space
recycling code solves the problem better). But I'm happy to leave it in
if these other questions can be addressed.

regards, tom lane

"Hiroshi Inoue" <inoue@tpf.co.jp> writes:

Why could you determine it ? Is PostgreSQL your system ?

Well, if you prefer, we can have a discussion and vote about
it on pghackers.

Oh discussion *first* is good but You committed *first*.
So isn't it reasonable to revert your change *first* ?

This is the second time you disable the on-line reindex
functionality for system tables. Why must I explain the
same thing many times ?

May I ask again ? Is PostgreSQL your system ?

regards,
Hiroshi Inoue

On Sun, 21 Sep 2003, Hiroshi Inoue wrote:

"Hiroshi Inoue" <inoue@tpf.co.jp> writes:

Why could you determine it ? Is PostgreSQL your system ?

Well, if you prefer, we can have a discussion and vote about
it on pghackers.

Oh discussion *first* is good but You committed *first*.
So isn't it reasonable to revert your change *first* ?

This is the second time you disable the on-line reindex
functionality for system tables. Why must I explain the
same thing many times ?

Actually, as a comment here, since I *think* I understand where Tom is
coming from ... and since I've either missed it, or it hasn't been
answered yet ... why was the original patch incomplete in only addressing
1 of 3 REINDEX conditions? Is there a reason why that one condition
is/was safe to do it with, but not the other 2?

Again ... if I understand Tom's objections to, and reason for reversing,
this patch correctly ...

"Marc G. Fournier" <scrappy@postgresql.org> writes:

Actually, as a comment here, since I *think* I understand where Tom is
coming from ... and since I've either missed it, or it hasn't been
answered yet ... why was the original patch incomplete in only addressing
1 of 3 REINDEX conditions? Is there a reason why that one condition
is/was safe to do it with, but not the other 2?

That's exactly what's bothering me. Where I'd like to end up is that
either all three variants of REINDEX allow this, or all three do not.
I don't understand why only REINDEX TABLE should support it.

regards, tom lane

-----Original Message-----
From: Marc G. Fournier [mailto:scrappy@postgresql.org]
Sent: Sunday, September 21, 2003 6:45 AM
To: Hiroshi Inoue
Cc: 'Tom Lane'; pgsql-committers@postgresql.org
Subject: Re: [COMMITTERS] pgsql-server/src/backend
catalog/index.c comma ...

On Sun, 21 Sep 2003, Hiroshi Inoue wrote:

"Hiroshi Inoue" <inoue@tpf.co.jp> writes:

Why could you determine it ? Is PostgreSQL your system ?

Well, if you prefer, we can have a discussion and vote about
it on pghackers.

Oh discussion *first* is good but You committed *first*.
So isn't it reasonable to revert your change *first* ?

This is the second time you disable the on-line reindex
functionality for system tables. Why must I explain the
same thing many times ?

Actually, as a comment here, since I *think* I understand where Tom is
coming from ... and since I've either missed it, or it hasn't been
answered yet ... why was the original patch incomplete in
only addressing
1 of 3 REINDEX conditions? Is there a reason why that one condition
is/was safe to do it with, but not the other 2?

Sorry to trouble you.

In the first place, REINDEX is neither an SQL standard command nor
a preferable one.

When I introduced REINDEX command before 7.0, it was not
transaction-safe and only allowed to call under standalone mode
essentially.

Before 7.1, the introduction of pg_class.relfilenode gave us a possibilty
to make REINDEX command transaction-safe and I tried to make
REINDEX available under postmaster and the result was
1.All user indexes/tables could be REINDEXed under postmaster
2.System tables except shared or nailed ones could be REINDEXed
under postmaster.

Note that we couldn't reindex all system tables under postmaster.
It's the main reason why I didn't implement REINDEX DATABASE
under postmaster. As for REINDEX, I have

Tue Nov 20 02:46:13 2001 UTC (22 months ago) Tom committed
the following change which disables the functionality to reindex
system tables under postmaster.
Some minor tweaks of REINDEX processing: grab exclusive
lock a little earlier, make error checks more uniform.

The above change was the first time he disables the functionality.
I noticed the change, complained to him and
Thu Feb 7 00:27:30 2002 UTC (19 months, 1 week ago) I
Removed a check for REINDEX TABLE.

And this is the second time he disables the functionality without
any notification to me. Honestly I don't understand why I have to
explain this kind of thing only so as to revert the change.

regards,
Hiroshi Inoue

I've been looking at the issues involved in reindexing system tables,
and I now have what I think is a fairly defensible set of proposals.

We should whenever possible use the same reindexing technique used by
CLUSTER: assign a new relfilenode number, build the new index in that
file, and apply an ordinary heap_update operation to update the index's
pg_class row with the new relfilenode value. This technique is fully
crash-safe and transaction-safe (meaning it can be rolled back even after
completion, in case of failure later in the same transaction). However,
there are several pitfalls for applying it to system indexes:

1. There is a problem for a shared index, because we have no way to
update pg_class.relfilenode in all the other databases besides ours.
I see no good way around this problem.

2. There is a problem for a nailed-in-cache index, because the relcache
isn't prepared to cope with relfilenode updates for such indexes.
However, that is fixable.

3. There is a problem for an index on pg_class itself: doing heap_update
on a pg_class row must make new index entries. We have to be careful
that the new row does appear in the updated index, while not making
entries in old-and-possibly-broken indexes. This is doable.

4. There is a problem with updating indexes that might be used for catalog
fetches executed during the index_build process: if we try to use the
partially-rebuilt index for such a fetch we will fail. In the case of
disaster recovery the problem doesn't exist because we'll have
IsIgnoringSystemIndexes true, but for an ordinary on-line indexing
operation there's definitely a risk. Presently the code relies on
"deactivating indexes" to prevent this, but I think it can be done more
simply, because we only need to suppress access to the target index
locally in the reindexing backend. (Other backends will be locked out by
means of an AccessExclusiveLock on the system catalog in question.)

In short then, we can fix things so that the new-relfilenode path can be
taken in all cases except shared indexes. For shared indexes, we have
little choice but to truncate and rebuild the index in-place. (There is
then no need to update its pg_class row at all.) This is of course
horribly crash-unsafe.

The code presently uses a "deactivated indexes" flag (namely, setting
pg_class.relhasindex false) to provide some protection against a crash
midway through an in-place reindex. However, this concept is really quite
useless for a shared index, because only one database could contain the
deactivation flag. Accesses from any other database would still try to
use the broken index.

Based on this analysis, I feel that the "deactivated indexes" mechanism
is of no further value and should be retired. We should instead simply
acknowledge that reindexing shared indexes is dangerous. I propose that
it be allowed only from a standalone backend. Specifically I think that
REINDEX INDEX and REINDEX TABLE should error out if pointed to a shared
index or table, unless running in a standalone backend; REINDEX DATABASE
should skip over the shared indexes unless running in a standalone
backend. (I'm not convinced that either -O or -P needs to be insisted on
by REINDEX, btw, but we can discuss that separately.) We'll have to warn
users not to try to restart the database when reindexing of a shared table
wasn't successfully completed.

Details to back up the above claims:

Part of the code needed to fix the relcache restriction on nailed indexes
is already there, but ifdef'd out; that's the part that re-reads the
index's pg_class row after receiving SI inval for it. There are some
cases not yet handled though. In the first place, if the nailed index
being modified is pg_class_oid_index, ScanPgRelation will try to use that
same index to load the updated row, and will fail because it'll be trying
to use the old relfilenode. We can easily force a seqscan in that case,
however. A more subtle problem is that if an SI buffer overflow occurs,
RelationCacheInvalidate walks through the relation cache in a random
order. I believe that the correct update order has to be first
pg_class_oid_index, then the other nailed indexes, and finally all other
relation cache entries. pg_class_oid_index has to be updated first (with
the aforementioned seqscan), since it will be used by ScanPgRelation to
reread the pg_class rows for the other nailed indexes. Only when we are
sure the nailed indexes are up-to-date can we safely rebuild other
relcache entries.

Assigning a new relfilenode to indexes of pg_class is not a big deal when
we don't have an index-corruption problem. We simply have to make the new
heap_updated row before we start index_build (which indeed the code does
already); both old and new rows will be indexed in the new index, and all
is well. However, if we suspect index corruption then it is important not
to try to make entries into the old indexes, because we might crash trying
to update a corrupt index. I propose the following behavior:

REINDEX INDEX: no special action; this is not the thing to use
when index corruption is suspected anyway.

REINDEX TABLE: while operating on pg_class, arrange for the
relcache's list of known indexes of pg_class to contain only the
indexes already reindexed. In this way, only those indexes will
be updated by CatalogUpdateIndexes(), and so no untrustworthy
indexes will be touched while making new pg_class rows.

REINDEX DATABASE: take care to process pg_class first.

To avoid catalog accesses via an index that's being rebuilt, we can simply
generalize the SetReindexProcessing() state to include the OID of the
index currently being rebuilt. Places that presently make checks for
IsIgnoringSystemIndexes can check this as well and fall back to seqscans
when the targeted index would have been used.

One other point: IsIgnoringSystemIndexes seems presently to be taken to
mean not only that we don't *read* the system indexes, but that we don't
*write* them either. I think this is horribly dangerous; it effectively
means that the only thing you can safely do in a backend started with -P
is REINDEX. If you make any other changes to the system catalogs then
you've re-corrupted the indexes. Also, you don't have any protection
against duplicate entries getting made, since the unique indexes are
disabled. It would be a lot safer to define IsIgnoringSystemIndexes as
preventing the system indexes from being used for lookups, while still
updating the indexes on any catalog modification. This will not affect
the safety of REINDEX and will make other operations much less prone to
pilot error.

Comments?

regards, tom lane

First it should have been discussed before your commitment or at least
it should be discussed after reversing your change.

I require you to explain me why you committed the change
with no discussion and little investigation.

I also noticed that your change for catalog/index.c

Revision 1.200 / (download) - annotate - [select for diffs] , Mon Sep 23
00:42:48 2002 UTC (11 months, 4 weeks ago) by tgl
Changes since 1.199: +124 -161 lines
Diff to previous 1.199
Get rid of bogus use of heap_mark4update in reindex operations (cf.
recent bug report). Fix processing of nailed-in-cache indexes;
it appears that REINDEX DATABASE has been broken for months :-(.

removed an #ifndef ENABLE_REINDEX_NAILED_RELATIONS
trial stuff. I'm very surprised to see it now.

regards,
Hiroshi Inoue

"Hiroshi Inoue" <inoue@tpf.co.jp> writes:

I require you to explain me why you committed the change
with no discussion and little investigation.

If you want an apology for not having discussed it in advance, I'll
gladly offer one. It was poorly done.

I do, however, think that the reindexing code needs work. Can we
get on with discussing the technical issues?

regards, tom lane

On Mon, Sep 22, 2003 at 04:56:35AM +0900, Hiroshi Inoue wrote:

First it should have been discussed before your commitment or at least
it should be discussed after reversing your change.

I require you to explain me why you committed the change
with no discussion and little investigation.

I also noticed that your change for catalog/index.c

Revision 1.200 / (download) - annotate - [select for diffs] , Mon Sep 23
00:42:48 2002 UTC (11 months, 4 weeks ago) by tgl

You know you're talking about a commit of 1 year ago?

Kurt

Kurt Roeckx wrote:

On Mon, Sep 22, 2003 at 04:56:35AM +0900, Hiroshi Inoue wrote:

First it should have been discussed before your commitment or at least
it should be discussed after reversing your change.

I require you to explain me why you committed the change
with no discussion and little investigation.

I also noticed that your change for catalog/index.c

Revision 1.200 / (download) - annotate - [select for diffs] , Mon Sep 23
00:42:48 2002 UTC (11 months, 4 weeks ago) by tgl

You know you're talking about a commit of 1 year ago?

Yes of cource. Unfortunately I haven't had enough time to
check all the changes since long. This time I was lucky to
find a change to spoil my effort. Is it strange for me to
doubt other places ? I looked again the current code about
REINDEX command and found the above change which is closely
related to this topic.

regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/

Tom Lane wrote:

"Hiroshi Inoue" <inoue@tpf.co.jp> writes:

I require you to explain me why you committed the change
with no discussion and little investigation.

If you want an apology for not having discussed it in advance, I'll
gladly offer one. It was poorly done.

Thanks.

I do, however, think that the reindexing code needs work. Can we
get on with discussing the technical issues?

OK but I would need some time to remember the code.

regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/

Here is the proposed patch. This fixes the problems that prevented the
relcache from coping with relfilenode changes for nailed-in-cache
indexes. Accordingly, REINDEX is now crash-safe and transaction-safe
for all cases except reindexing shared system catalogs. That case is
permitted only in a standalone backend (!IsUnderPostmaster). We no
longer require -O to reindex system tables, and since -P now suppresses
only reads not writes of system indexes, it is safe to allow from the
client side. Upshot: reindexing system catalogs can be done without a
standalone backend for all cases except shared catalogs.

regards, tom lane

I've just put back your previous change, sorry.
As I already mentioned many times it must be the first thing.

Though I don't remenber my code completely yet, I would
reply to some points.
Unfortunately REINDEX wasn't a eagerly wanted command when
I implemented it. Though I wanted to introduce a per index
flag, I unwillingly used an existent per table flag(relhasindex)
instead. Because it was impossible to make REINDEX transaction-safe
then, such flag was needed to suppress inconsistency as less
as possible.
I also unwillingly introduced the ReindexProcessing mode(flag)
because I didn't think of other quick solutions.

IIRC the main reason why I gave up the REINDEX functionality
on nailed relations was the difficulty of reindexing pg_class
and the handling of relcache overflow. I didn't have much time
to test it. In addtion REINDEX wasn't a recognized command
then and I found no one to discuss the situation.

Tom Lane wrote:

I've been looking at the issues involved in reindexing system tables,
and I now have what I think is a fairly defensible set of proposals.

We should whenever possible use the same reindexing technique used by
CLUSTER:

REINDEX was the first command which used the pg_class.relfilenode
functionality. The pg_class.relfilenode was essentially my proposal.

1. There is a problem for a shared index, because we have no way to
update pg_class.relfilenode in all the other databases besides ours.
I see no good way around this problem.

So the current REINDEX disallows on-line reindex on shared relations.

2. There is a problem for a nailed-in-cache index, because the relcache
isn't prepared to cope with relfilenode updates for such indexes.
However, that is fixable.

My code works pretty good with nailed relations except pg_class
#if defined (ENABLE_REINDEX_NAILED_RELATIONS).

3. There is a problem for an index on pg_class itself: doing heap_update
on a pg_class row must make new index entries. We have to be careful
that the new row does appear in the updated index, while not making
entries in old-and-possibly-broken indexes. This is doable.

Yes.

Sorry I have no time to continue the discussion now.

regards,
Hiroshi Inoue
http://www.geocities.jp/inocchichichi/psqlodbc/

Hiroshi Inoue wrote:

instead. Because it was impossible to make REINDEX transaction-safe
then, such flag was needed to suppress inconsistency as less
as possible.

This mean that the actual REINDEX is not transaction-safe ?

Regards
Gaetano Mendola