8.4.1 strange GiST (btree_gist?) messages + index row size error (possible BUG, test case + test data)

From: Sergey Konoplev <gray(dot)ru(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: 8.4.1 strange GiST (btree_gist?) messages + index row size error (possible BUG, test case + test data)
Date: 2009-10-26 12:49:02
Message-ID: c3a7de1f0910260549h7698d57ci40f25eca5e692227@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi, All

I faced this odd situation when I was migrating my data from 8.3.7 to
8.4.1. After setting up instance, applying schema dump w/o and indexes
and loading data I was trying to create this indexes and got a lot of
multiply messages "picksplit method for column 2 of index ... doesn't
support secondary split" after each gist index containing 2+ columns.
Finally I got this message "index row requires 10440 bytes, maximum
size is 8191" after creation of one complex index.

Test-case:

1. Install PG 8.4.1 + btree_gist, set log_statement = 'all', create test_db

2. Create test table

CREATE TABLE test_table
(
obj_id bigint NOT NULL,
obj_status_did smallint NOT NULL DEFAULT 5,
obj_created timestamp with time zone NOT NULL DEFAULT now(),
obj_main_pic_obj_id bigint,
obj_tsvector tsvector NOT NULL DEFAULT ''::tsvector,
person_photo_is_best boolean NOT NULL DEFAULT false,
person_vislvl smallint NOT NULL DEFAULT 9,
CONSTRAINT pk_test_table PRIMARY KEY (obj_id)
);

3. Load a data into the table

Data dump is here http://drop.io/rdccygi (it was created with COPY
(SELECT...) TO '...' using psql from 8.3.7)

COPY test_table FROM '/tmp/data.dump';

4. Try to create this indexes

CREATE INDEX i_test_table__created_tsvector ON test_table USING gist
(obj_created, obj_tsvector) WHERE obj_status_did = 1;

CREATE INDEX i_test_table__tsvector_vislvl_by_photo_created ON
test_table USING btree (obj_tsvector, person_vislvl,
(COALESCE(person_photo_is_best::integer, 0)) DESC,
sign(COALESCE(obj_main_pic_obj_id, 0::bigint)::double precision) DESC,
obj_created DESC) WHERE obj_status_did = 1;

And you will see something like this http://drop.io/5tla8sg

p.s. One thing I have forgotten to write - I tried it on Ubuntu 9.04,
PG was built from sources.

--
Regards,
Sergey Konoplev

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2009-10-26 12:53:52 Re: Endgame for all those SELECT FOR UPDATE changes: fix plan node order
Previous Message Dave Page 2009-10-26 08:56:22 Re: License clarification: BSD vs MIT