Hlavní navigace

PostgreSQL : Zákěřnost SQL update

1. 11. 2016 12:18 (aktualizováno) | Michal Šalko

Zákeřnice je hmyz, který se živí telnimi těkutinami živočichů. Aby přežili, mají různé prapodivné strategie, jak se nakrmit. Obecně, ale zákeřností nazýváme situaci, kdy něco předpokládáme a ono to dopadne úplně jinak. Tímto termínem bychom mohli nazvat i jeden typ SQL update příkazů. Nejenom v PostrgeSQL !

Máme dvě tabulky t_month (sběrná tabulka s daty po měsících) a tabulku t_sum2 (která je agregační  tabulka dat z t_month).

create table t_month
 (id1 integer not null,
  month date not null,
  data1_month integer not null,
  data2_month integer not null);

create table t_sum2
(id1 integer primary key,
 data1_sum integer,
 data2_sum integer)

  insert into t_month
  (id1,month,data1_month,data2_month)
  values
  (1,'2016_01_01',10,0),
  (2,'2016_01_01',12,45),
  (1,'2016_02_01',0,40),
  (2,'2016_02_01',10,5),
  (2,'2016_02_01',13,15);

insert into t_sum2
 (id1,data1_sum,data2_sum)
 values
 (1,123,45),(2,12,33),(3,44,15); 

Co se stane, když se pokusíme provést agregační update nad daty tabulky t_sum2. Nestačíme se divit, když příjdeme o data pro id1=3.

update t_sum2 s
  set (data1_sum,data2_sum) = (
  select sum(data1_month) + data1_sum, sum(data2_month) + data2_sum
  from t_month m
  where s.id1 = m.id1)
  returning id1,data1_sum,data2_sum;

 id1 | data1_sum | data2_sum
-----+-----------+-----------
   1 |       133 |        85
   2 |        47 |        98
   3 |           | 

Záchrana je nejdříve v tom, že se využijí nástroje SQL a v rámci toho se dodefinují podmínky existence záznamu v tabulce t_sum takto :

create table t_sum
(id1 integer primary key,
 data1_sum integer not null default 0,
 data2_sum integer not null default 0);

insert into t_sum
 (id1,data1_sum,data2_sum)
 values
 (1,123,45),(2,12,33),(3,44,15);

update t_sum s
  set (data1_sum,data2_sum) = (
  select sum(data1_month) + data1_sum, sum(data2_month) + data2_sum
  from t_month m
  where s.id1 = m.id1)
  returning id1,data1_sum,data2_sum;

ERROR:  null value in column "data1_sum" violates not-null constraint
DETAIL:  Failing row contains (3, null, null).

 Problém sice není vyřešen, ale vhodná datová struktúra tabulky nás upozornila, že se pokoušíme o nepovolenou operaci v rámci integrity dat.

Problém se zákerným updatem vysvětlí „query plan“, který nám řekne, že se nejdříve provedl scan nad tabulkou t_sum (připravil hodnoty pro data.._month), pak se provedla agregace nad tabulkou t_month dle nastaveného filtru s.id1 = m.id1. Takže SQL se snažilo přiřadit agregovanou hodnotu datum. No a v určitém případě, to vracelo hodnotu null. A null + hodnota = null.

explain update t_sum s
  set (data1_sum,data2_sum) = (
  select sum(data1_month) + data1_sum, sum(data2_month) + data2_sum
  from t_month m
  where s.id1 = m.id1)
  returning id1,data1_sum,data2_sum;

                                  QUERY PLAN
------------------------------------------------------------------------------
 Update on t_sum s  (cost=0.00..67738.00 rows=2040 width=18)
   ->  Seq Scan on t_sum s  (cost=0.00..67738.00 rows=2040 width=18)
         SubPlan 1 (returns $3,$4)
           ->  Aggregate  (cost=33.17..33.19 rows=1 width=8)
                 ->  Seq Scan on t_month m  (cost=0.00..33.13 rows=9 width=8)
                       Filter: (s.id1 = id1) 

V podstatě existují 3 řešení tohoto problému:

1. řešení NULL hodnota se dá na hodnotu 0.

update t_sum s
  set (data1_sum,data2_sum) = (
  select coalesce(sum(data1_month),0) + data1_sum, coalesce(sum(data2_month),0) + data2_sum
  from t_month m
  where s.id1 = m.id1)
  returning id1,data1_sum,data2_sum; 

Toto řešení pracuje se všemi záznami s tabulky t_sum. A někdy při více záznamech v t_sum může být delší doba vykonávaní.

2. řešení Omezí se výběr hodnot pro update – klasicky.

update t_sum s
  set (data1_sum,data2_sum) = (
  select sum(data1_month) + s.data1_sum, sum(data2_month) + s.data2_sum
  from t_month m
  where s.id1 = m.id1)
where exists (select 1 from t_month m
 where s.id1 = m.id1)
  returning id1,data1_sum,data2_sum; 

Query plan pak je tento :

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Update on t_sum s  (cost=37.63..33926.38 rows=1020 width=16)
   ->  Hash Join  (cost=37.63..33926.38 rows=1020 width=16)
         Hash Cond: (s.id1 = m.id1)
         ->  Seq Scan on t_sum s  (cost=0.00..30.40 rows=2040 width=10)
         ->  Hash  (cost=35.13..35.13 rows=200 width=10)
               ->  HashAggregate  (cost=33.13..35.13 rows=200 width=10)
                     Group Key: m.id1
                     ->  Seq Scan on t_month m  (cost=0.00..28.50 rows=1850 width=10)
         SubPlan 1 (returns $1,$2)
           ->  Aggregate  (cost=33.17..33.18 rows=1 width=8)
                 ->  Seq Scan on t_month m_1  (cost=0.00..33.13 rows=9 width=8)
                       Filter: (s.id1 = id1) 

Query plan je složitější o agregaci m.id1, která omezuje vybírané řádky z t_sum pro update. Takže agregace t_month se provádí 2×.

Musím se přiznat, že kdysi, když jsem dělal v Oraclu, jsem tuto konstrukci používal často.

3. řešení Omezení se výběr hodnot pro update – jednou agregaci přes t_month

Rozdíl a dost podstatný je v tom, že u toho update se provede pouze jedna agregace ze které se i odvodí podmínka omezení.

update t_sum s
  set (data1_sum,data2_sum) = (t.data1_sum + s.data1_sum, t.data2_sum +s.data2_sum)
  from (select id1,sum(data1_month) as data1_sum, sum(data2_month) as data2_sum
  from t_month m
  group by id1) t
  where t.id1 = s.id1
  returning s.id1,s.data1_sum,s.data2_sum; 

Query plan je tento :

                                      QUERY PLAN
--------------------------------------------------------------------------------------
 Update on t_sum s  (cost=98.28..106.03 rows=200 width=70)
   ->  Hash Join  (cost=98.28..106.03 rows=200 width=70)
         Hash Cond: (t.id1 = s.id1)
         ->  Subquery Scan on t  (cost=42.38..46.38 rows=200 width=64)
               ->  HashAggregate  (cost=42.38..44.38 rows=200 width=12)
                     Group Key: m.id1
                     ->  Seq Scan on t_month m  (cost=0.00..28.50 rows=1850 width=12)
         ->  Hash  (cost=30.40..30.40 rows=2040 width=10)
               ->  Seq Scan on t_sum s  (cost=0.00..30.40 rows=2040 width=10) 

Proto je takto sestavený update nejrychlejší ze všech tří.

Závěrem :

Pro mně z toho plynou tři poučení:

  1. Věnovat čas datové integritě pomocí nástrojů, které databáze poskytuje.
  2. Pokud je to možno ladit SQL příkaz buďto přes otevřenou transakci nebo na vzorce dat.
  3. Dát si pozor na zákeřný update.

Přidávat nové názory je zakázáno.