Re: BUG #5784: CREATE INDEX USING GIN complains about array containing null values yet none exist - Mailing list pgsql-bugs

On Sunday 05 December 2010 17:42:59 Tom Lane wrote:
> Andres Freund <andres@anarazel.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