SQL Puzzler : Lets RULE!
Partitioning lze použít jako elegantní řešení široké škály problémů - distribuci tabulky na více disků (pokud z nějakých důvodů nechcete použít RAID pole), efektivní mazání archivních dat (bez DELETE), atd. Automaticky ho lze realizovat pomocí triggerů nebo pravidel. Podívejme se na puzzler související s partitioningem realizovaným pomocí RULE.
Uvažujme tabulku s následující jednoduchou strukturu
CREATE SEQUENCE base_sequence;
CREATE TABLE base_table (
id INTEGER PRIMARY KEY,
value TEXT
);
a řekněme že ji chceme rozdělit na dvě části obsahující záznamy se sudými resp. lichými ID. Vytvoříme tady dvě partitions
CREATE TABLE odd_table (
PRIMARY KEY (id),
CHECK (id % 2 = 1)
) INHERITS (base_table);
CREATE TABLE even_table (
PRIMARY KEY (id),
CHECK (id % 2 = 0)
) INHERITS (base_table);
a dvě pravidla která budou vkládané záznamy přesměrovávat do příslušných partitions
CREATE RULE even_rule AS ON INSERT TO base_table
WHERE (nextval('base_sequence') % 2 = 0) DO INSTEAD INSERT INTO
even_table (id, value) VALUES (currval('base_sequence'), NEW.value);
CREATE RULE odd_rule AS ON INSERT TO base_table
WHERE (currval('base_sequence') % 2 = 1) DO INSTEAD INSERT INTO
odd_table (id, value) VALUES (currval('base_sequence'), NEW.value);
Tato pravidla si zaslouží stručný komentář:
- PostgreSQL vždy vyhodnocuje všechna pravidla (resp. příšlušné WHERE podmínky), a to v pořadí dle názvu pravidla. To znamená že pravidlo "even_rule" je spuštěno jako první, a následně je spuštěno pravidlo "odd_rule."
- Funkce "nextval" je použita pouze ve WHERE podmínce even_rule pravidla (tj. prvního spouštěného), a vygenerovaná hodnota je dále používána pomocí funkce "currval."
V tuto chvíli tedy máme tabulku "base_table" která zůstane prázdná, a tabulky "odd_table" resp. "even_table" do kterých budou řádky rozdělovány dle hodnoty ve sloupci "id" (načítané automaticky ze sekvence). Do tabulky "even_table" budou vkládány řádky se sudými hodnotami, a do tabulky "odd_table" řádky s lichými hodnotami.
Máme také dvě jednoduchá pravidla která která toto rozdělování řádek zabezpečují.
Spusťme tedy dva následující INSERT příkazy
INSERT INTO base_table (value) VALUES ('prvni radek');
INSERT INTO base_table (value) VALUES ('druhy radek');
a otázkou pochopitelně je co je jejich výsledkem. Jako v předchozích puzzlerech i tentokrát vám dám na výběr z několika možností:
- Všechny záznamy budou vloženy do základní tabulky (base_table), tj. žádný partitioning proveden nebude. Tabulka bude obsahovat řádky [1, 'prvni radek'] a [2, 'druhy radek'].
- Všechny záznamy budou vloženy do tabulky "even_table" která bude obsahovat řádky [2, 'prvni radek'] a [4, 'druhy radek'].
- Všechny záznamy budou vloženy do tabulky "odd_table" která bude obsahovat řádky [1, 'prvni radek'] a [3, 'druhy radek'].
- Dojde k chybě, ani jeden INSERT se nepovede.
I tentokrát je odpověď skryta - zobrazí se až po kliknutí na nadpis "Odpověď".
Odpověď
Správná odpověď je (b) - oba řádky budou vloženy do tabulky "even_table" která bude obsahovat řádky
2 | prvni radek 4 | druhy radek
a naopak tabulka "odd_table" která by měla obsahovat řádky s lichým "id" je prázdná. Otázkou ale zůstává proč se tak děje?
Podezřelé také je že že hodnoty ve sloupci "id" se zvyšují o 2 - vždyť vytvořená pravidla obsahují pouze jediné volání metody "nextval" takže by se hodnota měla zvyšovat pouze o 1.
Podívejme se na execution plan, třeba nám naznačí kde je zakopán pes
fuzzy-new=# explain insert into base_table(value) values ('treti');
QUERY PLAN
----------------------------------------------------------------------
Result (cost=0.02..0.03 rows=1 width=0)
One-Time Filter:
((((nextval('base_sequence'::regclass) % 2::bigint) = 0) IS NOT TRUE)
AND
(((currval('base_sequence'::regclass) % 2::bigint) = 1) IS NOT TRUE))
Result (cost=0.01..0.02 rows=1 width=0)
One-Time Filter:
((nextval('base_sequence'::regclass) % 2::bigint) = 0)
Result (cost=0.01..0.02 rows=1 width=0)
One-Time Filter:
((currval('base_sequence'::regclass) % 2::bigint) = 1)
(8 rows)
Jak je vidět, kromě dvou očekávaných větví (jedna pro každé pravidlo) obsahuje execution plan ještě třetí větev, automaticky doplněnou PostgreSQL planerem. Tato větev je uvedena jako první, vkládá řádky do "základní" tabulky, a její podmínka v podstatě znamená "není splněna podmínka žádného jiného pravidla." Což v tomto případě vlastně znamená podmínku
(nextval('base_sequence') % 2 != 0) AND
(currval('base_sequence') % 2) != 1)
a to je právě to další spuštění (volatile) funkce nextval. Díky tomu hodnota ve sloupci "id" skáče o 2 a všechny řádky se vkládají do "even_table."
Jak ale tento problém vyřešit?
První možností je dodefinování pravidla bez podmínky, která se postará o případy kdy není splněna ani jedna z podmínek dalších pravidel. Pokud jste si jisti že se podmínky pokrývají všechny možné varianty, a že tedy pro každý INSERT existuje odpovídající pravidlo, můžete vytvořít pravidlo které nic nedělá, například
CREATE OR REPLACE RULE empty_rule AS ON INSERT TO base_table DO INSTEAD NOTHING;
Toto pravidlo se sice nikdy nespustí, ale zajistí že PostgreSQL nebude automaticky doplňovat žádné pravidlo a nebude tak vznikat žádné neočekávané spuštění funkce nextval.
Druhou možností je nepoužívat v pravidlech funkci nextval, ale ID pro nový řádek si nejdříve explicitně načíst (právě zavoláním funkce nextval), a v pravidlech používat jen a pouze funkci currval. Tj. například pravidla definovat takto
CREATE RULE even_rule AS ON INSERT TO base_table
WHERE (currval('base_sequence') % 2 = 0) DO INSTEAD INSERT INTO
even_table (id, value) VALUES (currval('base_sequence'), NEW.value);
CREATE RULE odd_rule AS ON INSERT TO base_table
WHERE (currval('base_sequence') % 2 = 1) DO INSTEAD INSERT INTO
odd_table (id, value) VALUES (currval('base_sequence'), NEW.value);
a při vkládání řádků postupovat takto
SELECT nextval('base_sequence');
INSERT INTO base_table (value) VALUES ('prvni radek');
Případně lze pravidla od sekvencí úplně odstínit
CREATE RULE even_rule AS ON INSERT TO base_table WHERE (NEW.id % 2 = 0) DO INSTEAD INSERT INTO even_table (id, value) VALUES (NEW.id, NEW.value); CREATE RULE odd_rule AS ON INSERT TO base_table WHERE (NEW.id % 2 = 1) DO INSTEAD INSERT INTO odd_table (id, value) VALUES (NEW.id, NEW.value);
což ale vyžaduje poněkud složitější logiku při vkládání, protože je nutno řádek načíst do proměnné (například do Javy nebo PHP). V PHP by (pro jednoduchost vynechávám ošetření chybových stavů) to mohlo vypadat například takto:
<?php
// načtení hodnoty ze sekvence do proměnné
$res = pg_query("SELECT nextval('base_sequence') AS id");
$row = pg_fetch_assoc($res);
// použití hodnoty v insertu
pg_query("INSERT INTO base_table (id, value) " .
"VALUES (" . $row['id'] ."'prvni radek')");
?>
Podotýkám že nelze prostě vkládat řádky příkazem
INSERT INTO base_table (id, value)
VALUES (nextval('base_sequence'), 'nova hodnota');
protože volání metody "nextval" nebude vyhodnoceno na začátku vyhodnocení insertu, ale bude dosazeno do pravidel - problém se tedy nevyřeší, ale naopak se ještě zhorší (hodnoty budou skákat ještě více protože funkce "nextval" bude znovu vyhodnocena pro každý výskyt NEW.id).
