Re: [HACKERS] Bug in DROP NOT NULL

[Christopher Kings-Lynne]

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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faq