Re: Dealing with CLUSTER failures - Mailing list pgsql-patches

Seems like an idea to me...

On another note, what about the problem I pointed out where it's not
possible to drop the default on a serial column after you alter it to a
varchar, for example...

Chris


On Sat, 7 May 2005, Bruce Momjian wrote:

> Christopher Kings-Lynne wrote:
>>> I don't think that's a bug.  You may not intend ever to cluster on that
>>> index again, and if you try it will tell you about the problem.
>>
>> Except it breaks the 'cluster everything' case:
>>
>> test=# cluster;
>> ERROR:  cannot cluster when index access method does not handle null values
>> HINT:  You may be able to work around this by marking column "a" NOT NULL.
>
> I looked over this item, originally posted as:
>
>     http://archives.postgresql.org/pgsql-hackers/2005-03/msg01055.php
>
> It seems that if you use an index method that doesn't support NULLs, you
> can use ALTER to set the column as NOT NULL, then CLUSTER, and then set
> it to allow NULLs, and when you CLUSTER all tables, the cluster errors
> out on that table.
>
> I thought about removing the cluster bit when you do the alter or
> something like that, but it seems too confusing and error-prone to be
> sure we get every case.
>
> I think the main problem is that while cluster is a performance-only
> feature, we error out if we can't cluster one table, basically treating
> it as though it needs transaction semantics.  It doesn't.
>
> This patch throws an ERROR of you cluster a specific index that can't be
> clustered, but issues only a WARNING if you are clustering all tables.
> This allows it to report the failed cluster but keep going.  I also
> modified the code to print the index name in case of failure, because
> without that the user doesn't know the failing index name in a
> database-wide cluster failure.
>
> Here is an example:
>
>     test=> cluster  test_gist_idx on  test;
>     ERROR:  cannot cluster on index "test_gist_idx" because access method
>     does not handle null values
>     HINT:  You may be able to work around this by marking column "a" NOT NULL.
>     test=> cluster;
>     WARNING:  cannot cluster on index "test_gist_idx" because access method
>     does not handle null values
>     HINT:  You may be able to work around this by marking column "a" NOT NULL.
>     CLUSTER
>
> You can see the ERROR for a specific index, and WARNING for full
> database cluster.
>
> --
>  Bruce Momjian                        |  http://candle.pha.pa.us
>  pgman@candle.pha.pa.us               |  (610) 359-1001
>  +  If your life is a hard drive,     |  13 Roberts Road
>  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
>