powstał właśnie serwis doxygen.postgresql.org, dzięki któremu można w dosyć wygodny sposób poprzeglądać źródła postgresa, zobaczyć jakie są podefiniowane struktury, gdzie, gdzie używane itd.

jakiś czas temu pisałem o tym, że mysql skaluje się gorzej od postgresa. bluszcz stwierdził, że wolałby testy z falcon'em lub soliddb.

potem pokazałem testy z falcone'em.

a dziś – dziś dam wam linka do artykułu o teście soliddb. w tym porównania wydajności soliddb do innodb. wiem, że wydajność a skalowalność to co innego. ale niestety – nikt nie zrobił (albo nie trafiłem na) testy skalowalności soliddb, więc dzielę się tym co mam.

wyniki? fajny engine. ma transakcje, klucze obce, obsługa planów optymistycznych lub pesymistycznych.

minusy? wydajność. jest gorzej niż w innodb (o myisam nie wspominając). insert – ponad 2 razy wolniej. update'y – 6 razy wolniej. delete'y trochę szybciej od innodb, ale wolniej niż myisam.

to jeszcze nie koniec. największym minusem soliddb jest fakt, że ma całkowicie zwaloną (w/g mnie, w/g nich to tylko “missing feature") obsługę locale. w dużym skrócie – indeksy możesz zakładać tylko przy standardowym locale na tekstach zawierających jedynie znaki z latin1. koniec, kropka. chcesz “ą"? no cóż. nie możesz go zindeksować.

to ja mimo wszystko podziękuję i zostanę przy postgresie.

na listę announce'ów poszedł mail informujący by *nie* instalować ostatnio wypuszczonych wersji postgresa (8.2.2 i 8.1.7). przyczyna: jest błąd w obsłudze typów tekstowych.

werjsa 8.0 i wcześniejsze podobno nie mają problemów, ale jeszcze są testowane.

nowe wersje nie zostały jeszcze oficjalnie obwieszczone, ale na ftp'ie już są.

muszę przyznać, że za mojej pamięci to pierwsza taka sytuacja.

w 8.2 pojawiły się agregaty wielokolumnowe. muszę przyznać, że nie bawiły mnie za bardzo dopóki nie trafiłem na swój dawny post na google groupsach.

tyczy się on tego jak zapisać agregat łączący wiele tekstów w jeden – rozdzielony znakiem definiowanym w zapytaniu.

nie był to kod ładny.

saint zaproponował inne rozwiązanie, ale miało one w/g mnie dwie wady:

1. konieczność obcinania końcowego separatora.
2. zwraca pusty string a nie null w przypadku odpalenia na pustym zbiorze – a ja wolę null'a 🙂

oczywiście obie te rzeczy można prosto poprawić lekko modyfikując zapytanie, ale wolałbym by logika była w funkcjach.

no i wreszcie. dzięki 8.2 można zrobić coś takiego:

CREATE OR REPLACE FUNCTION agg_text_sum(in_base TEXT, new_text TEXT, separator TEXT) RETURNS TEXT AS $BODY$
DECLARE
BEGIN
IF in_base IS NULL THEN
RETURN new_text;
END IF;
RETURN in_base || separator || new_text;
END;
$BODY$ LANGUAGE 'plpgsql';

CREATE aggregate SUM(TEXT, TEXT) (
sfunc = agg_text_sum,
stype = TEXT
);

a potem można to stestować:

CREATE TABLE table_names AS SELECT oid::INT4 AS id, relname::TEXT AS TABLE_NAME FROM pg_class  WHERE relkind = 'r';
SELECT SUM(TABLE_NAME, ', ') FROM table_names;

i już. separator można dowolnie zmieniać

nie jest to może szczytowe osiągnięcie w inżynierii oprogramowania, ale może się przydać.

na ftp'ie pojawiły się nowe wersje postgresa – 8.2.2, 8.1.7, … – ogólnie – nowa wersja z każdej wersji x.y aktualnie utrzymywanej (czyli od 7.3 wzwyż).

co ciekawe – nie pojawiła się żadna informacja o tej wersji na webie ani na listach dyskusyjnych – zakładam, że oficjalna informacja wyjdzie za chwilę.

w paczce z postgresem, w danych nt. wydań jest data wydania: 2007-02-05. czyli za 3 dni.

a co jako zmiany?

poprawki związane z bezpieczeństwem – w tym bardzo ważna pozwalająca klientowi czytanie pamięci procesu postgresa – przez co można dostać dostęp do danych do których nie powinno być dostępu.

poza tym – jak zawsze przy nowych wersjach gdzie podbijana jest ostatnia cyfra – żadnych nowych funkcjonalności. trochę patchy przyspieszających, trochę poprawek łatających jakieś błędy.

ogólnie – warto instalować.

jak właśnie przeczytałem na blogu neila conwaya, jest on teraz maintainerem (jakiś pomysł na tłumaczenie na polski) projektu pgmemcache.

dzięki temu, ten zapomniany nieco projekt ma szanse z powrotem wrócić do aktywnego rozwijania.

jeśli nie wiecie co to – pgmemcache jest interfejsem do memcached – interfejsem wbudowanym w postgresa. dostępnego z poziomu sql'a.

a co to memcache? rozproszony, bardzo szybki system cache'a. bazuje na przechowywaniu par (klucz, wartość) w rozproszonym środowisku (na wielu maszynach) – tylko w ram'ie – dzieki czemu jest baaaardzo szybki.

dzięki pierwszej poprawce neila, pgmemcache kompiluje się wreszcie na 8.1 i 8.2. życie stało się słodsze.

ukazała się nowa wersja – 2.0, pakietu dbi-link. jest to pakiet umożliwiający podłączanie postgresa do innych baz danych (innych instancji postgresa, albo po prostu zupełnie innych baz), w sposób możliwie przezroczysty dla użytkownika.

przy użyciu tego pakietu, dostajemy wirtualne tabelki na których możemy prawie normalnie pracować  (jeszcze nie można join'ować), nie przejmując się tym, że tak naprawdę są to tabelki z innych baz.

wersja 2.0 zawiera olbrzymie zmiany idące w kierunku poprawienia działania, stabilności oraz ogólnej odporności.

jak się sprawdzi – za wcześnie jeszcze na wyrokowanie, ale wygląda mocno interesująco.

jeśli chodzi o to do jakich baz można się podłączyć – praktycznie do każdej. jak nazwa wskazuje do immplementacji samego połączenia do zdalnej bazy jets używana biblioteka dbi z perla, a ona ma sterowniki praktycznie do wszystkiego.

czy stanęliście kiedyś przed problemem wylosowania rekordu z tabeli? dowolnego rekordu?
oczywistym pomysłem jest:

# SELECT * FROM tabelka ORDER BY random() LIMIT 1;

no ale to jest wolne. wymaga posortowania całej tabeli. co w najlepszym układzie ma złożoność "n log n".
przykładowo u mnie na testowej tabelce trwało to 90 sekund! (1.7 miliona rekordów).
no nie za dobrze.
niektórzy mogą sugerować takie rozwiązanie:

1. znajdź maksymalne
2. SELECT * FROM tabelka WHERE id <= random() * maksymalne_id limit 1;

na oko jest ok. tzn. akurat nie jest, bo random jest funkcją volatile, i trzeba by raczej … WHERE id <= (select random() * maksymalne_id) LIMIT 1, ale to już szczegół.
czemu to jest złe?
bo wprowadza pewien istotny problem. jeśli numeracja pola id w naszej tabelce zawiera dziury (czyli jest takie id, które jest większe od minimalnego i mniejsze od maksymalnego, dla którego nie ma rekordu) – to te losowane rekordy wcale nie będą dobrze losowane.
jako ekstremalny przykład (ale dobrze pokazujący rzeczywistość) podajmy tabelkę z dwoma rekordami, o id odpowiednio: 1 i 100. rekord z id = 1 będzie wypadał 99 razy częściej niż rekord z id = 100!.
cóż więc pozostaje? siąść i płakać?
nie.
można użyć inteligencji. czyli funkcji/procedury.
przykładowo taka funkcja:

CREATE OR REPLACE FUNCTION random_record() RETURNS tabelka AS $BODY$

DECLARE

    id_min INT8;

    id_max INT8;

    range INT8;

    temp_id INT8;

    temprec RECORD;

BEGIN

    SELECT min(id) INTO id_min FROM tabelka;

    SELECT max(id) INTO id_max FROM tabelka;

    range:= 1 + ( id_max - id_min );

    LOOP

        temp_id := id_min + (random() * range::float8)::INT8;

        SELECT * INTO temprec FROM tabelka WHERE id = temp_id;

        IF found THEN

            RETURN temprec;

        END IF;

    END LOOP;

END;

$BODY$ language 'plpgsql';

co ona robi?zwraca losowy rekord. całkowicie losowy – każdy rekord ma te same szanse bycia wylosowanym.
warunki brzegowe? pole id musi być unikatowe (szokujące, nie?). no i: im więcej dziur w numeracji tym wolniej działa. ale co znaczy wolniej?
ta moja testowa tabelka ma takie dane:

# select min(id), max(id), count(*) from tabelka;

 min |   max    |  count

-----+----------+---------

   3 | 36574227 | 1721217

(1 row)

czyli jak widać – dziur jest sporo. w szczególności – dziur jest 21 razy więcej niż istniejących rekordów!
przypomnę, że

select * from tabelka order by random() limit 1;

działało na tej tabelce w około 90 sekund.
ile czasu zajmuje to funkcji?
6 kolejnych wywołań. czasy odpowiednio: 124.700, 141.442, 201.708, 94.413, 145.128, 110.076. milisekund!
jak widać – jest szybko.
problemem tej funkcji jest to, że teoretycznie może się zdarzyć, że nigdy się nie skończy. ale w/g mnie jest to gdybanie. zresztą – zawsze można dorobić warunek, że jeśli np. wykonano już 1000 strzałów niecelnych, to zwróćmy pierwszy rekord z brzegu.
i już.
czy można to jakoś dopalić?
tak.
jeśli wiecie, że tabelka w której szukacie ma dużo dziur, to dodajcie do niej jedno pole:

create sequence random_thing_seq;

alter table tabelka add column random_thing int8;

alter table tabelka alter column random_thing set default nextval('random_thing_seq');

update tabelka set random_thing = nextval('random_thing_seq') where random_thing is null;

alter table tabelka alter column random_thing set not null;

create unique index ui_random_thing on tabelka (random_thing);

i potem używajcie w funkcji random_thing a nie id.
cel ćwiczenia?
jak sie pojawi za dużo dziur w numeracji (random_thing też będzie miał dziury) to zawsze możecie:

update tabelka set random_thing = nextval('random_thing_seq');

i już dziur nie ma,
a ponieważ random_thing nie jest do niczego innego używane – jest to w pełni bezpieczne.
oczywiście po takim update'cie dobrze jest zrobić vacuum'a. a najlepiej vacuum full'a.

eh. od dawna mi tego brakowało w postgresie. i okazało się, że niepotrzebnie, bo już od 8.1 jest! wyłączanie triggerów. pojedynczo. każdy można tymczasowo wyłączyć.
na wypadek gdybyście nie wiedzieli:

# ALTER TABLE test DISABLE TRIGGER xxx;

gdzie xxx to nazwa triggera, lub słowo "ALL" jeśli chcemy wyłączyć wszystkie triggery, lub słowo "USER" jeśli chcemy wyłączyć wszystkie triggery, ale pozostawić działające klucze obce (które są realizowane triggerami).
włączenie z powrotem to:

# ALTER TABLE test ENABLE TRIGGER xxx;

jedynym problemem z tym polega na tym, że w psql'u, przy robieniu \d tabeli nie widać które triggery są wyłączone:

# create table x (id serial primary key, wstawiana text);

# CREATE TRIGGER test AFTER INSERT OR UPDATE ON x FOR EACH ROW EXECUTE PROCEDURE test();

# CREATE TRIGGER test_u AFTER UPDATE ON x FOR EACH ROW EXECUTE PROCEDURE test_u();

# alter table x disable trigger test;

# \d x

                           Table "public.x"

  Column   |  Type   |                   Modifiers

-----------+---------+------------------------------------------------

 id        | integer | not null default nextval('x_id_seq'::regclass)

 wstawiana | text    |

Indexes:

    "x_pkey" PRIMARY KEY, btree (id)

Triggers:

    test AFTER INSERT OR UPDATE ON x FOR EACH ROW EXECUTE PROCEDURE test()

    test_u AFTER UPDATE ON x FOR EACH ROW EXECUTE PROCEDURE test_u()

można sobie ręcznie sprawdzić które triggery są wyłączone:

# select t.tgname, t.tgenabled from pg_class c join pg_trigger t on c.oid = tgrelid where c.relname = 'x';

 tgname | tgenabled

--------+-----------

 test_u | t

 test   | f

(2 rows)

w 8.3 na szczęście jest to już poprawione:

# \d x

                           Table "public.x"

  Column   |  Type   |                   Modifiers

-----------+---------+------------------------------------------------

 id        | integer | not null default nextval('x_id_seq'::regclass)

 wstawiana | text    |

Indexes:

    "x_pkey" PRIMARY KEY, btree (id)

Triggers:

    test_u AFTER UPDATE ON x FOR EACH ROW EXECUTE PROCEDURE test_u()

Disabled triggers:

    test AFTER INSERT OR UPDATE ON x FOR EACH ROW EXECUTE PROCEDURE test()

słodko 🙂