imported>Pavel v 26. 3. 2013, 11:16

2013-03-26T11:16:41Z

Nová stránka

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).
<pre>
-- š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,') || ...
</pre>

*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
<pre>
--š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);
</pre>
*''Naprosto zásadní bezpečnostní chybou je nezabezpečené dynamické SQL'':
<pre>
-- š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;
</pre>
*Drobnější, nicméně častou chybou je generování intervalu z čísla pomocí přetypování:
<pre>
-- neefektivně
x := (d || ' days')::interval;

-- efektivně
x := d * interval '1 day';
</pre>
* Konečně - zbytečné použití explicitních kurzorů
<pre>
-- 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;
</pre>
*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.
<pre>
CREATE OR REPLACE FUNCTION array_sort(anyarray)
RETURNS anyarray AS $$
SELECT ARRAY(SELECT unnest($1) ORDER BY 1);
$$ LANGUAGE sql;
</pre>
* Zbytečné použití SELECT INTO, zbytečné použití PL/pgSQL
<pre>
-- š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;
</pre>
*Jiný příklad na stejné téma - hledání minim a rychlost pro 100 000 volání:
<pre>
-- š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;
</pre>
PL/pgSQL není optimalizovaný na rekurzivní volání - nepoužívejte PL/pgSQL na hluboká rekurzivní volání, používejte nerekurzivní algoritmy
<pre>
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
</pre>

Časté chyby v PL/pgSQL - Historie editací

imported>Pavel v 26. 3. 2013, 11:16