postgresql 8.2 feature freeze!

dowiedziałem się właśnie, że postgres 8.2 wszedł właśnie w fazę feature freeze.

konsekwencje:

  • można się spodziewać relatywnie niedługo 8.2 stable.
  • nowe super feature'y na serwerach produkcyjnych 🙂

co dokładnie będzie nowego – postaram się jeszcze dziś napisać.

jak dobrze pisać update’y do bazy danych?

teoretycznie napisanie polecenia update jest bardzo proste:

update tabelka set pole = ‘xxx' where id = yy;

tylko czy to na pewno słuszny sposób?

otóż nie.

problem polega na tym, że w postgresie działa cos co nazywa się mvcc. nie wnikając w szczegóły (jak kogoś z was bardzo interesują, odsyłam do dokumentacji postgresa) – powoduje to, że każdy update to tak naprawdę delete + insert.

efekty – tabelka rośnie. rosną też indeksy na polach które nie zostały zaktualizowane!

zostałem przez ten efekt dosyć boleśnie “ugryziony" gdy okazało się, że dosyć spora tabelka (1.5 miliona rekordów, 5 gigabajtów wielkości) rośnie mi jak na drożdżach, bo bardzo często robię w niej update jednego pola. a że na tabelce jest 15 indeksów to efekt się multiplikował.

rozwiązanie? wykonywanie update'a tylko gdy naprawdę jest konieczny:

update tabelka set pole = ‘xxx' where id = yy and pole <> ‘xxx';

sprawa się nieco komplikuje gdy pole może być null'em, ale na szczęście jest (mało znany) operator “is distinct from", i możemy zapisać zamiast:

update tabelka set pole = ‘xxx' where id = yy and (pole is null or pole <> ‘xxx');

to:

update tabelka set pole = ‘xxx' where id = yy and pole is distinct from ‘xxx';

co istotne – jeśli już modyfikujemy choć jedno pole – to modyfikacje kolejnych nie mają tego złego efektu.

więc nie musimy robić:

update tabelka set pole = ‘xxx' where id = yy and pole is distinct from ‘xxx';

update tabelka set pole_2 = ‘x2' where id = yy and pole_2 is distinct from ‘x2';

itd.

wystarczy (i zdecydowanie lepiej zadziała):

update tabelka set pole = ‘xxx', pole_2 = ‘x2' where id = yy and (pole is distinct from ‘xxx' or pole_2 is distinct from ‘x2');
dodam jeszcze, że o ile vacuum świetnie sobie radzi z odzyskiwaniem miejsca po update'ach w plikach tabel, o tyle z miejscem w plikach indeksów nie jest tak słodko 🙁
inną opcją (dodatkowo pomagającą) jest:

jesli macie dużą tabelkę (kilkanaście kolumn, sporo danych) i tylko jedno/kilka pól jest często aktualizowanych, przy czym reszta danych (w szczególności indeksy) nie jest dotykana – warto się zastanowić by te kolumny wydzielić do oddzielnej tablicy i połączyć relacją 1-do-1.

od razu ostrzegam: to nie zawsze da dobry rezultat. ale może warto spojrzeć. zmniejszone pliki to zmniejszone zapotrzebowanie na pamięć do cache'a dyskowego. a więc cały system zaczyna szybciej działać.

chcesz poznać pl/pgsql – proszę bardzo.

kilka dni temu odbyło się oficjalne zawiązanie nowej grupy użytkowników postgresql'a – portland postgresql users group. na inauguracyjne spotkanie został zaproszony w charakterze mówcy david wheeler – znany głównie jako osoba zaangażowana w rozwój bricolage – opartego na postgresie systemu klasy cms.

david na temat swojego wystąpienia wybrał sobie pl/pgsql.

przygotował sporą prezentację i wystąpił. wstęp został przyjęty bardzo dobrze. a slajdy zostały udostępnione na sieci. jeśli nie pisaliście nic w tym języku, lub nie czujecie się w nim do końca pewnie – zdecydowanie warto obejrzeć.

jak monitorować postgresa?

po udanym postawieniu serwera, przychodzi kolej na to by go nadzorować. spora część ludzi używa w tym celu softu który automatycznie robi wykresy różnych parametrów. przykładem (którego używam np. ja) jest mrtg. są też inne pakiety, lepsze czy gorsze. używam i lubię mrtg.

mrtg potrafi rysować wykresy nie tylko ruchu na interfejsach, ale praktycznie dowolnych danych podawanych w postaci liczbowej.

