Skip site navigation (1) Skip section navigation (2)

Re: Bug in DROP NOT NULL

From: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
To: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
Cc: Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Bug in DROP NOT NULL
Date: 2005-03-31 12:53:53
Message-ID: 424BF2E1.7040303@familyhealth.com.au (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-patches
Sorry, was in a rush before.  I still don't have time to fix this for 
8.0.2, so that's why I rushed out the report.  Here is a full description...

> You can drop a NOT NULL on a column, even if that column is part of an 
> index that is clustered, where the index does not index NULLs.

First, install tsearch2...

test=# create table test (a tsvector);
CREATE TABLE
test=# create index test_gist_idx on test using gist (a);
CREATE INDEX
test=# \d test
       Table "public.test"
  Column |   Type   | Modifiers
--------+----------+-----------
  a      | tsvector |
Indexes:
     "test_gist_idx" gist (a)

test=# cluster test_gist_idx on test;
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.
test=# alter table test alter a set not null;
ALTER TABLE
test=# cluster test_gist_idx on test;
CLUSTER
test=# \d test
       Table "public.test"
  Column |   Type   | Modifiers
--------+----------+-----------
  a      | tsvector | not null
Indexes:
     "test_gist_idx" gist (a) CLUSTER

test=# alter table test alter a drop not null;
ALTER TABLE
test=# cluster test_gist_idx on test;
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.
test=# \d test
       Table "public.test"
  Column |   Type   | Modifiers
--------+----------+-----------
  a      | tsvector |
Indexes:
     "test_gist_idx" gist (a) CLUSTER

Note that index is still 'clustered', but unclusterable.

The correct behaviour IMHO is to prevent dropping NOT NULL on a column 
that particpates in such an index.  (Index access method that does not 
handle nulls)

> Also, I dont think that ALTER TABLE blah CLUSTER ON foo; actually warns 
> about clustering a non-null indexing index.  However, CLUSTER foo ON 
> blah; does.

I was wrong about that...

Chris

In response to

Responses

pgsql-hackers by date

Next:From: Qingqing ZhouDate: 2005-03-31 14:28:06
Subject: Re: understanding pg_stat* numbers
Previous:From: Andrew DunstanDate: 2005-03-31 12:31:44
Subject: Re: [HACKERS] contrib/pg_buffercache

pgsql-patches by date

Next:From: Tom LaneDate: 2005-03-31 14:59:55
Subject: Re: Bug in DROP NOT NULL
Previous:From: Andrew DunstanDate: 2005-03-31 12:31:44
Subject: Re: [HACKERS] contrib/pg_buffercache

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group