Klient v PHP se stará pouze o prezentaci. Kód vznikal během posledních 18 let a vyjma toho, že ho je docela hodně, tak není příliš komplikovaný. Co se týče kvality, klasika, sebejistě porušuje všechny best practices, které znám. Na druhou stranu, bez ohledu na kvalitu kódu se jedná o produkt, který je odladěný a uživateli dlouhodobě používaný.
Ačkoliv je klient napsaný v PHP, tak se nejedná o klasickou webovou aplikaci. Větší zákazníci si aplikaci provozují sami (na svém železe s vlastní správou databáze). Vlastní portace aplikace do Postgresu je jen prvním krokem. Dalším krokem bude postupná migrace zákazníků z Oracle do Postgresu. Tady se počítá s velkou setrvačností uživatelů. Všichni chtějí ušetřit, zároveň nikdo z nich nechce měnit to, co jim roky bez větších problémů funguje. Proto se předpokládá, realisticky, že migrace zákazníků bude trvat několik let (i když technicky by to bylo možné za několik málo měsíců).
Primární motivací pro migraci je snaha se zbavit závislosti na Oracle. Sekundární motivací jsou pak úspory na licencích. Jiné důvody pro migraci nejsou. Před 18 lety bylo použití Oracle jako backendu vstupenkou do první ligy. Dnes dodavatel databáze už nehraje takovou roli. Často jsou pro zákazníky důležitější provozní a licenční náklady.
Strategie
Základem je definice budoucího provozu aplikace. Je několik možností:
- jednorázová migrace, následně vývoj a provoz už jen na PostgreSQL
- portace aplikace do PostgreSQL, vývoj na Oracle + aktualizace portu na Pg, provoz Oracle a PostgreSQL
- portace aplikace do PostgreSQL, vývoj na PostgreSQL + backporty na Oracle, provoz na Oracle a PostgreSQL
Varianta a je nejjednodušší. S nástroji, které jsou dnes k dispozici, lze provést portaci docela rychle (v závislosti na kreativitě programátorů portované aplikace). Bohužel většinou tato varianta není možná. Uživatelé Oracle bývají, když už mají ponětí o databázích, poměrně konzervativní, a tak převedení všech zákazníků z Oracle na PostgreSQL může trvat 2–5 let. Po tu dobu je potřeba podporovat jak Oracle, tak PostgreSQL.
Pro variantu c. neexistují žádné nástroje, tudíž zbývá varianta b.
Tento postup poměrně pracný. Předpokladem je dostat kód v Oracle do stavu, kdy je možná automatická portace do PostgreSQL a v tomto stavu kód udržovat až do kompletního dokončení migrace.
- export kódu z Oracle a jeho transformace do Postgresu
- import kódu do Postgresu a jeho validace
- identifikované problémy se opraví v Oracle
- jdi na bod 1.
Začátek je dost náročný. I když změny jsou spíše mechanické, musí se upravit tisíce řádků. Vynaložená námaha se ale určitě vrátí. Podpora dvou různých databází je pak díky automatickému převodu „zdarma“. Po pročištění kódu je už s migrací minimum problémů.
Pracnost portace hodně souvisí s kvalitou originálního Oracle kódu. Porušení best practices obvykle zvyšuje pracnost portace.
První krok zajišťuje Ora2pg. Výstupem je sql generující skript (originál). Pro druhý krok lze použít plpgsql_check. Po ručních úpravách získáme validní sql generující skript. Rozdíl mezi originálním a validním sql skriptem je vstupem pro třetí krok.
Nástroje
Ora2pg
Ora2pg je nástroj určený původně k migraci tabulek a dat z Oracle. Časem přibyla možnost migrovat pohledy a relativně nově Ora2pg umí migrovat PL/SQL do PL/pgSQL. Je to už letitá aplikace, která řeší poměrně komplikovanou úlohu, takže ovládání není zrovna kdoví jak komfortní. Na druhou stranu toho umí opravdu hodně, a vlastně nejkomplikovanější část na zprovoznění Ora2pg byla instalace a konfigurace driveru pro Oracle. Navíc s autorem Gillesem Daroldem je radost spolupracovat. Kolikrát byly bugfixy a feature requesty vyřešené do 24 hodin. To nebývá časté ani u placeného produktu. Kromě Oracle Ora2pg zvládne migraci schématu a dat i z MySQL.
Hodně se mi ulevilo ulevilo, když se Ora2pg naučilo migrovat pohledy s pluskovou zápisem. Představa, že budu muset ručně přepisovat 1200 gigantických pohledů, byla moje noční můra.
- http://ora2pg.darold.net/start.html
- https://ora2pg.darold.net/documentation.html
- https://github.com/darold/ora2pg
plpgsql_check
Pokud používáte PL/pgSQL, tak určitě víte, že kontrola kódu, kterou PostgreSQL provádí při registraci funkce, je „mělká“. Nekontroluje se, zdali použité databázové objekty (tabulky, pohledy, funkce) existují, a zda mají správné atributy (názvy sloupců, parametry, typy parametrů). Toto chování je v některých ohledech výhodné (závislosti mezi objekty se řeší až v runtime), v některých nutnost (v Postgresu práce s lokálními dočasnými tabulkami). Ve chvíli, kdy ale máte zkontrolovat tisíce řádků kódu a nemáte unit testy, tak Vám Postgres moc nepomůže.
Pro jiný poměrně rozsáhlý projekt v plpgsql jsem napsal extenzi plpgsql_lint. plpgsql_check je druhou generací této extenze. Smyslem této extenze je provedení maximálně možné statické analýzy bez nutnosti spouštět kód. Tato extenze není náhradou testů, a ani být nemůže. Je ale velice rychlá (za vteřinu zvládne cca 8000 řádků), a dokáže dobře identifikovat překlepy, neodpovídající si typy a množství dalších problémů, které vznikají při portaci kódu z Oracle. U starších aplikací často chybí testy. S plpgsql_check můžeme portaci dotáhnout do stavu, kdy lze začít oživovat kód a začít psát testy.
plpgsql_check si můžete nainstalovat z komunitního repozitáře nebo si stáhnout zdrojáky z GitHubu a přeložit.
Orafce
Stejně jako plpgsql_check, ani extenze Orafce nebyla určena k portaci aplikací z Oracle. Cílem bylo vytvořit pohodlnější prostředí pro vývoj v PL/pgSQL a naučit se psát extenze pro Postgres. Začal jsem portací API knihovny PL/Vision. Pak jsem přidal několik dalších funkcí, které se mi buďto líbily nebo které chyběly lidem, kteří přecházeli z Oracle. Až do letošního roku jsem Orafce pro žádný projekt nepoužil.
postgres=# CREATE EXTENSION orafce; CREATE EXTENSION postgres=# SELECT * FROM dual; ┌───────┐ │ dummy │ ├───────┤ │ X │ └───────┘ (1 row) postgres=# SET search_path TO public,oracle; SET postgres=# SELECT * FROM product_component_version ; ┌───────────────────┬─────────┬─────────────┐ │ product │ version │ status │ ├───────────────────┼─────────┼─────────────┤ │ PostgreSQL 9.4.12 │ 9.4.12 │ 64bit Debug │ │ plpgsql │ 9.4.12 │ 64bit Debug │ │ orafce │ 3.6 │ 64bit Debug │ └───────────────────┴─────────┴─────────────┘ (3 rows) postgres=# SELECT next_day(CURRENT_DATE, 'sun'), add_months(CURRENT_DATE,1); ┌────────────┬────────────┐ │ next_day │ add_months │ ├────────────┼────────────┤ │ 2017-07-09 │ 2017-08-02 │ └────────────┴────────────┘ (1 row)
Pak se Orafce chytil RedHat, který jej chtěl použít pro portaci Spacewalku. Nějakým způsobem se o Orafce dozvěděli v Japonsku a začali jej intenzivně používat v NTT při portacích z Oracle. V jedné jejich prezentaci uváděli, pro mne překvapivě, vysokou úspěšnost. Cca 73 % aplikací lze jednoduše portovat na Postgres s minimálními zásahy do kódu a Orafce. Drtivá většina nového kódu v Orafce je za poslední roky od lidí a firem, kteří provozují multiplatformní aplikace.
- https://www.slideshare.net/pgdayasia/migration-from-oracle-to-postgresql
- https://github.com/orafce/orafce
Postup
- export struktur tabulek z Oracle do sql skriptu a jeho případné ruční doladění.
- import sql skriptu do Postgresu – poté lze doladit schéma příkazy ALTER TABLE ..
- dočasné odstranění indexů, vypnutí kontrol referenční integrity
- import dat
- vytvoření indexů, kontrola referenční integrity a zapnutí kontrol
- export funkcí do sql skriptu
- vytvoření fake procedur potřebných pro provedení kontrol funkcí
- import funkcí, kontrola importovaných funkcí, případně jejich opravy v sql skriptu
- export/import pohledů
- export procedur do sql skriptu
- import procedur, jejich kontrola a případné opravy v sql skriptu
- export triggerů do sql skriptu
- import triggerů, jejich kontrola a případné opravy v sql skriptu
- oživení aplikace
Občas je to úmorná práce. Zvlášť když kódu je na začátku hodně. Postupem času, tak jak klesá počet chybných a roste počet úspěšně zvalidovaných procedur a funkcí, je vidět světlo na konci tunelu. Těch cca 300 000 řádek kódu se převedlo za půl roku ve dvou lidech. Já jsem byl zodpovědný za PostgreSQL část a pracoval jsem tak cca 1/3 úvazek. Kolega zodpovědný za Oracle pracoval na tomto projektu cca 70 % svého času.
Datové typy
Jedním z velkých rozdílů mezi PostgreSQL a Oraclem je přístup k implicitním konverzím mezi datovými typy. U PostgreSQL jsou implicitní konverze omezené na příbuzné typy a je snaha identifikovat některé problémy již v čase překladu. Oracle se implicitním konverzím nebrání. Pozor: Všechny best practices před implicitními konverzemi důsledně varují. Bohužel, ne každý programátor se těmito doporučeními řídí. Leckdy se používá varchar ve smyslu generického typu, kam se ukládají i čísla a datumy.
Výsledkem může být docela chaos mezi typy sloupců, proměnnými, parametry funkcí – jedinou možností jak se vyvarovat chaosu je používání notace %TYPE a %ROWTYPE. V kódu, kde se tato notace intenzivně používala, jsem se ale občas ztrácel také. Chyběla mi zřejmá informace, o jaký základní datový typ se jedná. Dala by se použít maďarská notace, ale to už je vrstvení záplat a pak už je všechno špatně.
Novější prvky jazyka SQL vycházející ze standardu, jako jsou CASE, COALESCE, jsou typově restriktivní (a v chování velmi podobné PostgreSQL). A i když Oracle roky doporučuje používat tyto konstrukce, tak se staré funkce jako je DECODE, NVL stále používají (někdy je to dáno i dobou vzniku aplikace).
Do určité míry nejpracnější částí portace je vyčištění kódu od následujících problémů:
- používání funkcí pro řetězce vůči číselným hodnotám:
SELECT trim(cislo_popisne), ...
- šílené konstrukce pro práce s datumy (typ date):
-- ŠPATNĚ, ořízne čas (implicitní konverze text -> date) datevar := TO_CHAR(sysdate, 'DD.MM.YYYY'); -- ŠPATNĚ (zbytečné konverze, nečitelné) datevar := TO_DATE(T_CHAR(sysdate, 'DD.MM.YYYY'),'DD.MM.YYYY'); -- ŠPATNĚ (implicitní konverze date-> text) datevar := TO_DATE(sysdate, 'DD.MM.YYYY'); datevar := trunc(sysdate); -- správně datevar := '1.1.' || TO_CHAR(sysdate, 'YYYY'); -- začátek roku ŠPATNĚ datevar := trunc(sysdate, 'YEAR'); -- správně intvar := TO_CHAR(sysdate, 'MM'); -- aktuální měsíc ŠPATNĚ intvar := EXTRACT(MONTH FROM sysdate); -- správně TO_CHAR(datevar1, 'SS') - TO_CHAR(datevar2, 'SS') -- rozdíl v času v SEC ŠPATNĚ EXTRACT(SECOND FROM datevar1) - EXTRACT(SECOND FROM datevar2); -- správně
- automatické přetypování čísel na text – funkce
NVLumožňuje používat textové i číselné parametry naráz (COALESCEneboCASEuž je striktní):SELECT nvl('ahoj', 0); -- projde SQL> select coalesce('ahoj',0) from dual; select coalesce('ahoj',0) from dual * ERROR na řádku 1: ORA-00932: nekonzistentní datové typy: očekáváno CHAR, nalezeno NUMBER
PostgreSQL je možné přiohnout, aby se choval hodně podobně jako Oracle. Je možné definovat vlastní funkce: např. trim, btrim pro celá čísla. Je možné definovat implicitní konverze: např. mezi textem a timestampem. To ale určitě nedoporučuji. I s povolenými implicitními konverzemi se liší chování obou databází. V případě, že má PostgreSQL na výběr mezi konverzí do specifického typu nebo řetězce, PostgreSQL preferuje řetězce. U Oracle je to přesně opačně. Každé řešení má svojí logiku. Pokud v PostgreSQL projde typová kontrola, tak už se preferuje řešení, které je tolerantní v runtime. U Oracle je tolerantní typová kontrola a restriktivní runtime. Nešikovné použití implicitního přetypování může vést k tomu, že se nepoužijí indexy.
Specifikem Oracle jsou datové typy date a number, u kterých není jednoznačný převod do PostgreSQL. Oracle date může být PostgreSQL date nebo time nebo timestamp. Number v Oracle může být numeric, double precision, int, bigint v PostgreSQL. Je chybou vždy převádět number na postgresový numeric.
V závislosti na volbě typu v Postgresu pak musíme řešit další problémy. Například, pokud si zvolíme timestamp jako náhradu date v Oracle, pak běžná operace jako je +/- celé číslo není podporovaná:
bes_jmmaj=# SELECT CURRENT_TIMESTAMP + 1;
ERROR: operator does not exist: timestamp with time zone + integer
ŘÁDKA 2: SELECT CURRENT_TIMESTAMP + 1;
^
Musíme buďto explicitně přetypovat na PostgreSQL date, nebo použít interval, nebo si nadefinovat vlastní operátor:
bes_jmmaj=# SELECT CURRENT_TIMESTAMP + interval '1day' ; ┌────────────────────────────────┐ │ ?column? │ ╞════════════════════════════════╡ │ 03.07.2017 06:33:45.16754 CEST │ └────────────────────────────────┘ (1 řádka) bes_jmmaj=# SELECT CURRENT_TIMESTAMP::date + 1; ┌────────────┐ │ ?column? │ ╞════════════╡ │ 03.07.2017 │ └────────────┘ (1 řádka) CREATE OR REPLACE FUNCTION public.timestamp_plus_int(timestamp with time zone, integer) RETURNS timestamp with time zone LANGUAGE sql IMMUTABLE STRICT AS $function$ -- emulace Oracle SELECT ($1::date + $2)::timestamp with time zone $function$ CREATE OPERATOR + (PROCEDURE=timestamp_plus_int, LEFTARG=timestamp with time zone, RIGHTARG=int); postgres=# SELECT CURRENT_TIMESTAMP + 1; ┌──────────────────────────┐ │ ?column? │ ╞══════════════════════════╡ │ 03.07.2017 00:00:00 CEST │ └──────────────────────────┘ (1 řádka)
Pro typ oracle.date z extenze Orafce jsou tyto operátory připraveny a tudíž si je nemusíte definovat sami.
Rozdíly v zápisu dotazu
Parser (část databáze, která text SQL příkazu transformuje na syntaktický strom) je v Postgresu citlivý na nevhodně použitá klíčová slova (například jako alias). Je to dáno vlastnostmi Yaccu. Mám pocit, že v Oracle je ručně psaný parser – vývojáři mají větší informace o kontextu, které mohou využít. Na některých pozicích tak nemusí řešit, jestli je symbol klíčovým slovem nebo není.
SELECT 10 boo FROM dual; -- výsledkem 10 ve sloupci "BOO"SELECT 10 AS boo FROM dual; -- výsledkem 10 ve sloupci "BOO"SELECT 10 AS FROM dual; -- vysledkem 10 ve sloupci "10AS"
Postgres v případě varianty c vyhodí syntaktickou chybu – AS je rezervované klíčové slovo, které se nesmí použít jako identifikátor. Navíc varianta c může být aplikační chybou, kdy vývojář opomenul popisek sloupce. Postgres je, co se týká syntaxe, matematicky striktní. Podle použitého labelu „10AS“ v Oraclu, to vypadá, že i v Oracle takový zápis nepovažují za úplně korektní. Považují ale za důležitější provést příkaz, než nutit programátora, aby opravil aplikaci. Nemyslím si, že je to správně, ale můj názor vůbec nic neznamená.
Další rozdíl mezi Oraclem a PostgreSQL je v implementaci více řádkových komentářů. V Oracle komentář začíná symbolem /* a končí symbolem */. Uvnitř může být libovolný počet symbolů pro začátek komentáře – to Postgres nedovoluje, protože podporuje zanořené komentáře, a počet výskytů /* musí odpovídat počtu ukončení komentářů */. Oracle to řešit nemusí, zanořené komentáře nepodporuje.
Komentář /* /* */ je v Oracle ok, v PostgreSQL způsobí syntaktickou chybu.
V PostgreSQL je povinný alias poddotazu, pokud se použije jako derivovaná tabulka tj v klauzuli FROM.
Samostatnou kapitolou jsou proprietární rozšíření SQL jako pluskové outer joiny a rekurzivní dotazy přes CONNECT BY. Opět, Oracle už roky nedoporučuje tyto zápisy používat, nicméně u starších aplikací se s nimi setkáme běžně. Pro převod outer joinů do ANSI SQL syntaxe jsem úspěšně používal Ora2pg.
Test na prázdný řetězec
Další specifickou vlastností je přístup Oracle k prázdnému řetězci. Oracle prázdné řetezce automaticky nahrazuje NULLem. Ve funkcích, které pracují s řetězci, je NULL automaticky nahrazen prázdným řetězcem. Nic takového v Postgresu není. Z toho samozřejmě plynou zásadní rozdíly. Výraz NULL || 'Ahoj' vrací v Postgresu NULL, v Oracle 'Ahoj';
Podmínka '' = '' je v PostgreSQL vždy pravdivá a v Oracle vždy nepravdivá, jelikož je ekvivalentem NULL = NULL, a to musí být vždy NEPRAVDA. Vlastně jakýkoliv test na prázdný řetězec vždy selže, jelikož je ekvivalentem = NULL. Proto se v Oracle řetězce nikdy netestují na prázdný řetězec, ale pouze na NULL. Je to zvláštní (z mého pohledu neoraclisty), ale má to svou logiku, a bezpochyby je to velmi pragmatické, a také unikátní v celém SQL světě. Jakákoliv portace z Oracle s tím může mít problémy a je nutné s tímto chováním počítat.
Ve funkci DECODE je možné použít prázdný řetězec ve smyslu NULL hodnoty. Ora2pg překládá DECODE na CASE – k transformaci '' na NULL ovšem nedochází a výsledkem je nevalidní CASE výraz:
bes_jmmaj=# select case when true then 10 else '' end;
ERROR: invalid input syntax for integer: ""
ŘÁDKA 2: select case when true then 10 else '' end;
^
Při portaci aplikace z Oracle je docela praktické zachovávat alespoň částečně chování Oracle a nikdy neukládat prázdné řetězce. Extenze replace_empty_string. obsahuje generickou trigger funkci, která automaticky nahradí všechny ukládané prázdné řetězce hodnotou NULL.
Při portaci uložených procedur je nutné si ohlídat inicializaci řetězcových proměnných. V obou databázích jsou proměnné bez defaultní hodnoty inicializovány na NULL. Nicméně v PostgreSQL výraz NULL || něco zůstává NULLem, v Oracle je výsledkem něco. Proto se řetězcové proměnné v Pg, které slouží jako akumulátor, explicitně nastavují na prázdný řetězec.
Je dobré nepsat si vlastní (zbytečné) funkce pro práci s řetězci (kde se iteruje znak po znaku). Výsledek může být výrazně pomalejší než v PL/SQL (které je kompilováno do nativního kódu, a kde je práce se řetězci implementována odděleně od databázového engine). V PL/pgSQL se doporučuje (ono se to doporučuje i v PL/SQL) použít vestavěné funkce, kterých je v PostgreSQL více než dostatečná nabídka ( string_to_array, funkce pro práci s regexpy, ..)
Rozdíly v implementaci cyklů v PL/SQL a PL/pgSQL
I když je příkaz FOR v PL/pgSQL vizuálně hodně podobný obdobnému příkazu v PL/SQL, jeho implementace a chování je jiné. Někdy to může působit problémy. Liší se hlavně iterace přes dotaz:
V PL/SQL zápis
FOR c IN ( SELECT ... FROM ) LOOP END LOOP
Řídící proměnná c je kompozitní lokální automatická proměnná. Lokální – tj. její viditelnost je omezena na tělo cyklu. Automatická – tj. deklaruje ji systém vždy bez ohledu na aktivitu programátora. Často programátoři netuší, že tyto proměnné jsou automatické a poctivě (a zbytečně) je deklarují. Pak se plní seznamy nepoužitých proměnných.
V Postgresu v PL/pgSQL je zápis cyklu nad dotazem podobný:
FOR c IN SELECT ... FROM LOOP END LOOP
kde c je proměnná kompozitního typu, nebo typu RECORD, nebo seznam skalárních proměnných. Jedná se ale o normální ručně deklarovanou proměnnou (deklarované proměnné). V Oracle řídící proměnná zastíní všechny vně deklarované proměnné. V Postgresu nikoliv. Při troše smůly můžeme při portaci narazit na kolizi.
Kód v PL/SQL:
DECLARE
TYPE tc IS REF CURSOR;
c tc;
x ...
BEGIN
OPEN c FOR SELECT ...
FETCH c INTO x;
IF c%FOUND THEN
FOR c IN (SELECT ... )
LOOP
...
Ora2pg umí tento kód převést do PL/pgSQL. Nepoužívá vlastní blok obalující cyklus, kde by bylo možné deklarovat lokální řídící proměnnou cyklu. Místo toho deklaruje proměnnou na úrovni procedury. Výše uvedený kód by se převedl na:
DECLARE
c REFCURSOR;
x ..;
c RECORD;
BEGIN
OPEN c FOR SELECT ...
FETCH c INTO x;
IF FOUND THEN
FOR c IN SELECT
LOOP
Kolize identifikátorů je zde jasně vidět.
Pozor: řídící proměnná celočíselného cyklu je lokální automatická jak v PL/pgSQL, tak PL/SQL. Nekonzistence v PL/pgSQL je dána technologickým omezením v sedmičkových verzích PostgreSQL. Nyní, díky podpoře typu RECORD, by bylo technicky snadné plnohodnotně emulovat PL/SQL cyklus. Není to ale možné z důvodu zachování zpětné kompatibility.
Migrace schématu a dat
Díky Ora2pg tato úloha nepředstavuje žádný problém. Export z Oracle není úplně nejrychlejší. Nemám znalosti, abych zjistil, jestli je problém v Oracle nebo v Ora2pg. V konfiguraci Ora2pg lze nastavit mapování typů (nastavení DATA_TYPE). Ve výchozí konfiguraci mapuje date na timestamp. To jsme změnili na date->date. Sloupce, kde potřebujeme timestamp, altrujeme až v Postgresu, po importu schématu.
Import schématu je první krok celé portace. Dá se zvládnout během několika dnů. Je to vykopnutí projektu. Donutí vás to si nastavit, připravit a nakonfigurovat prostředí, a od diskuzí přejít k práci s viditelným výsledkem.
Pokud zjistíte, že v názvu tabulek nebo sloupců používáte klíčová slova Postgresu, je praktičtější je přejmenovat v Oracle. Lze si vynutit zápis takového identifikátoru mezi uvozovky, ale to je zase možná příčina dalších komplikací.
-- Příkazy jsou odladěné na PostgreSQL 9.6
-- PŘED IMPORTEM DAT
-- nastaví timestamp u všech sloupců pojmenovaných datzmeny a datvyr
SELECT format('alter table %I alter column %I type timestamp', table_name, column_name)
FROM information_schema.columns
WHERE column_name IN ('datzmeny','datvyr') \gexec
-- vypne všechny triggery (včetně těch, které kontrolují RI)
SELECT format('alter table %I disable trigger all', table_name)
FROM information_schema.tables
WHERE table_schema = 'public' \gexec
-- pro textové sloupce nahodí CHECK constrait blokující prázdné řetězce
SELECT format($$alter table %I add constraint %I check(%I <> '') not valid$$,
table_name,
table_name || column_name || '_noempstr', column_name)
FROM information_schema.columns
WHERE table_schema = 'public' AND data_type = 'character varying' AND table_name NOT LIKE 'v_%'\gexec
-- znevalidní constrainty RI
UPDATE pg_constraint co SET convalidated = false
FROM pg_class cl
WHERE co.conrelid = cl.oid AND cl.relnamespace = 2200 AND contype = 'f';
--PO IMPORTU DAT
-- zapne všechny triggery (včetně těch, které kontrolují RI)
SELECT format('alter table %I enable trigger all', table_name)
FROM information_schema.tables
WHERE table_schema = 'public' \gexec
-- validuje constrainty RI
SELECT format('alter table %I validate constraint %I' ||, cl.relname, conname)
FROM pg_class cl, pg_constraint co
WHERE convalidated = false AND co.conrelid = cl.oid AND cl.relnamespace = 2200 AND contype = 'f' \gexec
-- validuje kontrolu neprázdných řetězců
SELECT format('alter table %I validate constraint %I', conrelid::regclass, conname)
FROM pg_constraint
WHERE conname LIKE '%str' AND NOT convalidated \gexec
Migrace procedur a funkcí v PL/SQL
Úvodní poznámka: export je dost pomalý, pokud nejsou funkce a procedury zkompilované. Ora2pg si může vynutit kompilaci volbou COMPILE_SCHEMA na 1. Když už jsem u konfigurace – je praktické vypnout kontrolu těla funkce Postgresem volbou FUNCTION_CHECK na 0. Díky tomu do Postgresu dostaneme nevalidní funkce, resp. hlavně dostaneme do Postgresu jejich hlavičky, takže validní funkce, které se na tyto funkce odkazují, budou ok.
-- Ukázka výstupu z plpgsql_check_function ┌─────────────────────────────┬────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ proname │ plpgsql_check_function │ ╞═════════════════════════════╪════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ vraceni_penez_abo_all │ warning extra:00000:unused parameter "$6" │ │ del_platba_odpis │ warning extra:00000:unused parameter "$3" │ │ tvorba_vyzah_evidph │ warning extra:00000:unused parameter "$4" │ │ upom_slevy_penale │ warning extra:00000:unused parameter "$4" │ │ upom_slevy_penale │ warning extra:00000:unmodified OUT variable "$3" │ │ vraceni_penez_platba_kompen │ error:42883:40:assignment:function del_platba_rozpis(bigint, timestamp with time zone, character varying, unknown, bigint, unknown) does not exist │ │ vraceni_penez_platba_kompen │ Query: SELECT DEL_PLATBA_ROZPIS( mID_NAJPLATBY, trunc(clock_timestamp()), mChybatext, 'N', mLIDENT, 'N' ) │ │ vraceni_penez_platba_kompen │ -- ^ │ │ vraceni_penez_platba_kompen │ Hint: No function matches the given name and argument types. You might need to add explicit type casts. │ │ tvorba_ucesaldo_pohledavky │ error:42883:91:SQL statement:operator does not exist: bigint = character │ │ tvorba_ucesaldo_pohledavky │ Query: SELECT ID_UCESALDOPOH FROM UCESALDOPOH │ │ tvorba_ucesaldo_pohledavky │ WHERE ID_UCEDOKLAD = mID_UCEDOKLAD │ │ tvorba_ucesaldo_pohledavky │ AND ID_UCESALDOCEL = mID_UCESALDOCEL │ │ tvorba_ucesaldo_pohledavky │ AND KOD_CISUCET = mKOD_CISUCET LIMIT 1 │ │ tvorba_ucesaldo_pohledavky │ -- ^ │ │ tvorba_ucesaldo_pohledavky │ Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts. │ │ tvorba_ucesaldo_pohledavky │ error:42883:268:SQL statement:operator does not exist: bigint = character │ │ tvorba_ucesaldo_pohledavky │ Query: SELECT ID_UCESALDOPOH FROM UCESALDOPOH │ │ tvorba_ucesaldo_pohledavky │ WHERE ID_UCEDOKLAD = mID_UCEDOKLAD_FAK_PLATBA │ │ tvorba_ucesaldo_pohledavky │ AND ID_UCESALDOCEL = mID_UCESALDOCEL │ │ tvorba_ucesaldo_pohledavky │ AND KOD_CISUCET = mKOD_CISUCET │ │ tvorba_ucesaldo_pohledavky │ -- ^ │ └─────────────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (22 řádek)
Pro zobrazení dotčených řádků je určený příkaz \sf+ v psql
bes_jmmaj=# \sf+ vraceni_penez_platba_kompen
CREATE OR REPLACE FUNCTION bes_procs.vraceni_penez_platba_kompen(mid_najplatby bigint, meneurceno numeric, ...
RETURNS record
LANGUAGE plpgsql
SECURITY DEFINER
1 AS $function$
2 DECLARE
3
4 mID_NAJPLATBY_KOM NAJPLATBY.ID_NAJPLATBY%TYPE;
5 mID_MENA NAJPLATBY.ID_MENA%TYPE;
6 mID_CODDNAJVZT NAJPLATBY.ID_CODDNAJVZT%TYPE;
7 mID_CISTYPPLAT CISTYPPLAT.ID_CISTYPPLAT%TYPE;
...
39 IF mEROZ_CASTKA > 0 THEN
40 mChybatext := DEL_PLATBA_ROZPIS( mID_NAJPLATBY, trunc(clock_timestamp()), mChybatext, 'N', mLIDENT, 'N' );
41
42 SELECT coalesce(ROZEPSANO,0) INTO STRICT mEROZ_CASTKA
43 FROM V_NAJPLATBY_VSE
44 WHERE ID_NAJPLATBY = mID_NAJPLATBY;
...
Související dotazy:
--Kontrola funkcí
SELECT p.proname, plpgsql_check_function(p.oid, fatal_errors => false, extra_warnings => true, others_warnings => true)
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p ON pronamespace = n.oid
JOIN pg_catalog.pg_language l ON p.prolang = l.oid
WHERE l.lanname = 'plpgsql' AND p.prorettype <> 2279;
--Kontrola triggerů
SELECT p.oid, p.proname, tgrelid::regclass, cf.*
FROM pg_proc p
JOIN pg_trigger t ON t.tgfoid = p.oid
JOIN pg_language l ON p.prolang = l.oid
JOIN pg_namespace n ON p.pronamespace = n.oid,
LATERAL plpgsql_check_function(p.oid, t.tgrelid, extra_warnings => false, others_warnings => true) cf
WHERE n.nspname = 'public' AND l.lanname = 'plpgsql';
Aktuálně v PL/pgSQL není analogie session package proměnných. Existuje workaround, kdy se jako globální proměnná použije postgresová session zákaznická konfigurační proměnná. Není to nic extra, ale funguje to. Bohužel v portované aplikaci se package proměnné používají poměrně hodně, a většinou ne úplně čistě, jako další parametry procedur, funkcí a triggerů. Refaktoring by byl naprosto na místě, ovšem pouštět se bez unit testů do takové akce znamená riskovat příliš.
Během několika let by v PostgreSQL mohly být session schema proměnné (podobně jako v DB2 viz příkaz CREATE VARIABLE). Pak by emulace package promenných v PostgreSQL byla triviální. Není ale čas čekat, až se schema proměnné objeví v PostgreSQL.
Přístup ke schématu v Oracle z PostgreSQL
Někdy může přijít k užitku možnost přístupu k databázovým objektům v Oracle z Postgresu. Kolega tak testoval výsledky pohledů a validoval jejich převod z Oracle pluskové syntaxe do ANSI SQL. FDW driver pro Oracle funguje na výbornou. Trochu komplikovanější byla instalace. S tímto driverem se PostgreSQL stává klientem Oracle. Tudíž musí mít přístup ke knihovnám driveru a musí mít nastavené všechny potřebné proměnné prostředí. V našem případě stačilo nastavit: ORACLE_BASE, ORACLE_SID, ORACLE_HOME, LD_LIBRARY_PATH. Jakmile je driver nakonfigurovaný a dokáže se připojit k Oracle, pak už se jen zavolá příkaz IMPORT FOREIGN SCHEMA a máme zpřístupněné tabulky, pohledy materializované pohledy z Oracle.
CREATE EXTENSION oracle_fdw ; CREATE SERVER oradb FOREIGN DATA WRAPPER oracle_fdw OPTIONS (dbserver '//ora2pg/bes'); CREATE USER MAPPING FOR bes SERVER oradb OPTIONS (user 'BES_JMMAJ', PASSWORD '***'); IMPORT FOREIGN SCHEMA "BES_JMMAJ" FROM SERVER oradb INTO fdw;
Poznámka: Máme pouze jeden vývojový server, na kterém běží Oracle i PostgreSQL současně.
Performance
Zpracování dotazu v Oracle a PostgreSQL probíhá hodně podobným způsobem, a tak i rychlost dotazů by měla korelovat. Někdy bude rychlejší Oracle, jindy zas Postgres. Pokud se v nested loopu volají zákaznické funkce, pak se může ukázat výhoda kompilace do nativního kódu Oracle. V nested loopech jde o mikrosekundy.
Obecně platí pravidlo, že by funkce neměly obalovat jednoduché SQL příkazy. Zvlášť, pokud se tyto funkce volají z komplikovanějších SELECTů. Jednak se tím snižuje prostor pro optimalizaci (SQL se uvnitř funkce optimalizuje izolovaně). Hlavně ale dochází ke generování a počítání velkého množství zanořených SQL příkazů, a to má dost velkou režii. Pokud jsem takovéto funkce z dotazů vyházel a nahradil poddotazy, na 20GB databázi jsem se dostal z několika minut na několik málo desítek vteřin. Jedná se o poměrně běžnou chybu (pokud vývojář nemá už trochu hlubší znalosti fungování databází, tak vůbec netuší, co dělá špatně), bohužel, někdy s fatálním dopadem na rychlost dotazů.
--ŠPATNĚ .. problém s výkonem -- jak na Oracle tak na PostgreSQL CREATE OR REPLACE FUNCTION usernamefx(int) RETURNS text AS $$ BEGIN RETURN (SELECT username FROM users WHERE id = $1) END; $$ LANGUAGE plpgsql STABLE STRICT; SELECT ..., usernamefx(userid), ... FROM data -- SPRÁVNĚ .. použij JOIN SELECT ..., username, ... FROM DATA d LEFT JOIN users u ON d.userid = u.id
V případě takovýchto funkcí je PostgreSQL mnohem citlivější než Oracle na dynamické SQL. To je asi jediná příležitost, kde se dnes ukáže pozitivní efekt plan cache. Jak se později ukázalo, tak dynamické SQL bylo naprosto zbytečné a po odstranění nebyl výrazný rozdíl v rychlostech SQL. Dynamického SQL jsme se zbavili, nevhodně použitých funkcí už nikoliv. To už by byl masivní zásah do aplikace, a to v tuhle chvíli není na pořadu dne. Primárním cílem je mít možnost provozovat aplikaci na PostgreSQL s podobným výkonem jako na Oracle.
Museli jsme vyřešit problém s rychlostí u komplexních komplikovaných pohledů. Jednalo se pohledy obsahující desítky JOINů a poddotazů (začínám být nervózní, jakmile se mi explain nevejde na obrazovku). Zde se nevešly na několik desítek obrazovek. Řešení nebylo až tak komplikované. Pomohlo výrazné zvýšení limitů planneru JOIN_COLLAPSE_LIMIT a FROM_COLLAPSE_LIMIT. Tyto hodnoty udávají, do jaké hloubky se má prohledávat prostor možných řešení zpracování dotazu. S výchozí hodnotou 8 jsme se zdaleka nedostali k optimálnímu plánu. Tyto hodnoty jsme museli nastavit na 50. Tato hodnota je daleko za všemi běžnými (a rozumnými) doporučeními (doporučená hodnota je max. 16). Čím jsou tyto hodnoty větší, tím je větší šance na nalezení optimálního plánu, zároveň ale rychle roste náročnost plánovaní dotazu – čas/paměť/CPU. Pokud zvýšíte tyto proměnné, tak je důležité NEZVYŠOVAT hodnotu proměnné GEQO_THRESHOLD. Ta představuje limit pro použití stochastického planneru.
Pokud by se při vysokém nastavení JOIN_COLLAPSE_LIMIT a FROM_COLLAPSE_LIMIT a komplikovaném dotazu, použil deterministický planner, tak by pravděpodobně došlo k vyčerpání paměti a kolapsu serveru, a to nikdo určitě nechce. Deterministický planner by měl garantovat optimální prováděcí plán, je ale náročný na CPU a paměť. Stochastický planner, který se pouští u komplexnějších dotazů, je výrazně úspornější a rychlejší. Jeho výsledkem je ale pouze „nejlepší nalezený“ prováděcí plán. V našem případě, silně postiženém nevhodným použitím funkcí, se stochastický planner osvědčuje a díky své rychlosti dokáže částečně eliminovat chybějící plan cache v PostgreSQL. U extrémních dotazů s extrémním nastavením limitů planneru by se už nějaká plan cache hodila.
V Postgresu je možnost použít explicitní předpřipravené dotazy – prepared statements (příkaz PREPARE), a vytvořit si a udržovat vlastní plan cache (která není sdílená). To by ale znamenalo větší zásahy do klienta, a pro to není v tuhle chvíli motivace. Rychlost Postgresu je dostatečná. Je ale otázkou, jestli by nebylo efektivnější se zbavit nevhodně použitých uživatelských funkcí. Potom by se vůbec nemusely řešit problémy s plannerem.
Shrnutí
Každá databázová aplikace má svá specifika a snad ani nemá cenu se snažit o generalizaci. Vždy je na ni vidět doba vzniku, stáří a rozsah aplikace, znalosti a zkušenosti vývojářů, požadavky zákazníků, charakter dat. Relativně úspěšně jsme portovali aplikaci, která je velká rozsahem a množstvím uložených procedur. Kvalita kódu nevybočuje ze zdejšího průměru a tudíž bylo nutné napřed pročistit kód. Na druhou stranu vývojáři záměrně používali jednodušší základní podmnožinu SQL, PL/SQL, a to určitě zjednodušilo migraci. Tato a podobné aplikace (myslím si, že podobných aplikací bude hodně) je do Postgresu portovatelná a nákladově udržitelná je i déle trvající souběžná podpora Oracle a PostgreSQL, která je často nutným požadavkem.