dzięki temu używam go do rysowania wykresów:

  • loadu systemowego
  • zużycia pamięci
  • zużycia procesorów
  • wolnego miejsca na dyskach
  • ruchu na interfejsach sieciowych
  • ruchu na swapie (in/out)

to monitoruję na każdym serwerze – niezależnie czy to webserwer, serwer aplikacyjny z jbossem, webproxy, firewall czy serwer bazodanowy.

ale na serwerach bazodanowych przydają się też inne parametry.

zanim przejdę do ich omówienia krótki wtręt.

mrtg potrafi pobierać dane poprzez uruchamianie programów które mu dane zwracają, lub poprzez odpytywanie zdalnego serwera snmp. wybrałem to drugie rozwiązanie, bo daje mi trochę więcej możliwości – np. mogę wykresy rysować na zupelnie innej maszynie niż ta której wykresy dotyczą.

do każdej znanej mi implementacji snmp jest możliwość podpięcia zewnętrznych skryptów/programów pod tzw. mib'y. dzięki temu mogę dowolnie rozszerzać funkcjonalność snmp i dodawać monitorowanie parametrów o których twórcy snmpd nie mieli pojęcia 🙂

pierwszym niestandardowym parametrem który monitoruję na serwerach bazodanowych jest ilość operacji i/o w podsystemie dyskowym.

postgresql jest mocno czuły na obciążenie dysków i tzw. iowaity powodują, że maszyna zachowuje się jakby ktoś jej podciął skrzydła. procesor jest wolny, wszystko działa ok, ale postgres się wlecze.

w używanym przeze mnie net-snmpd, odpowiednie wartości są dostępne pod mib'ami: ssIORawSent.0 i ssIORawReceived.0.

odpowiedni kawałek configu mrtg:

Target[db_sys_iorawsent]: ssIORawSent.0&ssIORawSent.0:public@db
Options[db_sys_iorawsent]: growright
MaxBytes[db_sys_iorawsent]: 100000000
Title[db_sys_iorawsent]: IORawSent at db
PageTop[db_sys_iorawsent]: IORawSent at db

(można umieścić oba wykresy na jednym obrazku, ale z przyczyn pozamerytorycznych wolę mieć każdy oddzielnie).

następnymi (i ostatnimi ogólno systemowymi) parametrami jakie monitoruję jest ilość context-switchy i przerwań w systemie.

dostępne jest to u mnie pod mibami: ssRawContexts.0 i ssRawInterrupts.0

i teraz przechodzimy do części najzabawniejszych.

co monitorować w samym postgresie.

przede wszystkim – jak? przygotowałem sobie prosty program który wykonuje jedno zadane polecenie w bazie danych i zwraca wynik na stdout. ten skrypt podłączam do snmp wykorzystując funkcję “exec" z snmpd.conf.

