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  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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 по дате отправления:

Предыдущее
От: garyjob@krake.io
Дата:
Сообщение: BUG #9168: Invalid JSON output generated by SQL statement
Следующее
От: Tom Lane
Дата:
Сообщение: Re: BUG #9175: REINDEX on functional index fails