PostgreSQL : Zákěřnost SQL update
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í:
- Věnovat čas datové integritě pomocí nástrojů, které databáze poskytuje.
- Pokud je to možno ladit SQL příkaz buďto přes otevřenou transakci nebo na vzorce dat.
- Dát si pozor na zákeřný update.
ČLÁNKY DO MAILU