i lecimy z zapytaniami:

  1. select pg_database_size(‘nazwa_bazy_której_wielkość_chcesz_monitorować');
    zwraca wielkość bazy w bajtach
  2. select cast(extract(epoch from now() – query_start) * 1000 as int8) from pg_stat_activity where current_query !~ ‘stats_command_string w postgresql.conf
  3. select pg_relation_size(‘nazwa_relacji');
    zwraca wielkość w bajtach dowolnej relacji – czyli tabeli czy indeksu. warto w ten sposób monitorować tabele czy indeksy które mają tendencje do puchnięcia (np. jakieś tabele z kolejkami zadań do wykonania).
  4. select sum(xact_commit) + sum(xact_rollback) from pg_stat_database;
    zapytanie to pokazuje ile transakcji (od ostatniego restartu) postgres wykonał (niezależnie czy były one zatwierdzone czy wycofane).
    zbierając tę ilośc co jakiś czas można prosto policzyć ile transakcji na sekundę postgres robi. można też zmodyfikować to zapytanie tak aby liczyło nie transakcje w ogóle przetworzone przez postgresa tylko w konkretnej bazie – modyfikacje zapytania pozostawiam czytelnikom, jest trywialna 🙂

jeśli używacie replikacji, a do replikacji slony'ego, warto monitorować opóźnienie replikacyjne. opóźnienie to wyraża się w dwóch wartościach:

  • lag_events
  • lag_time

lag_events jest to ilość zdarzeń modyfikujących bazę które jeszcze nie zostały przesłane na bazy podrzędne. lag_time jest to opóźnienie replikacji wyrażone jako czas.

wyciągnąć obie wartości można tak:

SELECT st_lag_num_events, st_lag_time FROM _slony.sl_status

przy czym aby móc to rysować lepiej jest przeliczyć lag_time na sekundy:

SELECT st_lag_num_events, CAST(EXTRACT(epoch FROM st_lag_time) * 1000 AS int8) FROM _slony.sl_status

zwrócić należy uwagę, iż jeśli lag_events jest równy 0, to lag_time jest nieistotny.
na koniec coś na zewnątrz sql'a.

w postgresql.conf jest taka opcja: log_min_duration_statement.

pozwala ona na logowanie długich zapytań. tu uwaga – ta opcja jest zupełnie niezależna od log_statement czy log_duration!

wartość opcji log_min_duration_statement oznacza liczbę milisekund jaką zapytanie się wykonywało by zostało uznane za zbyt długie, i zalogowane. ja osobiście wpisuję tam wartość 1000 – co powoduje, że każde zapytanie trwające powyżej 1 sekundy zostanie zapisane w logach. np. tak:

[2006-07-14 00:00:04 CEST] [25989] LOG: duration: 1133.235 ms statement: SELECT id, file_extension FROM flickr_images WHERE id > ‘0' AND size_x_orig = 0 and status >= 0 ORDER BY id ASC

zwracam tylko uwagę na fakt iż to, że zapytanie się długo wykonuje niekoniecznie jest problemem zapytania. najczęstszą sytuacją jest to, że pojawia się zapytanie które wykonuje się dosyć długo, ale poza tym przyblokowuje ono zupełnie inne zapytania – które też nagle zaczynają się długo wykonywać.

tak więc nie należy patrzeć na listę “długich" zapytań jako listę rzeczy do poprawienia, tylko jako na wsad do analizy statystycznej jakie zapytania pojawiają się najczęściej. i dlaczego.

to zasadniczo kończy temat. monitorować można sporo więcej – choćby ilość procesów postgresa, czy ilości odczytów via seq-scan czy index-scan. natomiast wydaje mi się, że te parametry mogą być istotne “punktowo", a niekoniecznie muszą być od razu rysowane.

przechowywanie historii zmian

zapewne każdy kto choć raz robił coś ciut większego w bazach danych zetknął się z problemem przechowywania zmian w tabelkach.

ci co nie mają triggerów, muszą używać kodu po stronie aplikacji klienckiej. co ma swoje wady.

z drugiej strony – w postgresie triggery są. i działają 🙂 więc można do roboty zaprząc postgresa.

załóżmy, że mamy bardzo prostą tabelkę z danymi:

CREATE TABLE objects (
id BIGSERIAL,
o_type TEXT NOT NULL DEFAULT '',
PRIMARY KEY (id)
);
chcemy zapisywać sobie modyfikacje. robimy więc tabelkę na zapis historii zmian:
CREATE TABLE history_objects (
id BIGSERIAL,
modified_on TIMESTAMP NOT NULL DEFAULT now(),
modification_type TEXT NOT NULL DEFAULT '',
modified_id INT8,
modified_o_type TEXT,
PRIMARY KEY (id)
);

rekordy w history_object zawierają:

  • modified_on – kiedy dana modyfikacja miała miejsce
  • modification_type – typ modyfikacji – insert, update czy delete.
  • modified_id – id zmodyfikowanego rekordu
  • modified_o_type – o_type w tym rekordzie

proste i miłe. tworzymy więc dwa triggery:

CREATE OR REPLACE FUNCTION trg_objects_ui() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
INSERT INTO history_objects (modified_id, modified_o_type, modification_type) VALUES (NEW.id, NEW.o_type, TG_OP);
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_objects_ui AFTER INSERT OR UPDATE ON objects FOR EACH ROW EXECUTE PROCEDURE trg_objects_ui();
 
CREATE OR REPLACE FUNCTION trg_objects_d() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
INSERT INTO history_objects (modified_id, modified_o_type, modification_type) VALUES (OLD.id, OLD.o_type, TG_OP);
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_objects_d AFTER DELETE ON objects FOR EACH ROW EXECUTE PROCEDURE trg_objects_d();i testujemy:

testujemy:

> SELECT * FROM objects;
id | o_type
----+--------
(0 ROWS)
 
> SELECT * FROM history_objects;
id | modified_on | modification_type | modified_id | modified_o_type
----+-------------+-------------------+-------------+-----------------
(0 ROWS)
 
> INSERT INTO objects (o_type) VALUES ('bleble');
INSERT 0 1
 
> INSERT INTO objects (o_type) VALUES ('bleble2');
INSERT 0 1
 
> UPDATE objects SET o_type = 'xxx' WHERE o_type = 'bleble';
UPDATE 1
 
> DELETE FROM objects WHERE o_type = 'bleble2';
DELETE 1
 
> SELECT * FROM objects;
id | o_type
----+--------
1 | xxx
(1 ROW)
 
> SELECT * FROM history_objects;
id |        modified_on         | modification_type | modified_id | modified_o_type
----+----------------------------+-------------------+-------------+-----------------
1 | 2006-07-15 12:14:56.138695 | INSERT            |           1 | bleble
2 | 2006-07-15 12:14:56.138695 | INSERT            |           2 | bleble2
3 | 2006-07-15 12:14:56.138695 | UPDATE            |           1 | xxx
4 | 2006-07-15 12:14:56.138695 | DELETE            |           2 | bleble2
(4 ROWS)

działa ślicznie.

ale jest jedno ale.

czasem chcielibyśmy logować więcej informacji. w projekcie nad którym pracują koledzy w firmy chcieli logować nazwę akcji webowej (coś jakby url) i nazwę użytkownika które spowodowały taką modyfikację.

tu pojawiają się schody – baza danych nie zna urla. baza danych nie zna nazwy użytkownika który się zalogował na www – wie tylko jaki user jest zalogowany do bazy danych, ale to jest zazwyczaj zawsze jeden i ten sam user – niezależnie od tego na kogo się użytkownik loguje via www.

hmm. trzeba więc logować z poziomu aplikacji, a nie bazy.

no tak, ale jeśli user w jednej akcji powoduje modyfikacje wielu rekordów czy wielu tabel – sprawa zaczyna się komplikować.

na szczęście jest rozwiązanie.

wystarczy założyć dodatkową tabelkę do której będziemy dodawać nowy rekord przy rozpoczynaniu procedury obsługi każdego requestu, i będziemy tam wpisywać url'a i nazwę usera. a potem tylko baza musi umieć z tego skorzystać.

aby baza mogła powiązać dane z tej nowej tabelki (nazwijmy ją sobie “actions"), musi być w stanie jakoś powiązać późniejsze insert'y, update'y i delete'y z wpisami w actions. najlepiej to zrobić używając pidu (numer identyfikacyjny procesu) backendu postgresa.

w postgresie jest funkcja która zwraca ten pid:

SELECT pg_backend_pid();

dodajmy więc tabelkę actions, od razu z indeksem na to po czym będziemy szukać:

CREATE TABLE actions (
id BIGSERIAL,
backend_pid INT4 NOT NULL DEFAULT pg_backend_pid(),
action_on TIMESTAMP NOT NULL DEFAULT now(),
action_url TEXT,
action_user TEXT,
PRIMARY KEY (id)
);
CREATE INDEX ui_actions_bpao ON actions (backend_pid, action_on);

tabelkę history_objects modyfikujemy tak aby zawierała pola na urla i username (zamiast tego można dodać action_id, ale dodanie całych pól daje nam dodatkowe możliwości):

CREATE TABLE history_objects (
id BIGSERIAL,
modified_on TIMESTAMP NOT NULL DEFAULT now(),
modification_type TEXT NOT NULL DEFAULT '',
modification_url TEXT,
modification_user TEXT,
modified_id INT8,
modified_o_type TEXT,
PRIMARY KEY (id)
);

pozostało zmodyfikować triggery:

CREATE OR REPLACE FUNCTION trg_objects_ui() RETURNS TRIGGER AS
$BODY$
DECLARE
temprec actions%ROWTYPE;
BEGIN
SELECT * INTO temprec FROM actions WHERE backend_pid = pg_backend_pid() ORDER BY backend_pid DESC, action_on DESC LIMIT 1;
IF NOT FOUND THEN
temprec.action_url := 'unknown url. direct database access?';
temprec.action_user := 'database user: ' || CURRENT_USER;
END IF;
INSERT INTO history_objects (modified_id, modified_o_type, modification_type, modification_url, modification_user) VALUES (NEW.id, NEW.o_type, TG_OP, temprec.action_url, temprec.action_user);
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_objects_ui AFTER INSERT OR UPDATE ON objects FOR EACH ROW EXECUTE PROCEDURE trg_objects_ui();
 
CREATE OR REPLACE FUNCTION trg_objects_d() RETURNS TRIGGER AS
$BODY$
DECLARE
temprec actions%ROWTYPE;
BEGIN
SELECT * INTO temprec FROM actions WHERE backend_pid = pg_backend_pid() ORDER BY backend_pid DESC, action_on DESC LIMIT 1;
IF NOT FOUND THEN
temprec.action_url := 'unknown url. direct database access?';
temprec.action_user := 'database user: ' || CURRENT_USER;
END IF;
INSERT INTO history_objects (modified_id, modified_o_type, modification_type, modification_url, modification_user) VALUES (OLD.id, OLD.o_type, TG_OP, temprec.action_url, temprec.action_user);
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_objects_d AFTER DELETE ON objects FOR EACH ROW EXECUTE PROCEDURE trg_objects_d();

no to pora na test. najpierw zobaczymy jak to działa gdy nie zapiszemy nic do actions:

> SELECT * FROM objects;
id | o_type
----+--------
(0 ROWS)
 
> SELECT * FROM history_objects;
id | modified_on | modification_type | modification_url | modification_user | modified_id | modified_o_type
----+-------------+-------------------+------------------+-------------------+-------------+-----------------
(0 ROWS)
 
> SELECT * FROM actions;
id | backend_pid | action_on | action_url | action_user
----+-------------+-----------+------------+-------------
(0 ROWS)
 
> INSERT INTO objects (o_type) VALUES ('bleble');
INSERT 0 1
 
> INSERT INTO objects (o_type) VALUES ('bleble2');
INSERT 0 1
 
> UPDATE objects SET o_type = 'xxx' WHERE o_type = 'bleble';
UPDATE 1
 
> DELETE FROM objects WHERE o_type = 'bleble2';
DELETE 1
 
> SELECT * FROM objects;
id | o_type
----+--------
1 | xxx
(1 ROW)
 
> SELECT * FROM history_objects;
id |        modified_on         | modification_type |           modification_url           |   modification_user   | modified_id | modified_o_type
----+----------------------------+-------------------+--------------------------------------+-----------------------+-------------+-----------------
1 | 2006-07-15 12:58:54.16915  | INSERT            | UNKNOWN url. direct DATABASE access? | DATABASE USER: depesz |           1 | bleble
2 | 2006-07-15 12:58:58.46624  | INSERT            | UNKNOWN url. direct DATABASE access? | DATABASE USER: depesz |           2 | bleble2
3 | 2006-07-15 12:59:04.454578 | UPDATE            | UNKNOWN url. direct DATABASE access? | DATABASE USER: depesz |           1 | xxx
4 | 2006-07-15 12:59:09.519242 | DELETE            | UNKNOWN url. direct DATABASE access? | DATABASE USER: depesz |           2 | bleble2
(4 ROWS)

działa ładnie. to teraz zapiszmy coś do actions i powtórzmy test:

> INSERT INTO actions (action_url, action_user) VALUES ('/jakis_url.html', 'admin');
INSERT 0 1
 
> INSERT INTO objects (o_type) VALUES ('A:bleble');
INSERT 0 1
 
> INSERT INTO objects (o_type) VALUES ('A:bleble2');
INSERT 0 1
 
> UPDATE objects SET o_type = 'A:xxx' WHERE o_type = 'A:bleble';
UPDATE 1
 
> DELETE FROM objects WHERE o_type = 'A:bleble2';
DELETE 1
 
> SELECT * FROM objects;
id | o_type
----+--------
1 | xxx
3 | A:xxx
(2 ROWS)
 
> SELECT * FROM history_objects;
id |        modified_on         | modification_type |           modification_url           |   modification_user   | modified_id | modified_o_type
----+----------------------------+-------------------+--------------------------------------+-----------------------+-------------+-----------------
1 | 2006-07-15 12:58:54.16915  | INSERT            | UNKNOWN url. direct DATABASE access? | DATABASE USER: depesz |           1 | bleble
2 | 2006-07-15 12:58:58.46624  | INSERT            | UNKNOWN url. direct DATABASE access? | DATABASE USER: depesz |           2 | bleble2
3 | 2006-07-15 12:59:04.454578 | UPDATE            | UNKNOWN url. direct DATABASE access? | DATABASE USER: depesz |           1 | xxx
4 | 2006-07-15 12:59:09.519242 | DELETE            | UNKNOWN url. direct DATABASE access? | DATABASE USER: depesz |           2 | bleble2
5 | 2006-07-15 13:00:15.098039 | INSERT            | /jakis_url.html                      | admin                 |           3 | A:bleble
6 | 2006-07-15 13:00:18.315004 | INSERT            | /jakis_url.html                      | admin                 |           4 | A:bleble2
7 | 2006-07-15 13:00:21.304025 | UPDATE            | /jakis_url.html                      | admin                 |           3 | A:xxx
8 | 2006-07-15 13:00:24.483061 | DELETE            | /jakis_url.html                      | admin                 |           4 | A:bleble2
(8 ROWS)

działa. i to całkiem ładnie. oczywiście mechanizm można dalej rozszerzać. dodawać nowe pola (ip, browser) czy z części rezygnować.

zmienna lista “cech” – ciąg dalszy

jakiś czas temu pisałem jak poradzić sobie z przechowywaniem zmiennej listy cech obiektów w bazach relacyjnych.

okazuje się, że nie tylko ja zetknąłem się z tym problemem (szokujące, czyż nie?).

dwóch, znanych w świecie postgresowym ludzi – teodor sigaev i oleg bartunov – zajęli się tym problemem.

jeśli ich nie kojarzycie – to są ludzie którzy stworzyli i rozwinęli tsearcha. zajmują się  optymalizacjami indeksów typu gist, wymyślili własny typ indeksów (gin) i ogólnie siedzą w źródłach postgresa chyba 24 godziny na dobę. ich prace są częściowo sponsorowane przez rosyjską (aha, obaj są z rosji) agencję do wspierania badań (taki ichniejszy komitet badań naukowych).

problemem cech się zajęli, pogłówkowali i zrobili. nowy typ danych do postgresa – hstore.

co potrafi?

w jednym polu może przechowywać dowolnie wiele par klucz/wartość. wyszukiwać rekordów mających określone pola w sposób który korzysta z indeksów.

operować na tych danych i ogólnie – dać programiście wreszcie dobre rozwiązanie tego problemu.

jak się tego używa? trywialnie. nie chcę tutaj kopiować dokumentacji, odsyłam was do przykładów. warto się zainteresować, bo soft działa ładnie, skutecznie i szybko.

nowa wersja slony’ego!

jak nie wiesz co to slony, to polecam zajrzenie na strone projektu i poczytanie. w skrócie – silnik replikacyjny do postgresa, działający jako replikacja asynchroniczna master-multi_slave.

silnik co trzeba dodać działający stabilnie, wygodnie i szybko.

ostatnio pojawiła się nowa wersja. a właściwie zapowiedź nowej wersji – prerelease wersji 1.2.

w niej – sporo ulepszeń. tym razem nie są to ulepszenia które pomagają zestawić replikację (jak większośc wersji 1.1.x), ale modyfikacje tego jak sam core slony'ego działa. efekt ma być murowany – szybciej, skuteczniej, mniej zasobożernie, bardziej monitorowalnie :):

  • najważniejsze – spore zmiany w zarządzaniu pamięcią. slony teraz wykrywa kiedy ma do czynienia z dużymi rekordami i replikuje je w inny sposób dzięki czemu nie zżera tyle pamięci co poprzednie wersje
  • szybko zapychana tabela sl_log_1 została wreszcie “dopalona". slony będzie co jakiś czas przełączał się między sl_log_1 i sl_log_2, i jak upewni się, że nic z tej nieaktualnej nie jest już używane, to będzie ją czyścił poprzez TRUNCATE – dzięki czemu rozmiar tych tabel zostanie mocno ograniczony
  • poprawki tyczące się wykonywania poleceń modyfikujących bazę danych poprzez mechanizmy slony'ego – teraz kolejne polecenia będą wykonywane oddzielnie dzięki czemu zniknie problem z odwoływaniem się do dopiero-co utworzonych tabel i/lub pól.
  • poprawki tyczące się uruchamiania slony'ego jako serwisu w windowsach (nie używam więc nie jestem w stanie zweryfikować)
  • sporo poprawek tyczących się stabilności działania slonyego. teraz ma już być zbędne uruchamianie slonego przez watchdoga.

pełna lista zmian dostępna jest w tej chwili via cvsweb.

własne operatory upraszczające życie

na jednej z list postgresowych jakiś koleś zamarudził, że o ile warunek:

where pole!=3

działa poprawnie, to już

where pole!=-3 nie działa poprawnie.

wiele osób mu wyjaśniło, że:

  • standard sql nie oferuje operatora != w ogóle. do tego jest <>
  • != w postgresie to dodatek. takie “nice to have"
  • gdy doda się spację między “!=" a “-3" to działa ok (where pole!= -3)
  • całość działa tak dlatego, że system nie ma jak rozróżnić czy chodzi o “pole" “!=" “-3" czy może “pole" “!=-" “3", a to drugie jest w pełni legalną nazwę operatora – którego domyślnie nie ma, ale może być – w końcy postgres ma architekturę mocno otwartą i można sobie dodefiniować własne operatory czy cokolwiek.

facet oczywiście marudził dalej, że powinno działa.

postanowiłem pomóc. jak? dopisać operator !=- który zadziała tak jak facet chce.

najpierw potrzebna była funkcja. 30 sekund później miałem:

CREATE FUNCTION not_equals_minus(int8, int8) RETURNS bool AS $BODY$
SELECT $1 <> -$2;
$BODY$ LANGUAGE SQL IMMUTABLE STRICT;

proste i szybkie. działa dla liczb typu int8 (bigint), ale przerobienie do innych typów to trywiał.

to teraz definiojemy operator. jak? nie pamiętam. ale szybki search w manualu dał mi odpowiednią stronę, z jej pomocą napisałem:

CREATE OPERATOR !=- (
leftarg = int8,
rightarg = int8,
PROCEDURE = not_equals_minus,
commutator = !=-
);

efekt? działa. można teraz zrobić WHERE pole <> -3, WHERE pole != -3, lub WHERE pole!=-3. kocham postgresa 🙂

postgresql summit – nowości

10 lat temu, na małym webserwerze w kanadzie został wystawiony w internecie – po raz pierwszy – projekt postgresql.

aby uczcić tę rocznicę, a także by dać developerom możliwość spotkania się osobiście (i pewnie uroślinnienia :), została przygotowana mała, 2 dniowa, konferencja – postgresql summit.

są na niej przedstawiciele z praktycznie wszystkich liczących się firm około postgresowych: afilias, enterprisedb, greenplum, command prompt, sra, sun, pervasive i inni.

pierwszego dnia (wczoraj) było kilka interesujących wystąpień.

jednym z najważniejszych technologicznie (w/g mnie) był pokaz przygotowany przez korry'ego douglasa (autora niezłej książki o postgresie, aktualnie pracującego w enterprise db). korry pokazywał przygotowany przez enterprisedb mechanizm pozwalający na np. debugowanie funkcji plpgsql'owych.

całość podobno ma około 10 linii kodu, dzięki czemu wpływ tego kodu w momencie gdy nie korzystamy z niego jest minimalny.

a dodatkowo całość nie została zrobiona jako debugger, tylko zestaw hook'ów w backendzie postgresql'a. dzięki czemu w oparciu o ten sam mechanizm będzie można budować profilery, optymalizatory czy tracery.

wstępne szacunki mówią, że kod zostanie włączony do źródeł postgresa przed wydaniem 8.2, więc jest spora szansa, że w 8.2 będzie już debugger z prawdziwego zdarzenia (z breakpointami, wykonywaniem krokowym, inspekcją zmiennych itd.).

interesujące jest to, że kod został przygotowany w taki sposób by dało się do niego (jako interfejs) dopiąć cokolwiek – w sensie, nie jest to monolityczne rozwiązanie, interfejsy będzie można mieć różne. np. phpmyadmin, czy dowolny inny program tego typu.

pozostaje czekać kiedy 8.2 się ukaże 🙂

zmienna lista “cech”

przy wielu projektach pojawia się potrzeba przechowywania zmiennej listy “cech" jakichś obiektów.

weźmy na przykład sklep internetowy: mamy jakieś tam kategorie (struktura drzewiasta, moja ulubiona 🙂 ), w nich produkty. każdy produkt ma pewne cechy stałe – cena, tytuł, opis. natomiast produkty w określonych kategoriach mają swoje własne cechy dodatkowe.

np. dla samochodów możemy chcieć przechowywać:

  • ilość drzwi
  • pojemność silnika
  • typ paliwa
  • rodzaj skrzyni biegów

z drugiej strony, ogłoszenia w kategorii komputery będą miały pola takie jak:

  • ilość pamięci
  • wielkość dysku
  • typ procesora

najprostszym rozwiązaniem jest trzymanie produktów w każdej kategorii w oddzielnej tabelce – gdzie każda z tych tabelek ma różną strukturę (inna lista pól).

jest to rozwiązanie nieakceptowalne – osobiście uważam, że jakiekolwiek rozwiązanie zakładające modyfikacje struktury bazy danych w trakcie normalnego użytkowania jest błędne.

inną metodą jest zrobienie sobie tabelki typu:

 CREATE TABLE zmienne_cechy (
id         SERIAL PRIMARY KEY,
produkt_id INT  NOT NULL REFERENCES produkty(id),
cecha1     TEXT,
cecha2     TEXT,
cecha3     TEXT,
cecha4     TEXT,
...
);

i pilnowanie, że dla danego produktu kolumna cecha1 oznacza pojemność silnika, a dla innego jest to ilość pamięci.

takie rozwiązanie ma swoje zalety – najważniejszą jest to, że aby wyciągnąć informacje o wszystkich polach dla danego produktu wystarczy pobrać jeden rekord z bazy.

ale dopóki nie obsługujesz miliona page-views dziennie w swoim sklepie – ten problem jest mało istotny 🙂

zdecydowanie najskuteczniejszą metodą jest tabelka:

 CREATE TABLE zmienne_cechy (
id         SERIAL PRIMARY KEY,
produkt_id INT  NOT NULL REFERENCES produkty(id),
cecha      TEXT,
wartosc    TEXT
);

taka tabelka w jednej prostej strukturze pozwala na zapisanie wszystkich mozliwych cech i łatwe wyszukiwanie. no właśnie. czy na pewno łatwe?

tabelka pokazana taka jak tu – jest może i fajna, ale brakuje jej jeszcze jednej rzeczy:

create unique index ui_zmienne_cechy_pic on zmienne_cechy (produkt_id, cecha);

jeśli nie czytacie sql'i ze 100% zrozumieniem, to powyższe powoduje, że dany produkt może mieć tylko jedną wartość danej cechy. może mieć dowolnie wiele cech, ale żadna z cech nie może mieć wielu wartości.

zazwyczaj takie ograniczenie w niczym nie przeszkadza. zdarzają się czasem (ale bardzo rzadko) sytuacje, że istnieje potrzeba wielu wartości jednej cechy – sugeruję by wtedy nie kasować tego indeksu/klucza unikalnego tylko po prostu użyć ciut innych cech.

czemu?

otóż taka tabelka z pokazanym kluczem unikalnym pozwala nam w trywialny sposób zrobienie tego co bez klucza jest dużo trudniejsze (zasobochłonne): znalezienia produktów w/g kilku cech jednocześnie.

załóżmy, że chcemy znaleźć samochody o pojemności silnika 2000 z automatyczną skrzynią biegów.

bez klucza unikalnego jesteśmy skazani na coś takiego:

 SELECT zc1.produkt_id
FROM zmienne_cechy zc1 JOIN zmienne_cechy zc2 ON zc1.produkt_id = zc2.produkt_id
WHERE zc1.cecha='pojemnosc silnika' AND zc1.wartosc = '2000' AND zc2.cecha = 'skrzynia biegow' AND zc2.wartosc = 'automat';

nie jest to oczywiście takie złe. ale przy dużej ilości produktów stanie się problematyczne. nie mówiąc o tym jak będziemy chcieli sprawdzić produkty w/g np. 5 cech na raz. 5 joinów? jeśli w bazie jest np. 1000 produktów i każdy ma średnio 10 cech, to łączymy 5 razy ze sobą tabelę o 50000 rekordów. i szukamy na nich wszystkich. mało przyjemne.

dodanie wspomnianego wyżej klucza unikalnego pozwala na użycie w naszym select'cie rzadko używanej (i słabo znanej) klauzuli HAVING:

 SELECT produkt_id
FROM zmienne_cechy
WHERE
(cecha='pojemnosc silnika' AND wartosc='2000')
OR
(cecha='skrzynia biegow' AND wartosc = 'automat')
GROUP BY produkt_id
HAVING COUNT(*) = 2;

powstałe zapytanie ma kilka zalet:

  • jest trywialnie rozbudowywalne o kolejne cechy – bez konieczności dodatkowych joinów = wystarczy dodać dodatkowe warunki i podbić wartość w klauzuli having
  • jeśli używamy postgresql'a 8.1 i mamy dodatkowo indeks dwupoowy na (cecha, wartosc), to postgresql uzyje bardzo szybkich bitmap-or'ów

moje testy wykazały, że na postgresie 8.1 obie metody dają bardzo podobne wyniki (chodzi o czas zapytania) jeśli szukamy dwóch cech, natomiast już od 3 przewaga rozwiązania z having jest olbrzymia.