http://postgres.cz/index.php?title=Injekt%C3%A1%C5%BE_zdrojov%C3%A9ho_k%C3%B3du_PL/pgSQL_funkc%C3%AD_za_%C3%BA%C4%8Delem_z%C3%ADsk%C3%A1n%C3%AD_seznamu_pou%C5%BE%C3%ADvan%C3%BDch_funkc%C3%AD&feed=atom&action=historyInjektáž zdrojového kódu PL/pgSQL funkcí za účelem získání seznamu používaných funkcí - Historie editací2024-03-29T14:08:34ZHistorie editací této stránkyMediaWiki 1.36.0http://postgres.cz/index.php?title=Injekt%C3%A1%C5%BE_zdrojov%C3%A9ho_k%C3%B3du_PL/pgSQL_funkc%C3%AD_za_%C3%BA%C4%8Delem_z%C3%ADsk%C3%A1n%C3%AD_seznamu_pou%C5%BE%C3%ADvan%C3%BDch_funkc%C3%AD&diff=469&oldid=previmported>Pavel v 28. 8. 2014, 18:532014-08-28T18:53:10Z<p></p>
<p><b>Nová stránka</b></p><div>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í:<br />
<br />
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í:<br />
<pre><br />
/*<br />
* The invocation of this function is injected to custom function's source code. It increase <br />
* a function staticstic counter and logs a current query (only once per 5 minutes). <br />
*/<br />
CREATE OR REPLACE FUNCTION signal_func_used(_funcid oid, _funcname varchar, _lanname varchar)<br />
RETURNS void AS $$<br />
BEGIN<br />
UPDATE user_stat_function_data <br />
SET calls = calls + 1<br />
WHERE funcid = _funcid;<br />
IF NOT FOUND THEN<br />
-- use a advisory lock but only for this function (specified by func oid)<br />
PERFORM pg_advisory_lock(_funcid);<br />
-- chec it again - registration can be processed by other session <br />
UPDATE user_stat_function_data <br />
SET calls = calls + 1<br />
WHERE funcid = _funcid;<br />
IF NOT FOUND THEN<br />
INSERT INTO user_stat_function_data(funcid,funcname,calls,lanname)<br />
VALUES(_funcid, _funcname, 1, _lanname);<br />
END IF;<br />
PERFORM pg_advisory_unlock(_funcid);<br />
END IF;<br />
/* <br />
* store current query to table activity_stat_data. Don't do it <br />
* more than once per five minutes - it reduce a log size<br />
*/<br />
IF NOT EXISTS(SELECT * <br />
FROM activity_stat_data <br />
WHERE funcid = _funcid AND query_start < current_timestamp - interval '5min') THEN<br />
INSERT INTO activity_stat_data <br />
SELECT _funcid, query_start, usename, client_addr, current_query<br />
FROM pg_stat_activity <br />
WHERE procpid = pg_backend_pid();<br />
END IF; <br />
RETURN;<br />
END;<br />
$$ LANGUAGE plpgsql VOLATILE;<br />
</pre><br />
Pro hromadnou úpravu zdrojových kódů použijeme funkce:<br />
<pre><br />
/*<br />
* This function injects all custom functions in current database. It check if some <br />
* function is injected or not. When not, then put to begin of function's code a call<br />
* of function signal_function_used.<br />
*/<br />
CREATE OR REPLACE FUNCTION inject_watcher()<br />
RETURNS void AS $$<br />
DECLARE r record; _sigfunc varchar;<br />
BEGIN<br />
FOR r IN <br />
SELECT p.oid, proname, nspname, prosrc, lanname<br />
FROM pg_proc p JOIN pg_language l ON l.oid = p.prolang<br />
JOIN pg_user u ON u.usesysid = p.proowner<br />
JOIN pg_namespace n ON n.oid = p.pronamespace<br />
WHERE lanname IN ('sql','plpgsql') AND usename <> 'postgres' AND nspname <> 'pg_catalog'<br />
AND proname NOT IN ('inject_watcher','signal_func_used')<br />
LOOP<br />
IF position('signal_func_used' in r.prosrc) = 0 THEN<br />
IF r.lanname = 'sql' THEN<br />
UPDATE pg_proc <br />
SET prosrc = 'SELECT signal_func_used(' || r.oid || ',' || quote_literal(r.proname) || ',' || quote_literal(r.lanname) || ');' || prosrc<br />
WHERE oid = r.oid;<br />
ELSEIF r.lanname = 'plpgsql' THEN<br />
_sigfunc := 'PERFORM signal_func_used(' || r.oid || ',' || quote_literal(r.proname) || ',' || quote_literal(r.lanname) || ');';<br />
/* regularnim vyrazem musim najit prvni begin a za nej umistit signal */<br />
UPDATE pg_proc <br />
SET prosrc = regexp_replace(prosrc,'begin','BEGIN ' || _sigfunc, 'i')<br />
WHERE oid = r.oid;<br />
END IF;<br />
END IF;<br />
END LOOP;<br />
RETURN;<br />
END;<br />
$$ LANGUAGE plpgsql;<br />
<br />
SELECT inject_watcher();<br />
<br />
/*<br />
SELECT test1();<br />
SELECT * FROM test2();<br />
SELECT test3(10);<br />
*/<br />
/*<br />
* remove injected code<br />
*/<br />
CREATE OR REPLACE FUNCTION remove_watcher()<br />
RETURNS void AS $$<br />
DECLARE r record; _sigfunc varchar;<br />
BEGIN<br />
FOR r IN <br />
SELECT p.oid, proname, nspname, prosrc, lanname<br />
FROM pg_proc p JOIN pg_language l ON l.oid = p.prolang<br />
JOIN pg_user u ON u.usesysid = p.proowner<br />
JOIN pg_namespace n ON n.oid = p.pronamespace<br />
WHERE lanname IN ('sql','plpgsql') AND usename <> 'postgres' AND nspname <> 'pg_catalog'<br />
AND proname NOT IN ('inject_watcher','signal_func_used')<br />
LOOP<br />
IF position('signal_func_used' in r.prosrc) <> 0 THEN<br />
IF r.lanname = 'sql' THEN<br />
_sigfunc := 'SELECT signal_func_used(' || r.oid || ',' || quote_literal(r.proname) || ',' || quote_literal(r.lanname) || ');';<br />
ELSEIF r.lanname = 'plpgsql' THEN<br />
_sigfunc := 'PERFORM signal_func_used(' || r.oid || ',' || quote_literal(r.proname) || ',' || quote_literal(r.lanname) || ');';<br />
END IF;<br />
UPDATE pg_proc<br />
SET prosrc = replace(prosrc, _sigfunc, '')<br />
WHERE oid = r.oid;<br />
END IF;<br />
END LOOP;<br />
RETURN;<br />
END;<br />
$$ LANGUAGE plpgsql;<br />
<br />
/*<br />
SELECT remove_watcher();<br />
*/<br />
</pre><br />
Zbývá pouze vytvořit logovací tabulky:<br />
<pre><br />
/*<br />
* necessary tables<br />
*/<br />
DROP TABLE IF EXISTS user_stat_function_data;<br />
DROP TABLE IF EXISTS activity_stat_data;<br />
<br />
CREATE TABLE user_stat_function_data(<br />
funcid oid PRIMARY KEY,<br />
funcname varchar,<br />
calls bigint,<br />
lanname varchar<br />
);<br />
<br />
CREATE TABLE activity_stat_data(<br />
funcid oid,<br />
query_start timestamp with time zone,<br />
usename varchar,<br />
client_addr inet,<br />
query varchar<br />
);<br />
</pre></div>imported>Pavel