BUG #9175: REINDEX on functional index fails
От | ia.shumilova@gmail.com |
---|---|
Тема | BUG #9175: REINDEX on functional index fails |
Дата | |
Msg-id | 20140210121620.2717.2555@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #9175: REINDEX on functional index fails
|
Список | pgsql-bugs |
The following bug has been logged on the website: Bug reference: 9175 Logged by: Irina Email address: ia.shumilova@gmail.com PostgreSQL version: 9.3.2 Operating system: Ubuntu Server 13.10 Description: -- steps to reproduce: -- first of all we should create structure: -- some table that describes trees CREATE TABLE tree_master ( id serial NOT NULL, title character varying(255), CONSTRAINT tree_master_pkey PRIMARY KEY (id) ); -- table describes dependent nested set CREATE TABLE tree_detail ( id serial NOT NULL, tree_master_id integer NOT NULL, -- some entry that contains structure entry value, taxonomy for example tax_entry character varying(64) NOT NULL, -- nested set fields _left integer, _right integer, _level integer, CONSTRAINT tree_detail_pkey PRIMARY KEY (id), CONSTRAINT tree_detail_tree_master_id_fkey FOREIGN KEY (tree_master_id) REFERENCES tree_master (id) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE ); -- function for tree structure assembling -- outputs something like 'level0/level1/level2/leaf' CREATE OR REPLACE FUNCTION taxonomy_string(tree_detail) RETURNS text AS $BODY$ SELECT COALESCE(string_agg(tax_entry, '/'), '') FROM ( SELECT tax_entry FROM tree_detail WHERE _left <= $1._left AND _right >= $1._right AND tree_master_id = $1.tree_master_id ORDER BY _level ) u $BODY$ LANGUAGE sql IMMUTABLE; --next, let's add some data -- master item INSERT INTO tree_master VALUES (1, 'tree #1'); -- detail items INSERT INTO tree_detail VALUES (1, 1, '1-level-0', 1, 8, 0), (2, 1, '1-level-1', 2, 3, 1), (3, 1, '2-level-1', 4, 7, 1), (4, 1, '1-level-2', 5, 6, 2); -- ok, everything work as expected this moment -- we can check it by query -- SELECT t.taxonomy_string FROM tree_detail t ORDER BY t.id -- but(!) if we want to add index on this page -- by query CREATE INDEX tree_detail_tree_master_id_taxonomy_string_idx ON tree_detail USING btree (tree_master_id, taxonomy_string(tree_detail.*)); -- and if we want to REINDEX this index by query REINDEX INDEX tree_detail_tree_master_id_taxonomy_string_idx; -- we will got error like this: -- -- ERROR: could not read block 0 in file "base/12070/16407": read only 0 of 8192 bytes -- CONTEXT: SQL function "taxonomy_string" during startup -- there are no faults in 9.2 branch -- server package: 9.3.2-1ubuntu1: amd64
В списке pgsql-bugs по дате отправления: