Find out what on what function depends an index

[Andreas Ulbrich <andreas.ulbrich@xxxxxxxxxxxxxx>]

Salvete.

If I have an expression based index (a fucntion call) then the index 
will become corrupt, if the function is changed. As the function 
developer, I do not know, who uses the function for an index, and as the 
user of the function in an index I do not know if the function is 
changed. So the idea is: Have an event trigger ON ddl_command_end to do 
a REINDEX; see the attached example.

The question is the todo in the script: Is there a way to find out what 
indexes depends on what function?

thanks
Andreas


/* $Id: t_reindexfn.sql,v 1.1 2015/05/17 08:42:31 andreas Exp $ */
\set  ON_ERROR_ROLLBACK ON
\pset pager OFF

\set nrows 100
BEGIN;
  CREATE FUNCTION reindex_function_index() RETURNS event_trigger AS $$
  DECLARE
    obj RECORD;
    idx_name TEXT;
  BEGIN
    FOR obj IN SELECT * FROM pg_event_trigger_ddl_commands() 
                WHERE object_type = 'function' LOOP
-- TODO looking for a generic way to determine what indexes depends on what functions
      IF obj.object_identity = 'test.modix(integer)' THEN
      BEGIN
        idx_name := 'rxftest_modix_idx';
        EXECUTE 'REINDEX INDEX ' || idx_name;
        RAISE NOTICE '% on % rebuild index %', obj.command_tag, obj.object_identity, idx_name;
      EXCEPTION
        WHEN SQLSTATE '42P01' THEN
          RAISE DEBUG 'INDEX % not exists in reindex_function_index().', idx_name;
      END;
      END IF;
    END LOOP;
  END$$ LANGUAGE plPgSQL;

  SET LOCAL ROLE super;
-- /* Begin comment
    CREATE EVENT TRIGGER reindex_function_index ON ddl_command_end
           EXECUTE PROCEDURE reindex_function_index();
-- */ end comment
  RESET ROLE;

  CREATE TABLE rxftest AS
         SELECT nr FROM Generate_Series(1, :nrows) nr;
  -- TABLE rxftest;

  CREATE OR REPLACE FUNCTION modix(INTEGER) RETURNS INTEGER AS
  $$ VALUES($1 % 7)$$ LANGUAGE SQL IMMUTABLE STRICT;

  CREATE INDEX ON rxftest(modix(nr));
  ANALYZE rxftest;

  \d+ rxftest
  \di+ rxftest_modix_idx
  \d+ rxftest_modix_idx

  EXPLAIN SELECT * FROM rxftest WHERE modix(nr) = 0;
  SELECT nr, modix(nr) FROM rxftest WHERE modix(nr) = 0;

  CREATE OR REPLACE FUNCTION modix(INTEGER) RETURNS INTEGER AS
  $$ VALUES($1 % 7 - 3)$$ LANGUAGE SQL IMMUTABLE STRICT;

  EXPLAIN SELECT * FROM rxftest WHERE modix(nr) = 0;
  SELECT nr, modix(nr) FROM rxftest WHERE modix(nr) = 0;

  --SELECT * FROM pg_index;
  --SELECT * FROM pg_indexes;

  DROP FUNCTION modix(INTEGER) CASCADE;
ROLLBACK;
\pset pager ON
\set  ON_ERROR_ROLLBACK interactive

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general