BUG #9175: REINDEX on functional index fails
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
ia.shumilova@gmail.com writes:
-- 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;
I don't have a whole lot of sympathy for a bug report that involves
a function claimed to be IMMUTABLE when that marking is a lie.
This function selects from tree_detail, so it can at best be claimed
to be STABLE; which means you cannot use it in an index.
I think the proximate cause of the reported error is that once the
index exists, the query in the function tries to use it; and then
that usage will fail when the index is in mid-rewrite. It might
be worth trying to make that fail more cleanly, if index functions
that depend on selecting from their table were a supported thing;
but they aren't and never will be.
You might be able to get the results you want by including the
taxonomy_string() function in a materialized view over the table.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs