Časté chyby v PL/pgSQL

Z PostgreSQL
Verze z 31. 10. 2011, 16:32, kterou vytvořil Pavel (diskuse | příspěvky)

(rozdíl) ← Starší verze | zobrazit aktuální verzi (rozdíl) | Novější verze → (rozdíl)
Přejít na: navigace, hledá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;