Přechod z db Solid do PostgreSQL
Pavel Stěhule
Pro svého zaměstnavatele jsem řešil port IS z RDBMS Solid na RDBMS PostgreSQL 7.4. (Pozn. zde uvedené skripty a procedury vychází z možností PostgreSQL 7.4 - určité funkce lze v aktuálních verzích řešit efektivněji a přehledněji např. použitím OUT parametrů, ...).
Tento informační systém byl realizován v letech 1997-2000. V tehdejších podmínkách byla zvolen jako RDBMS systém finský systém Solid. Provozovatel po několika letech ukončil vztah díky nedostatečné podpoře prodejce, nicméně systém zůstal nadále v provozu. V únoru 2004 bylo rozhodnuto o přechodu na PostgreSQL. Po několika týdnech práce a testovacího provozu byl systém plně zprovozněn na PostgreSQL. Pozn. ačkoliv názory na Solid nebyli pozitivní (díky skutečně pomalé odezvě systému) je třeba konstatovat, že Solid byl v provozu s minimální kvalifikovanou podporou 5 let. Portace měla být provedena s minimálními změnami v kódu aplikace (což bylo možné, díky zapouzdření volání SQL do vlastní knihovny).
Charakteristika systému - databáze obsahuje informace zajména podklady pro řízení a dohled nad službami zákazníků. Se systémem pracuje maximálně 20 uživatelů, z toho však jen maximálně čtvrtina přidává a modifikuje záznamy. tj. zátěž je prakticky minimální.
Databáze sama má cca 100MB. Největší tabulky 650K záznamů. Příprava
- Rozdíl ve funkcích - Některé funkce Solidu nemají ekvivalentní funkci v PostgreSQL, nebo se funkce v PostgreSQL jmenuje jinak. Řešeno UDP funkcemi v sql, např.
CREATE FUNCTION Convert_Date(anyelement) RETURNS date AS 'SELECT CAST($1 AS date);' LANGUAGE sql;
nebo náhradou názvu funkce, resp. konstanty, např.curdate -> CURRENT_DATE, ifnull -> COALESCE
.
- Rozdíl v zobrazení hodnot - jediný problém byl v typu timestamp, který v Solidu obsahuje čas s přesností sekund, kdežto v PostgreSQL s přesností milisekund. Řešeno modifikací funkcí Now()
CREATE FUNCTION nowls() RETURNS timestamp AS 'SELECT CAST(date_trunc('second', now()) AS timestamp without time zone);' LANGUAGE sql;
- Rozdíl v syntaxi volání funkcí. PostgreSQL nepodporuje příkaz CALL. Řešeno dynamickou změnou SQL příkazu, tj. SQL příkaz CALL fx(..) se převádí na SELECT fx(..) a v případě chyby (jedná li se SRF funkci) na SELECT * FROM fx(..)
// call beru hned jinak, a cokoliv z SP_OP_SELECT taky
if (eregi("(call[[:space:]]+)([%a-zA-Z0-9_]+)", $query, $regs)) {
if (eregi("sp_op_select", $query)) {
$query2 = str_replace($regs[1].$regs[2], "SELECT * FROM $regs[2]", $query);
} else {
$query2 = str_replace($regs[1].$regs[2], "SELECT $regs[2]", $query);
}
$result = @pg_query($conn, $query2);
if ($result ) return $result;
$err = pg_last_error($conn);
if (eregi("set-valued function called in context", $err)) {
$query2 = str_replace($regs[1].$regs[2], "SELECT * FROM $regs[2]", $query);
}
}
- Rozdíl v možnosti použití klíčových slov v definici tabulky - Solid dovoluje použití klíčového slova jako názvu sloupce tabulku, což PostgreSQL zakazuje, nebo je nutné použít ANSI notaci zápisu (uvozovky). Řešeno dynamickou změnou SQL příkazu, tj. pokud PostgreSQL zahlasí syntaktickou chybu, testuje se, zda-li její příčinnou mohlo být použití klíčového slova. Pokud ano, tak se toto slovo zapouzdří do uvozovek a systém znova zkusí provést SQL dotaz (problemová jsou zejména slova: NAME, TYPE, LIMIT, ASC, DESC, TABLE, TEXT). Místo psaní komplikovaného SQL parseru se osvětčila metoda pokus/omyl, tj. SQL příkaz se nechá provést a přez regulární výrazy se analyzuje chybové hlášení. Pokud na základě této analýzy je důvod k změně SQL příkazu, pak se SQL příkaz modifikuje a nechá se znovu provést. Negativní dopady jsou při stávajícím zatížení nepostřehnutelné.
$result = @pg_query($conn, $query);
if ($result ) return $result;
if ($oprav > 30) return $result;
$err = pg_last_error($conn);
if (eregi ("column \"?([a-zA-Z0-9_]+\.)*([a-zA-Z0-9_]+)\"? does not exist", $err, $regs)) {
$tabulka = strtoupper($regs[2]);
if ($tabulka == "NAME" ||$tabulka == "TYPE" ||$tabulka == "LIMIT" ||
$tabulka == "DESC" || $tabulka == "ASC" || $tabulka == "TABLE" || $tabulka == "TEXT") {
$offset = 0; $p = strpos($query, $regs[2], $offset);
$queryold = $query;
while (!($p === false)) {
if (substr($query, $p, 1) <> "\"") {
//muzu opravit
$query = substr($query, 0, $p) . "\"$tabulka\"" . substr($query, $p + strlen($regs[2]));
$oprav = $oprav + 1;
break;
}
$offset = $p + strlen($regs[2]);
$p = strpos($query, $regs[2], $offset);
}
if ($queryold == $query) {
return $result;
}
}
else
return $result;
}elseif (eregi ("syntax error at or near \"([a-zA-Z0-9_]+)\" at character ([[:digit:]]+)", $err, $regs)) {
$tabulka = strtoupper($regs[1]);
if ($tabulka == "NAME" ||$tabulka == "TYPE" ||$tabulka == "LIMIT" ||
$tabulka == "DESC" || $tabulka == "ASC" || $tabulka == "TABLE" || $tabulka == "TEXT") {
$oprav = $oprav + 1;
$pred = substr($query, 0, $regs[2]-1);
$za = substr($query, $regs[2] + strlen($tabulka)-1);
$query = "$pred\"$tabulka\"$za";
}
- Rozdíl v syntaxi LIKE - pokud vzor v LIKE neobsahoval mezeru, pak Solid netrval na omezení masky apostrofy. PostgreSQL toto (naštěstí) nepodporuje.
// zkusim opravit unclosed like
if (eregi ("([a-zA-Z0-9\"]+[[:space:]]+like[[:space:]]+)([%a-zA-Z0-9_]+)", $query, $regs)) {
$oprav = $oprav + 1;
$co = $regs[1].$regs[2]; $cim = "$regs[1]'$regs[2]'";
$query = str_replace($co, $cim, $query);
}
else
return $result;
- Solid nepodporoval klauzuli LIMIT. Omezení počtu řádku bylo prováděno pomocí fiktivního sloupce rownum. Řešením bylo dohledání těchto dotazů a jejich ruční oprava, tj z .. WHERE rownum < n na .. LIMIT n - 1
- Rozdíl v zobrazení pořadí modifikovaných řádků - Pro PostgreSQL modifikace záznamu je ekvivalentní jeho zrušení a přidání, tj. modifikovaný záznam se ve výpisu objeví na konci. V Solidu modifikace záznamu nemění pořadí zobrazení řádku. To vedlo k zmatení uživatelů. Řešením bylo do problematických selectů doplnění ORDER BY.
- Rozdíl v syntaxi uložených procedur - Jazyk pro programování SP v Solidu je blízký SQL/SP..., PL/pgSQL vychází z Oracle PLSQL. Rozdíl mezi oběma jazyky je příliš velký, takže se SP přepisovaly ručně (důležité bylo akceptovat základní vzor):
-- Solid
CREATE PROCEDURE foo(p1 t1) RETURNS
(r1 t1, r2 t2, ...)
BEGIN
DECLARE cnt integer;
DECLARE i integer;
EXEC SQL PREPARE C_COUNT SELECT COUNT(*) FROM ... WHERE id = ?
EXEC SQL PREPARE C_SELECT SELECT ... FROM ... WHERE id = ?
EXEC SQL EXECUTE C_COUNT USING(p1) INTO (cnt);
EXEC SQL FETCH C_COUNT;
EXEC SQL CLOSE C_COUNT;
EXEC SQL DROP C_COUNT;
i := 0;
EXEC SQL EXECUTE C_SELECT USING(p1) INTO (r1, r2, ...);
WHILE i < cnt LOOP
EXEC SQL FETCH C_SELECT;
RETURN ROW;
i := i + 1;
END LOOP;
EXEC SQL CLOSE C_SELECT;
EXEC SQL DROP C_SELECT;
END;
-- PostgreSQL
CREATE TYPE foo_ret as (r1 t1, r2 t2, ...);
CREATE FUNCTION foo(p1 t1) RETURNS SETOF foo_ret AS '
DECLARE r foo_ret;
BEGIN
FOR r IN SELECT ... FROM ... WHERE id = p1 LOOP
RETURN NEXT r;
END LOOP;
RETURN;
END;' LANGUAGE plpgsql;
Při převodu se opět ověřilo základní doporučení: lokální proměnné PL/pgSQL se nemají shodovat s názvy sloupců v SQL příkazech. Vznikají záludné a těžko lokalizované chyby (nejedná se o syntaktickou chybu).
Převod dat
Samotný přechod nebyl nejjednodušší. Přišlo se na chybu dumpu Solidu, kdy Solid v případě exportu položek typu timestamp neexportoval čas. Dálší záludnost, příkaz COPY neakceptuje \n\r, atd. Bylo potřeba tyto znaky z dumpu nahradit (např. <cr>) a pak po importu vrátit do původní podoby. Pro automatizaci importu (pro testování) import probíhal opakovanně nakonec bylo třeba napsat několik skriptů, importujících kompletní množinu tabulek. Pro import je třeba znát závislosti tabulek (pokud nenastavim ref. integritu ....., i přesto pokud nechceme ručně vypisovat n příkazů copy). Osvědčil se následující skript
#!/bin/bash
psql intra <<EOF
CREATE OR REPLACE FUNCTION list_user_tables_sort_depend
(owner VARCHAR, revers BOOLEAN) RETURNS SETOF VARCHAR AS '
DECLARE tabulky VARCHAR[]; i INTEGER; opakovat BOOLEAN = ''t'';
pom VARCHAR; exportovano VARCHAR[] = ''{}''; r RECORD; mohu_exportovat BOOLEAN;
BEGIN SELECT ARRAY(SELECT tablename FROM pg_tables WHERE tableowner = owner) INTO tabulky;
WHILE opakovat LOOP
opakovat := ''f'';
FOR i IN array_lower(tabulky,1) .. array_upper(tabulky,1) LOOP
IF tabulky[i] <> '''' THEN
mohu_exportovat := ''t'';
FOR r IN SELECT t.relname AS z, x.relname AS nz FROM pg_catalog.pg_constraint d
INNER JOIN pg_catalog.pg_class t on t.oid = d.conrelid
INNER JOIN pg_catalog.pg_class x on x.oid = d.confrelid
WHERE d.contype = ''f'' AND t.relname = tabulky[i] LOOP
IF NOT r.nz = ANY(exportovano) THEN
mohu_exportovat := ''f'';
END IF;
END LOOP;
IF mohu_exportovat THEN
pom := tabulky[i];
exportovano := exportovano || tabulky[i];
opakovat := ''t''; tabulky[i] := '''';
END IF;
END IF;
END LOOP;
END LOOP;
IF revers THEN
FOR i IN REVERSE array_upper(exportovano,1) .. array_lower(exportovano,1) LOOP
RETURN NEXT exportovano[i];
END LOOP;
ELSE
FOR i IN array_lower(exportovano,1) .. array_upper(exportovano,1) LOOP
RETURN NEXT exportovano[i];
END LOOP;
END IF;
RETURN;
END;
' LANGUAGE plpgsql;
EOF
if [ ! -d postgresql ]; then
mkdir postgresql
else
rm postgresql/*
fi;
if [ ! -d postgresql ]; then
mkdir postgresql
else
rm postgresql/*
fi;
./intrain.sh
DATADIR=./home/okbob/`date +%Y%m%d`
echo "BEGIN;\n" >> postgresql/import.sql
for table in `psql -At -c "SELECT * FROM list_user_tables_sort_depend('root','f');" intra`; do
TABLE=`echo $table|tr [[:lower:]] [[:upper:]]`
echo $TABLE
if [ -e $DATADIR/$TABLE.dat ]; then
cat $DATADIR/$TABLE.dat |./reformat.pl > postgresql/$TABLE.data
echo "\copy $table from '$TABLE.data' delimiter ',' null 'NULL'" >> postgresql/import.sql
fi
done
echo "COMMIT;" >> postgresql/import.sql
echo "BEGIN;" >> postgresql/delete.sql
for table in `psql -At -c "SELECT * FROM list_user_tables_sort_depend('root','t');" intra`; do
echo "delete from $table;" >> postgresql/delete.sql
done
echo "COMMIT;" >> postgresql/delete.sql
cat $DATADIR/dict.sql | ./get_seq.pl >> postgresql/get_seq.sql
rm -rf ./home
Provoz
Díky možnosti logování dotazů jejichž provedení trvalo delší dobu než určenou bylo možné velice přesně a rychle vytipovat dotazy, které bylo třeba optimalizovat (přepsáním, indexy). Během třech dnů provozu pouze dotazy obsahující výraz LIKE trvají déle než 100 ms. Pro představu jedna z nejčastějších SP trvala obvykle 900-2000ms, po optimalizaci 40-80ms. Objevil se problém s dotazy a update příkazem obsahující podmínku typu
where pk = konstanta1 OR (select pk from .. wherere neco = konstatnta2)
ktere bylo třeba ručně přepsat do tvaru
where pk IN ( select pk from .. wherere neco = konstatnta union all select konstatnta1)
Zrychlení bylo z cca 140 až 200ms na 10 ms (v prvním tvaru se nepoužil index). I bez optimalizace byly základní dotazy mnohem rychlejší v PostgreSQL než v Solidu (je znát časový odstup 6 let) a to 6x až 10x. Což znatelně minimalizuje zatížení serveru a možnost zahlcení.
Závěr
Zásadně se zrychlila aplikace (běžné odezvy jsou "okamžité"), nedochází k zahlcení serveru, bez nutnosti změny hw (samotná změna hw by nebyla až takovým problémem, horší by byla pravděpodobná nutnost reinstalace sw). Po nasazení PostgreSQL vše mohlo zůstat při starém. Ačkoliv se nejedná o rozsáhlou databázi její běh je pro firmu kritický (fy. je ovšem zvyklá používat open source produkty, další systém běží nad MySQL, PostgreSQL bylo zvoleno pro svou "podobnost" se Solidem, tudíz zásahy do kódu aplikace mohli být skutečně minimální.