Přechod z Solidu na PostgreSQL

Pavel Stěhule

Pro svého zaměstnavatele jsem řešil port IS z RDBMS Solid na RDBMS PostgreSQL 7.4.

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 xxx(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 xxx_ret as (r1 t1, r2 t2, ...);
  CREATE FUNCTION xxx(p1 t1) RETURNS SETOF xxx_ret AS '
    DECLARE r xxx_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í.