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
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 |