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

Re: BUG #5784: CREATE INDEX USING GIN complains about array containing null values yet none exist

From: Andres Freund <andres(at)anarazel(dot)de>
To: pgsql-bugs(at)postgresql(dot)org
Cc: "Martin Atukunda" <matlads(at)gmail(dot)com>
Subject: Re: BUG #5784: CREATE INDEX USING GIN complains about array containing null values yet none exist
Date: 2010-12-05 11:39:23
Message-ID: 201012051239.23510.andres@anarazel.de (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Martin AtukundaDate: 2010-12-05 12:07:23
Subject: Re: BUG #5784: CREATE INDEX USING GIN complains about array containing null values yet none exist
Previous:From: Martin AtukundaDate: 2010-12-05 06:50:51
Subject: BUG #5784: CREATE INDEX USING GIN complains about array containing null values yet none exist

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