PostgreSQL: Transakce v PL/pgsql - Jak jsem se s tím vypořádal
Každý kdo přešel od jiných velikých databází a programoval v procedurálním jazyku se pokusil v PL/pgsql vytvořit transakci tak narazil. PL/pgsql totiž transakce nepovoluje. Spíše každé spuštění funkce je jednou velikou transakcí.
Každý, kdo potřeboval přenést velký objem dat pomocí PL/pgsql narazil na problém. Jak to sakra udělat, abych data přenesl a nealkoval veškeré zdroje databáze pro sebe ? Vše v jedné obrovské dlouho trvající transakci, která před dokončením spadne, protože v nějakém zapomenutém číselníku, chybí nějaká ta hodnota. Řešení je prosté, rozsekat celou transformaci do dílčích transakcí ty přes shell script a pgsql je spouštět. Dobré, ale mimo databázi. Lze to i jinak.
Jak jsem se vypořádal s transakcemi v PL/pgsql.
Asi za všeho si demonstrujme příklad:
create table t_data
(id serial primary key,
txt text unique);
create table t_log
(id serial primary key,
txt text); Vytvoříme dvě tabulky. Do jedné ‚d_data‘ budou téct data a do druhé ‚t_log‘ logy operací. Abychom simulovali chybu vložení dat, tak sloupec t_data.txt musí mít jedinenčné hodnoty.
Proveďme vše klasicky bez ošetření chybových stavů. Funkce ná vrátí chybu a nevloží se žádná data.
-- Definice funkce, ktera vlozi data do tabulky t_data a uspesne volani zapise do logu.
CREATE OR REPLACE FUNCTION f_test_ins2(pdata text)
RETURNS boolean AS
$BODY$
DECLARE
v_exc_context TEXT;
pocetr BIGINT;
BEGIN
-- Provedeni akce
INSERT INTO t_data(txt)
VALUES
(pdata);
-- Zaznam do logu
GET DIAGNOSTICS pocetr = ROW_COUNT;
INSERT INTO t_log(txt)
VALUES ('Bylo vlozeno '||pocetr::TEXT||' radku.');
return true;
END;
$BODY$
LANGUAGE plpgsql;
-- Testovací funkce
CREATE OR REPLACE FUNCTION f_test_ins2()
RETURNS boolean AS
$BODY$
DECLARE
lerr TEXT;
BEGIN
select * from f_test_ins2('ab') into lerr;
select * from f_test_ins2('ab') into lerr;
return lerr;
END;
$BODY$
LANGUAGE plpgsql;
-- Vymazání testovacích dat
truncate table t_log;
truncate table t_data;
-- Spuštění funkce
select * from f_test_ins2();
-- Funkce vypíše tuto chybu
ERROR: duplicate key value violates unique constraint "t_data_txt_key"
DETAIL: Key (txt)=(ab) already exists.
CONTEXT: SQL statement "INSERT INTO t_data(txt)
VALUES
(pdata)"
PL/pgSQL funkce f_test_ins2(text) řádek 7 na SQL příkaz
SQL statement "select * from f_test_ins2('ab')"
PL/pgSQL funkce f_test_ins2() řádek 6 na SQL příkaz
********** Chyba **********
ERROR: duplicate key value violates unique constraint "t_data_txt_key"
Stav SQL: 23505
Podrobnosti:Key (txt)=(ab) already exists.
Kontext:SQL statement "INSERT INTO t_data(txt)
VALUES
(pdata)"
PL/pgSQL funkce f_test_ins2(text) řádek 7 na SQL příkaz
SQL statement "select * from f_test_ins2('ab')"
PL/pgSQL funkce f_test_ins2() řádek 6 na SQL příkaz
Můžeme ošetřit vše pomocí výjnimky, která detekuje chzbový stav a neprovede špatnou transakci.
CREATE OR REPLACE FUNCTION f_test_ins(pdata text)
RETURNS boolean AS
$BODY$
DECLARE
v_exc_context TEXT;
pocetr BIGINT;
BEGIN
/*
* Pokusne vlozeni dat do tabulky t_data. Vysledek
* je logovan do t_log.
*
* Vstup:
* pdata - vlozena hodnota
*
* Vystup :
* true - vse je ok
*
* Pouzite db objekty :
* t_data - cilova tabulka
* t_log - logovaci tabulka
*
*/
-- Provedeni akce
INSERT INTO t_data(txt)
VALUES
(pdata);
-- Zaznam do logu
GET DIAGNOSTICS pocetr = ROW_COUNT;
INSERT INTO t_log(txt)
VALUES ('Bylo vlozeno '||pocetr::TEXT||' radku.');
return true;
EXCEPTION
WHEN OTHERS THEN
-- Osetreni chybovych stavu
GET STACKED DIAGNOSTICS v_exc_context = PG_EXCEPTION_CONTEXT;
-- Zaznam do logu
INSERT INTO t_log (txt)
VALUES
(SQLERRM || ' [SQL State: ' || SQLSTATE || '] Context: ' || v_exc_context);
RETURN false;
END;
$BODY$
LANGUAGE plpgsql;
-- Test
select * from f_test_ins('aa'); -- Hodnotu jsme vložili pouze jednu
select * from t_data;
id | txt
----+-----
31 | ab
-- V logu je záznam jak o vložení řádku, tak i chybě, která zabránila provedení druhého příkazu
select 8 from t_log;
id | txt
----+--------------------------------------------------------------------------------------------------------------------------------------
22 | Bylo vlozeno 1 radku.
23 | duplicate key value violates unique constraint "t_data_txt_key" [SQL State: 23505] Context: SQL statement "INSERT INTO t_data(txt)\r+
| VALUES \r +
| (pdata)" +
| PL/pgSQL funkce f_test_ins(text) řádek 22 na SQL příkaz +
| SQL statement "select * from f_test_ins('ab')" +
| PL/pgSQL funkce f_test_ins() řádek 6 na SQL příkaz Výsledek je oproti prvnímu příkladu lepší. Vložili jsem správná data a nesprávná data se nevložili. Jakoby existoval „save point“ pro uchování transakce, která v případě chyby odroluje chybnou transakci pouze do daného bodu. Je to dobré, ale jsou alokované zdroje na danou transakci, kterou nemůžeme rozbít.
Řešením byl dblink nad vlastní databází.
Co je to dblink ?
dblink je prostředek jak zadat SQL příkaz nad jinou databázi a výsledek konzumovat ve vlastní PostgreSQL databázi.
Jak rozchodit dblink v databázi ?
Asi nejlépe bude postupovat dle manuálu a podrobně se seznámit se vším, co kolem toho je. (Vřele doporučuji. :) ). Pro všechny netrpělivce zde stručný návod jak to udělat :
- Definici extenze do databáze
create extension dblink; -- Extenze pro pripojeni databaze create extension postgres_fdw; - Definice cizího serveru (na sebe sama)
-- Server 'localhost' definovaný na sebe sama CREATE SERVER localserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'localhost',dbname 'test2',port '5432'); - Mapování uživatele na sebe sama
-- pro uzivatele 'test' na sebe sana pro lokální server CREATE USER MAPPING FOR test SERVER localserver OPTIONS (user 'test',password 'test'); -- Kdyby bylo potreba vymazat mapovani -- DROP USER MAPPING FOR test SERVER localserver; - Test připojení
-- Dotaz nad localserverem na běžný datum vrací záznam 'r', který obsahuje jeden -- sloupec dat typu date select * from dblink('localserver','select current_date;')r(dat date); -- Výsledek /* dat ------------ 2017-10-22 */
Nyní definujme funkci, která ve dvou transakcích vloží stejná data.
CREATE OR REPLACE FUNCTION f_test_ins()
RETURNS boolean AS
$BODY$
DECLARE
lerr TEXT;
BEGIN
/*
* Volání v izolovaných transakcí vložení dat do tabulky t_data. Vysledek
* je logovan do t_log.
*
* Vstup:
*
* Vystup :
* true - vse je ok
*
* Pouzite db objekty :
* t_data - cilova tabulka
* t_log - logovaci tabulka
*
*/
-- Provedeni akce
select * from dblink('localserver','select * from f_test_ins(''ab'');')r(err boolean) into lerr;
select * from dblink('localserver','select * from f_test_ins(''ab'');')r(err boolean) into lerr;
return lerr;
END;
$BODY$
LANGUAGE plpgsql; Po spuštění funkce sice funkce vrátí false a v logu je vyskytnutá chyba, ale 1. transakce prošla bez chyby. Navíc jsme fyzicky provedli 2 transkace.
-- test transakcni funkce
select * from f_test_ins()
-- Výpis logu. 1. transakce prošla a 2. né
select * from t_log;
id | txt
----+--------------------------------------------------------------------------------------------------------------------------------------
9 | Bylo vlozeno 1 radku.
10 | duplicate key value violates unique constraint "t_data_txt_key" [SQL State: 23505] Context: SQL statement "INSERT INTO t_data(txt)\r+
| VALUES \r +
| (pdata)" +
| PL/pgSQL funkce f_test_ins(text) řádek 22 na SQL příkaz
(2 řádky)
-- Výpis obsahu t_data. Pouze 1. transakce zapsala data
select * from t_data;
id | txt
----+-----
17 | ab Pozór na DEADLOCK.
Deadlock vzniká tehdy, když jedna transakce čeká na ukočení druhé, ale ta druhá čeká na ukončení té první. Nasimulovat je to velice jednoduché. Například :
-- Simulace deadlocku
delete from t_data;
insert into t_data(txt) values ('a');
CREATE OR REPLACE FUNCTION f_test_upd()
RETURNS boolean AS
$BODY$
DECLARE
ltxt text;
BEGIN
update t_data
set txt = 'b';
select dblink_exec('localserver','update t_data set txt = ''c'';') into ltxt;
END;
$BODY$
LANGUAGE plpgsql;
select * from f_test_upd() Zde funkce f_test_ins probíhá v transakci a zamyká veškeré řádky v tabulce t_data. Provede update a spustí akci v dblinku, ale dblink che udělat to samé. Kdyby byly v jedné transakci nestane se nic, ale protože existují dvě transakce navzájem si blokují provedení.
Pak nezůstává nic jiné než alespoň jednu transakci sestřelit.
ČLÁNKY DO MAILU