BUG #9175: REINDEX on functional index fails

From: ia(dot)shumilova(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #9175: REINDEX on functional index fails
Date: 2014-02-10 12:16:20
Message-ID: 20140210121620.2717.2555@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 9175
Logged by: Irina
Email address: ia(dot)shumilova(at)gmail(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Alexey Bashtanov 2014-02-10 12:33:43 index scan is performed when index-only scan is possible (partial index)
Previous Message Heikki Linnakangas 2014-02-10 09:08:34 Re: BUG #9169: Replica (v 9.3.2) crashed with "PANIC: WAL contains references to invalid pages"