Thread: Bug in DROP NOT NULL
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. 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. Chris
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
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>> 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.
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.
regards, tom lane
> 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. Chris
>> 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. Any further comments on this? I still think it's a bug, ie. an invariant that's not being maintained... Chris
On Mon, Apr 04, 2005 at 10:17:40PM +0800, 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. > > Any further comments on this? I still think it's a bug, ie. an > invariant that's not being maintained... I agree with your observation. What sense does it make to allow marking an index "clusterable", knowing in advance that it will error out at cluster time? None to me. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "La persona que no quería pecar / estaba obligada a sentarseen duras y empinadas sillas / desprovistas, por ciertode blandosatenuantes" (Patricio Vogel)