Časté chyby v PL/pgSQL
Skočit na navigaci
Skočit na vyhledávání
Při auditu zdrojových kódů v PL/pgSQL se relativně často setkávám s následujícími programátorskými prohřešky:
- nepoužívání vestavěných funkcí, C-čkový styl programování - v případě jakéhokoliv interpretovaného jazyku je zásadní používat vestavěné funkce. To platí pro PL/pgSQL dvojnásob. Snažte se zmenšit počet výrazů na rozumné minimum (snaha o absolutní minimum může být na škodu - runtime PL/pgSQL rozlišuje mezi jednoduchým výrazem a obecným výrazem. Jednoduché jsou vyhodnocovány v speciálním řádově rychlejším režimu).
-- špatně IF x1 IS NOT NULL THEN s := s || x1 || ','; ELSE s := s || 'NULL,'; END IF; IF x2 IS NOT NULL THEN s := s || x2 || ',' ELSE s := s || 'NULL,'; END IF; ... -- správně s := coalesce(x1 || ',', 'NULL,') || coalesce(x2 || ',','NULL,') || ...
- Získávání měsíce, dnu, roku z datumové proměnné pomocí přetypování na text a výběrem podřetězce
--špatně m = substring(current_date::text FROM 6 FOR 2)::int; -- lépe m = substring(to_char(current_date, 'YYYY-MM-DD') FROM 6 FOR 2):: int; --správně m = EXTRACT(month FROM current_date);
- Naprosto zásadní bezpečnostní chybou je nezabezpečené dynamické SQL:
-- špatně
EXECUTE 'SELECT ' || column_name || ' FROM ' || table_name
|| ' WHERE ' || column_name || e'=\'' || some_variable || e'\''
INTO var;
-- správně
EXECUTE 'SELECT ' || quote_ident(column_name) || ' FROM ' || quote_ident(table_name)
|| ' WHERE ' || quote_ident(column_name) || '=' || quote_literal(some_variable)
INTO var;
-- správně pro 8.4 a novější
EXECUTE 'SELECT ' || quote_ident(column_name) || ' FROM ' || table_name::regclass
|| ' WHERE ' || quote_ident(column_name) || '= $1'
INTO var
USING some_variable
-- špatně
CREATE OR REPLACE FUNCTION create_table(schemaname varchar, tablename varchar)
RETURNS void AS $$
BEGIN
EXECUTE 'CREATE TABLE ' || coalesce(schemaname || '.','') || tablename;
RETRUN;
END;
$$ LANGUAGE plpgsql;
-- správně
CREATE OR REPLACE FUNCTION create_table(schemaname varchar, tablename varchar)
RETURNS void AS $$
BEGIN
EXECUTE 'CREATE TABLE ' || coalesce(quote_ident(schemaname) || '.','') || quote_ident(tablename);
RETURN;
END;
$$ LANGUAGE plpgsql;
- Drobnější, nicméně častou chybou je generování intervalu z čísla pomocí přetypování:
-- neefektivně x := (d || ' days')::interval; -- efektivně x := d * interval '1 day';
- Konečně - zbytečné použití explicitních kurzorů
-- zbytečně složité
DECLARE
curs CURSOR FOR SELECT * FROM tenk1;
r record;
BEGIN
open curs;
FETCH curs INTO r;
LOOP
EXIT WHEN NOT found;
...
FETCH curs INTO r;
END LOOP;
close curs;
END;
-- jednodušeji
DECLARE r record;
BEGIN
FOR r IN
SELECT * FROM tenk1
LOOP
...
END LOOP;
END;
- Nevhodné použití PL/pgSQL - PL/pgSQL je nevhodný jazyk pro iterace s velkým počtem cyklů obsahující modifikaci polí nebo řetězců. Např. funkce pro seřazení pole v PL/pgSQL je šikovná pro výuku PL/pgSQL ale naprosto nevhodná pro produkční prostředí. Tam bude řádově rychlejší funkce v SQL právě proto, že používá vestavěnou funkcionalitu PostgreSQL.
CREATE OR REPLACE FUNCTION array_sort(anyarray) RETURNS anyarray AS $$ SELECT ARRAY(SELECT unnest($1) ORDER BY 1); $$ LANGUAGE sql;
- Zbytečné použití SELECT INTO, zbytečné použití PL/pgSQL
-- špatně CREATE OR REPLACE FUNCTION foo(a float) RETURNS float AS $$ DECLARE result float; BEGIN SELECT 0.23 * a INTO result; return result; END; $$ LANGUAGE plpgsql; -- lépe CREATE OR REPLACE FUNCTION foo(a float) RETURNS float AS $$ BEGIN RETURN 0.23 * a; END; $$ LANGUAGE plpgsql; -- správně -- použít SQL proceduru CREATE OR REPLACE FUNCTION foo(a float) RETURNS float AS $$ SELECT 0.23 * $1; $$ LANGUAGE sql;
- Jiný příklad na stejné téma - hledání minim a rychlost pro 100 000 volání:
-- špatně (210 ms)
CREATE OR REPLACE FUNCTION min1(a int, b int)
RETURNS int AS $$
BEGIN
IF a < b THEN
RETURN a;
ELSE
RETURN b;
END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
-- lépe, ale stále špatně (190 ms)
CREATE OR REPLACE FUNCTION min1(a int, b int)
RETURNS int AS $$
BEGIN
RETURN least(a,b);
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;
-- lépe (30 ms)
CREATE OR REPLACE FUNCTION min2(a int, b int)
RETURNS int AS $$
SELECT CASE WHEN $1 < $2 THEN $1 ELSE $2 END;
$$ LANGUAGE sql;
-- nejrychlejší varianta (20 ms)
CREATE OR REPLACE FUNCTION min3(a int, b int)
RETURNS int AS $$
SELECT least($1,$2);
$$ LANGUAGE sql;
PL/pgSQL není optimalizovaný na rekurzivní volání - nepoužívejte PL/pgSQL na hluboká rekurzivní volání, používejte nerekurzivní algoritmy
CREATE OR REPLACE FUNCTION public.psqlfibnr(n integer)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE STRICT
AS $function$
DECLARE
prev1 int = 0;
prev2 int = 1;
result int = 0;
BEGIN
FOR i IN 1..n
LOOP
result := prev1 + prev2;
prev2 := prev1;
prev1 := result;
END LOOP;
RETURN result;
END;
$function$
CREATE OR REPLACE FUNCTION public.psqlfibr(n integer)
RETURNS integer
LANGUAGE plpgsql
IMMUTABLE STRICT
AS $function$
BEGIN
IF n < 2 THEN
RETURN n;
END IF;
RETURN psqlfib(n-1) + psqlfib(n-2);
END;
$function$
postgres=# select n, psqlfibnr(n)
from generate_series(0,35,5) as n;
n | psqlfibnr
----+-----------
0 | 0
5 | 5
10 | 55
15 | 610
20 | 6765
25 | 75025
30 | 832040
35 | 9227465
(8 rows)
Time: 1.178 ms
postgres=# select n, psqlfibr(n)
from generate_series(0,35,5) as n;
n | psqlfib
----+---------
0 | 0
5 | 5
10 | 55
15 | 610
20 | 6765
25 | 75025
30 | 832040
35 | 9227465
(8 rows)
Time: 282992.820 ms