Re: Dealing with CLUSTER failures

From: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
To: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL-patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: Dealing with CLUSTER failures
Date: 2005-05-08 04:22:51
Message-ID: 20050508122205.E16361@houston.internal
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers 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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Madison Kelly 2005-05-08 04:23:39 Re: [HACKERS] Invalid unicode in COPY problem
Previous Message Alvaro Herrera 2005-05-08 04:22:01 Re: rendezvous

Browse pgsql-patches by date

  From Date Subject
Next Message Tom Lane 2005-05-08 04:28:02 Re: Dealing with CLUSTER failures
Previous Message Christopher Kings-Lynne 2005-05-08 04:12:36 Re: Update psql and pg_dump for new COPY api