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: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org, 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 17:06:20
Message-ID: 201012051806.20719.andres@anarazel.de (view raw or flat)
Thread:
Lists: pgsql-bugs
On Sunday 05 December 2010 17:42:59 Tom Lane wrote:
> Andres Freund <andres(at)anarazel(dot)de> writes:
> >> On Sunday 05 December 2010 13:07:23 Martin Atukunda wrote:
> >>>> so, basically, the only way out of this would be to:
> I think the reason the given example fails is just that it's all being
> done in one transaction.  If the null-containing row were known dead
> it wouldn't get indexed.  So: commit.
Um I doubt it.

test=# \i /tmp/test.sql 
DROP TABLE IF EXISTS t;
DROP TABLE
Time: 36.070 ms
BEGIN;
BEGIN
Time: 0.122 ms
CREATE TABLE t (id serial primary key, apps bigint[]);
psql:/tmp/test.sql:3: NOTICE:  00000: CREATE TABLE will create implicit 
sequence "t_id_seq" for serial column "t.id"
LOCATION:  transformColumnDefinition, parse_utilcmd.c:341
psql:/tmp/test.sql:3: NOTICE:  00000: CREATE TABLE / PRIMARY KEY will create 
implicit index "t_pkey" for table "t"
LOCATION:  DefineIndex, indexcmds.c:438
CREATE TABLE
Time: 102.843 ms
INSERT INTO t (apps) VALUES ('{1,2,3,4}');
INSERT 0 1
Time: 0.408 ms
INSERT INTO t (apps) VALUES ('{1,2,3,4}');
INSERT 0 1
Time: 0.111 ms
INSERT INTO t (apps) VALUES ('{1,2,3,NULL}');
INSERT 0 1
Time: 0.127 ms
UPDATE t SET apps[4] = -1 WHERE apps[4] IS NULL;
UPDATE 1
Time: 0.271 ms
COMMIT;
COMMIT
Time: 0.086 ms
BEGIN;
BEGIN
Time: 0.192 ms
CREATE INDEX  t_apps_idx ON t USING GIN(apps);
psql:/tmp/test.sql:18: ERROR:  22004: array must not contain null values
LOCATION:  ginarrayextract, ginarrayproc.c:53
COMMIT;
ROLLBACK
Time: 0.076 ms

I remembered seeing that before....


Andres

In response to

Responses

pgsql-bugs by date

Next:From: Peter EisentrautDate: 2010-12-05 17:10:06
Subject: Re: BUG #5783: plpythonu bool behavior change
Previous:From: Tom LaneDate: 2010-12-05 16:42:59
Subject: Re: 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