Injektáž zdrojového kódu PL/pgSQL funkcí za účelem získání seznamu používaných funkcí

Počínaje 8.4 máme k dispozici jednoduchý intergrovaný profiler, který nám umožňuje zjistit, které zákaznické funkce se používají, a které nikoliv. Ve starších verzích tuto možnost nemáme a tak si musíme pomoci jinak. Jednou z možností je vložit do zdrojového kódu funkce volání jakési registrační funkce, která zaznamená do vybrané tabulky informaci o volání funkce. Pozor - na produkčním serveru může dojít (při vyšším zatížení) ke ztrátě výkonu. Oproti integrovanému profileru můžeme zjistit registrační funkcí více informací:

Registrační funkce je ukázkou funkce, která je napsána tak, aby neselhala za žádných okolností - nechceme, aby běh aplikace byl ovlivněn profilací:

/*
 * The invocation of this function is injected to custom function's source code. It increase 
 * a function staticstic counter and logs a current query (only once per 5 minutes). 
 */
CREATE OR REPLACE FUNCTION signal_func_used(_funcid oid, _funcname varchar, _lanname varchar)
RETURNS void AS $$
BEGIN
  UPDATE user_stat_function_data 
     SET calls = calls + 1
    WHERE funcid = _funcid;
  IF NOT FOUND THEN
    -- use a advisory lock but only for this function (specified by func oid)
    PERFORM pg_advisory_lock(_funcid);
    -- chec it again - registration can be processed by other session 
    UPDATE user_stat_function_data 
       SET calls = calls + 1
      WHERE funcid = _funcid;
    IF NOT FOUND THEN
      INSERT INTO user_stat_function_data(funcid,funcname,calls,lanname)
          VALUES(_funcid, _funcname, 1, _lanname);
    END IF;
    PERFORM pg_advisory_unlock(_funcid);
  END IF;
  /* 
   * store current query to table activity_stat_data. Don't do it 
   * more than once per five minutes - it reduce a log size
   */
  IF NOT EXISTS(SELECT * 
                   FROM activity_stat_data 
                  WHERE funcid = _funcid AND query_start < current_timestamp - interval '5min') THEN
    INSERT INTO activity_stat_data 
       SELECT _funcid, query_start, usename, client_addr, current_query
          FROM pg_stat_activity 
         WHERE procpid = pg_backend_pid();
  END IF; 
  RETURN;
END;
$$ LANGUAGE plpgsql VOLATILE;

Pro hromadnou úpravu zdrojových kódů použijeme funkce:

/*
 * This function injects all custom functions in current database. It check if some 
 * function is injected or not. When not, then put to begin of function's code a call
 * of function signal_function_used.
 */
CREATE OR REPLACE FUNCTION inject_watcher()
RETURNS void AS $$
DECLARE r record; _sigfunc varchar;
BEGIN
  FOR r IN 
    SELECT p.oid, proname, nspname, prosrc, lanname
       FROM pg_proc p JOIN pg_language l ON l.oid = p.prolang
                      JOIN pg_user u ON u.usesysid =  p.proowner
		      JOIN pg_namespace n ON n.oid = p.pronamespace
      WHERE lanname IN ('sql','plpgsql') AND usename <> 'postgres' AND nspname <> 'pg_catalog'
        AND proname NOT IN ('inject_watcher','signal_func_used')
  LOOP
    IF position('signal_func_used' in r.prosrc) = 0 THEN
      IF r.lanname = 'sql' THEN
        UPDATE pg_proc 
           SET prosrc = 'SELECT signal_func_used(' || r.oid || ',' || quote_literal(r.proname) || ',' || quote_literal(r.lanname) || ');' || prosrc
          WHERE oid = r.oid;
      ELSEIF r.lanname = 'plpgsql' THEN
        _sigfunc := 'PERFORM signal_func_used(' || r.oid || ',' || quote_literal(r.proname) || ',' || quote_literal(r.lanname) || ');';
        /* regularnim vyrazem musim najit prvni begin a za nej umistit signal */
        UPDATE pg_proc 
           SET prosrc = regexp_replace(prosrc,'begin','BEGIN ' || _sigfunc, 'i')
          WHERE oid = r.oid;
      END IF;
     END IF;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT inject_watcher();

/*
SELECT test1();
SELECT * FROM test2();
SELECT test3(10);
*/
/*
 * remove injected code
 */
CREATE OR REPLACE FUNCTION remove_watcher()
RETURNS void AS $$
DECLARE r record; _sigfunc varchar;
BEGIN
  FOR r IN 
    SELECT p.oid, proname, nspname, prosrc, lanname
       FROM pg_proc p JOIN pg_language l ON l.oid = p.prolang
                      JOIN pg_user u ON u.usesysid =  p.proowner
		      JOIN pg_namespace n ON n.oid = p.pronamespace
      WHERE lanname IN ('sql','plpgsql') AND usename <> 'postgres' AND nspname <> 'pg_catalog'
        AND proname NOT IN ('inject_watcher','signal_func_used')
  LOOP
    IF position('signal_func_used' in r.prosrc) <> 0 THEN
      IF r.lanname = 'sql' THEN
        _sigfunc := 'SELECT signal_func_used(' || r.oid || ',' || quote_literal(r.proname) || ',' || quote_literal(r.lanname) || ');';
      ELSEIF r.lanname = 'plpgsql' THEN
        _sigfunc := 'PERFORM signal_func_used(' || r.oid || ',' || quote_literal(r.proname) || ',' || quote_literal(r.lanname) || ');';
      END IF;
      UPDATE pg_proc
         SET prosrc = replace(prosrc, _sigfunc, '')
          WHERE oid = r.oid;
     END IF;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

/*
SELECT remove_watcher();
*/

Zbývá pouze vytvořit logovací tabulky:

/*
 * necessary tables
 */
DROP TABLE IF EXISTS user_stat_function_data;
DROP TABLE IF EXISTS activity_stat_data;

CREATE TABLE user_stat_function_data(
  funcid oid PRIMARY KEY,
  funcname varchar,
  calls bigint,
  lanname varchar
);

CREATE TABLE activity_stat_data(
  funcid oid,
  query_start timestamp with time zone,
  usename varchar,
  client_addr inet,
  query varchar
);