pg_index.isclustered can work

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I can easily have pg_index.indisclustered updated to 'true' if you ever
CLUSTER the index. Is that useful to anyone? Remember, clustering
doesn't remain if you modify the table.

I don't see any value in it as long as CLUSTER is in the disreputable
shape it's in. I don't really like giving people the impression that
CLUSTER is a supported operation ;-)

regards, tom lane

Bruce Momjian <pgman@candle.pha.pa.us> writes:

I can easily have pg_index.indisclustered updated to 'true' if you ever
CLUSTER the index. Is that useful to anyone? Remember, clustering
doesn't remain if you modify the table.

I don't see any value in it as long as CLUSTER is in the disreputable
shape it's in. I don't really like giving people the impression that
CLUSTER is a supported operation ;-)

OK, I have an idea!

1) Set pg_index.indisclustered during CLUSTER
2) Clear pg_index.indisclustered during vacuum if any tuples are expired
3) or, have vacuum auto-CLUSTER the table as part of vacuum
4) Use pg_index.indisclustered in the optimizer

Of course, this assumes we have all the CLUSTER problems fixed.

FYI, we now have a CLUSTER section in the TODO list which says:

* CLUSTER
* cluster all tables at once
* prevent lose of indexes, permissions, inheritance
* Automatically keep clustering on a table
* Keep statistics about clustering, perhaps during VACUUM ANALYZE
[optimizer]

Doesn't look too bad.

FYI, the reference to pg_index.indisclustered in ODBC was assuming it
meant it was a hash index, which is just plain wrong, so that code is
not coming back.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

[snip]

FYI, the reference to pg_index.indisclustered in ODBC was assuming it
meant it was a hash index,

Hmm where could I see it ?

which is just plain wrong, so that code is
not coming back.

regards,
Hiroshi Inoue

[ Charset US-ASCII unsupported, converting... ]

Bruce Momjian wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

[snip]

FYI, the reference to pg_index.indisclustered in ODBC was assuming it
meant it was a hash index,

Hmm where could I see it ?

which is just plain wrong, so that code is
not coming back.

It is in info.c, SQLStatistics():

/*
* Clustered index? I think non-clustered should be type
* OTHER not HASHED
*/
set_tuplefield_int2(&row->tuple[6], (Int2) (atoi(isclustered) ?
SQL_INDEX_CLUSTERED : SQL_INDEX_OTHER));

The HASH mention has me confused. Is that code valid? Maybe so. What
does ODBC think the column means, HASH or CLUSTER?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

[ Charset US-ASCII unsupported, converting... ]

Bruce Momjian wrote:

Bruce Momjian <pgman@candle.pha.pa.us> writes:

[snip]

FYI, the reference to pg_index.indisclustered in ODBC was assuming it
meant it was a hash index,

Hmm where could I see it ?

which is just plain wrong, so that code is
not coming back.

I now think the original ODBC code was right. It has defined as
possible values:

#define SQL_TABLE_STAT 0
#define SQL_INDEX_CLUSTERED 1
#define SQL_INDEX_HASHED 2
#define SQL_INDEX_OTHER 3

Not sure what SQL_TABLE_STAT is for, perhaps we should flag for
pg_statistics? Anyway, the test of the flag looks correct to me. Why
they would care only about HASH and CLUSTERED, I don't know.

I will restore the code, and fix the HASH while I am at it.

Of course, the cluster field is still alway false, but it will be ready
if we ever get it working.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026