Hi,
On Sunday 05 December 2010 07:50:51 Martin Atukunda wrote:
>
> CREATE INDEX USING GIN complains about array containing null values yet
> none exist.
>
> Take the following SQL as an example.
>
> -- >8 --
>
> BEGIN;
> CREATE TABLE t (id serial primary key, apps bigint[]);
> INSERT INTO t (apps) VALUES ('{1,2,3,4}');
> INSERT INTO t (apps) VALUES ('{1,2,3,4}');
>
> -- insert an array with one element as a null value
> INSERT INTO t (apps) VALUES ('{1,2,3,NULL}');
>
> -- remove the null value
> UPDATE t SET apps[4] = -1 WHERE apps[4] IS NULL;
>
> -- the create index fails
> CREATE INDEX t_apps_idx ON t USING GIN(apps);
>
> COMMIT;
>
> -- 8< --
>
>
> note that index creatiion succeeds if I first create the index without
> inserting a row containing an array with a null element.
Due to the wonders of MVCC the old row is still available in the heap. Best
read the docs about what MVCC means. And as pg's indexes don't care about
visibility it will still try to index the "old" row.
Andres