standardowa instalacja postgresa zawiera 2 (lub 3) bazy:

• template0
• template1
• postgres (tylko w najnowszych wersjach)

template0 jest bezpieczna, o tyle, że nie da się do niej prosto podłączyć, więc nikt w niej nie namiesza.
baza template1 jest używana jako "podstawa" do tworzenia nowych baz. tzn. za każdym razem jak robisz: "create database xxx;" to tak naprawdę jest wykonywana kopia bazy template1.
daje to parę fajnych możliwości – np. zrobienie czegokolwiek w bazie template1 oznacza, że każda nowa baza będzie to miała automatycznie.
np. załadowane rozszerzenie, języki itd.
co jednak jeśli do template1 przez pomyłkę wrzuciliście jakieś zbędne dane? np. odtworzyliście do template1 zamiast do xxx jakąś bazę z dumpa?
ręczne kasowanie jest skomplikowane.
ideałem byłoby przywrócenie template1 do stanu początkowego.
da się to prosto zrobić.
w pierwszym kroku łączymy się psql'em do postgresa, na konto admina (zazwyczaj postgres, lub pgdba). ważne jest by połączyć się do bazy innej niż template1.
będąc tak połączonym wykonujemy 4 proste kroki:

1. update pg_database set datistemplate = false where datname = ‘template1';
2. drop DATABASE template1;
3. CREATE DATABASE template1 with template template0;
4. update pg_database set datistemplate = true where datname = ‘template1';

krok 1 jest niezbędny gdyż baza z wartością "datistemplate = true" nie może być skasowana. więc zaznaczamy, że template1 nie jest szablonem 🙂
krok 2 – kasujemy bazę template1. tu dwie ważne uwagi:

1. w czasie wykonywania drop database do dropowaniej bazy nie może być żadnych połączeń. właśnie dlatego musieliśmy się psql'em podpiąć do innej bazy.
2. między drop database template1, a create database template1 nie da sie stworzyć innych baz (a dokłaniej, nie jest to tak proste jak zazwyczaj)

krok 3 – odtwarzamy bazę template1, korzystając z szablonu template0
krok 4 – zaznaczamy template1 jako szablon.
i już to wszystko.

tu informacja dodatkowo. może to wykryliście z powyższego przykładu, ale jak nie, to piszę:
w podobny sposób można zrobić *szybką* kopię całej bazy.
np. jeśli potrzebujecie backup przed odpaleniem na bazie jakichś skomplikowanych rzeczy, można:

create database xxx_backup with template xxx;

oczywiście do tego przydałoby się też ‘encoding qqq owner yyy', ale to już szczegół.
olbrzymim plusem tej metody jest szybkość. przy czym nie szybkość tworzenia kopii. to czasem jest dłuższe niż pg_dump. to co jest istotne, to fakt iż "przywrócenie" bazy danych z takiej kopii, to proste:

drop database xxx;

alter database xxx_backup rename to xxx;

największym minusem jest fakt iż w czasie kopiowania do bazy źródłowej nie może być żadnych połączeń. czyli nie można tak skopiować bazy używanej produkcyjnie.
to spora wada. tym niemniej w środowiskach testowych/developerskich stosuję ją często z wyśmienitym skutkiem.

w nowej zelandii od pewnego czasu istniał centralny system zarządzania wyborami. pracował w oparciu o oracle'a i jakoś nie dawał sobie rady.
w 2003 roku rozpoczęto prace nad nowym systemem. i właśnie ostatnio został pomyślnie wdrożony. aktualny system pracuje w oparciu o postgresa 7.4, z replikacją (system jest rozproszony) opartą o rserva. firma która się tym zajmuje przymierza się do migracji na 8.1 i slony'ego, plus kilka zmian wewnątrz bazodanowych (jak np. wprowadzenie partycjonowania).
baza zajmuje teraz około 50giga, i trzyma informacje o około 2.8 miliona osób i ich głosów.

heh. tytuł pewnie nic nie mówi ludziom nie siedzącym w bazach danych, ale to mało istotne – post jest dla bazodanowców.
trafiłem na bardzo fajny tekst mówiący o tym jakie są typy "materialized views", oraz jak je zrobić w postgresie. tekst nie jest nowy, ale jest to przyjemny zbiór informacji który nie zestarzał się za bardzo. polecam jeśli chcecie w prosty sposób szybko wyciągać skomplikowane dane 🙂

3 dni temu (zajęty byłem, nie mogłem od razu zblogować) core-team wypuścił kilka nowych wersji postgresql'a: 8.2.1, 8.1.6, 8.0.10, 7.4.15 i 7.3.17.
wersje te to typowe bugfixy – bez nowych funkcjonalności. tym niemniej – jeśli używasz postgresa – warto się zastanowić nad upgrade'em przynajnmniej w ramach swojej wersji głównej (7.3, 7.4, 8.0, 8.1 i 8.2) – poprawiono np. błędy związane z tym, że w pewnych określonych sytuacjach postgres mógł po prostu paść (backend, nie cała baza).

kto z was wie o istnieniu funkcji generate_series() w postgresie? a kto z niej kiedykolwiek (nie testowo!) skorzystał?
wydaje mi się, że jest to najbardziej niedoceniania funkcja istniejąca w tym systemie bazodanowym.
co daje? zobacz:

select * from generate_series(0, 100);

na pierwszy rzut oka nic rewolucyjnego? taki tam sobie liczniczek?
zobaczmy więc kilka przykładowych zastosowań.
pierwsze:
mamy tabelkę, z polem id (z seriala), i chcemy sprawdzić czy są dziury w numeracji. tradycyjna metoda to jakieś joiny czy skanowanie sekwencyjne ze zliczaniem dziur w aplikacji klienckiej.
a zobaczmy coś takiego:

create table x as select * from generate_series(1, 10000) as x (i);

delete from x where random() < 0.0010;

w ten sposób uzyskaliśmy tabelę x, z numerycznym polem i. a w numeracji tego pola są dziury.
jak je wylistować? tak:

select * from generate_series(1, 10000) except select i from x;

voila. działa. ślicznie. i szybko 🙂

to było proste i oczywiste.
teraz przykład trochę mniej oczywisty – przykład jest z mojej pracy, ale użyję trochę uproszczonej wersji struktur danych 🙂
stwórzmy sobie tabelkę z użytkownikami:

CREATE TABLE users (id serial PRIMARY KEY, username TEXT, registered date);

ok. teraz – wstawmy do niej 10 użytkowników, z losowymi unikalnymi username'ami, i różnymi, losowymi datami rejestracji:

insert into users (username, registered) select 'user: ' || x.i, '2000-01-01'::date + cast(random()*1000 as int4) from generate_series(1, 10) as x(i);

mamy teraz w users 10 użytkowników. daty rejestracji są losowane z zakresu ‘2000-01-01' do ‘2002-09-27';
teraz. to co potrzebowałem to musiałem wygenerować "faktury" dla użytkowników. w tym zaległe.
idea była taka, że w tabelce z fakturami:

CREATE TABLE invoices (id serial PRIMARY KEY, user_id INT4, paid_from date, paid_to date, status INT4);

są trzymane faktury użytkowników. każda faktura jest na 30 dni. musimy wstawić każdemu użytkownikowi tyle faktur by obejmowały cały okres od daty rejestracji użytkownika do chwili obecnej.
przykładowo:
jeśli mielibyśmy użytkownika który zarejestrował się 2006-12-01 (a dziś jest 2007-01-11) to musielibyśmy wygenerować dla niego 2 faktury:

1. od 2006-12-01 do 2006-12-30
2. od 2007-12-31 do 2007-01-29

dalszych nie, bo musimy zakończyć faktury na fakturze "aktualnej".
pierwsze co potrzebujemy to wiedzieć ile maksymalnie nam się może faktur wygenerować dla pojedynczego użytkownika. w naszym przypadku sprawdzamy to poprzez:

select ceil((now()::date - min(registered))/30.0) from users;

u mnie pokazało się 83.
ponieważ lubię proste liczby, zaokrąglę sobie to do 100. nie ma żadnego matematycznego uzasadnienia – po prostu tak wolę.
tak więc. to co nam się przyda to generate_series(1,100), a dokładniej generate_series(0,100).
pierwsze zapytanie które napiszemy zwróci nam dla danego użytkownika (na razie tylko jednego) listę 101 faktur (od 0 do 100):

SELECT u.registered + (x.i * 30), u.registered + (x.i*30) + 29 FROM users u, generate_series(0, 100) as x(i) WHERE u.id = 1;

zwracam uwagę na to, że nie mamy żadnego warunku na połączenie users i generate_series – nie jest to join, ale tzw. kartezjan.
jak widać – zapytanie zadziałało.
teraz musimy je ograniczyć, by zwróciło dane tylko do aktualnych:

SELECT

    u.registered + (x.i * 30), u.registered + (x.i*30) + 29

FROM

    users u, generate_series(0, 100) as x(i)

WHERE

    u.id = 1

    AND now()::date >= u.registered + (x.i * 30)

;

wow. działa.
to teraz. ponieważ mamy do czynienia z kartezjanem a nie joinem, to usunięcie "where u.id = 1" nie spowoduje uszkodzenia danych, tylko wypełnienie ich dla wszystkich userów.
dzięki czemu możemy zapisać finalne zapytanie w postaci:

INSERT INTO invoices (user_id, paid_from, paid_to, status)

SELECT

    u.id,

    u.registered + (x.i * 30),

    u.registered + (x.i*30) + 29,

    0

FROM

    users u, generate_series(0, 100) as x(i)

WHERE

    now()::date >= u.registered + (x.i * 30)

;

czy na pewno zadziałało dobrze?
zobaczmy:

SELECT

    u.id,

    u.registered,

    ( SELECT count(*) FROM invoices i WHERE i.user_id = u.id ) as invoice_count,

    i1.paid_from as invoice1_from,

    i1.paid_to as invoice1_to,

    i2.paid_from as invoice2_from,

    i2.paid_to as invoice2_to,

    now()::date as "now"

FROM

    users u,

    invoices i1,

    invoices i2

WHERE

    i1.id = (SELECT min(t.id) FROM invoices t WHERE t.user_id = u.id)

    AND

    i2.id = (SELECT max(t.id) FROM invoices t WHERE t.user_id = u.id)

;

u mnie wszystko zadziałało ok.
jak widać (mam nadzieję) – generate_series potrafi sporo. generowanie list liczb pozwala na robienie np. robienie zestawień, włącznie z wyświetlaniem sum danych nieistniejących, sprawdzanie braków, "zgadywanie" pewnych wartości.
pozostaje zachęcić was do testowania i bawienia się – to czym i jak się bawicie jest niejednokrotnie ważniejsze od finalnego efektu – efekt jest jednorazowy. zabawa owocuje wiedzą która zostaje i kiedyś sie przyda.
aha. ważna notatka – generate_series zostało dodane w 8.0. no ale przecież chyba nikt już nie używa baz starszych 🙂 (żartuję oczywiście)

napisałem ostatnio o kolejnych testach jakie robili kolesie z tweakers.net.
bluszcz wtedy skomentował, że chciałby zobaczyć testy postgresa z soliddb lub choćby falconem, a nie z kiepskawym innodb.
prosił i ma 🙂 blog mysql performance przeprowadził testy innodb, myisam i falcona. właśnie pod kątem skalowalności. efekt – ogólna, praktycznie całkowita porażka falcona.
przykłady wykres wyników jednego z testów wygląda tak:

przy czym uwaga: nie wybrałem takiego na którym falcon wyszedł najgorzej. są takie wykresy gdzie linia od wydajności falcona "leży" na osi.
do tego dochodzi jeszcze takie jedno zdanie:
"… Falcon cannot handle LIMIT properly …" (wyjęte z kontekstu, ale sens niezmieniony).
czekam teraz aż ktoś pokaże testy z soliddb. wtedy też na pewno ja zaprezentuję.

za tydzień, w sobotę, 13 stycznia adam buraczewski wygłosi wykład nt. "słonie pracują w stadzie".
tematyka obejmie wszystkie liczące się metody klastrowania postgresa: slony, pgpool, dblink, sequoia, postgres-r i inne.
wykład odbędzie się w gliwicach – jeśli będziecie w pobliżu – polecam. temat ciekawy, a adam ma kolosalną wiedzę. i umiejętność jej przekazania.

jest to zdecydowanie najbardziej rozpowszechniona metoda przechowywania drzew w bazach.
zakłada ona (zgodnie z naszymy podstawowymi założeniami), że każdy element ma tylko 1 element nadrzędny (lub go nie ma – gdy jest to element główny). dzięki temu całość można zapisać w jednej tabeli
:

$ CREATE TABLE categories (
    id        BIGSERIAL,
    parent_id INT8 REFERENCES categories (id),
    codename  TEXT NOT NULL DEFAULT '',
    PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ui_categories_picn ON categories (parent_id, codename);

nasze testowe drzewo w tej tabeli będzie wyglądało tak:

# SELECT * FROM categories;
 id | parent_id |  codename
----+-----------+------------
  1 |    [null] | sql
  2 |         1 | postgresql
  3 |         1 | oracle
  4 |         2 | linux
  5 |         3 | solaris
  6 |         3 | linux
  7 |         3 | windows
  8 |         6 | glibc1
  9 |         6 | glibc2
(9 rows)

jak widać – same podstawowe danych, jedna tabelka, mała nadmiarowość. wygląda ok. a jak to odpytać?

1. pobranie listy elementów głównych (top-levelowych)

> SELECT * FROM categories where parent_id is null;

2. pobranie elementu bezpośrednio “nad" podanym elementem
dane wejściowe:

• ID : id elementu

SELECT p.* FROM categories c JOIN categories p ON c.parent_id = p.id WHERE c.id = [ID];

jeśli zapytanie nic nie zwróci – znaczy to, że dany element był “top-levelowy".

3. pobranie listy elementów bezpośrednio “pod" podanym elementem
dane wejściowe:

• ID : id elementu

> SELECT * FROM categories WHERE parent_id = [ID];

4. pobranie listy wszystkich elementów “nad" danym elementem (wylosowanym)
dane wejściowe:

• ID : id elementu

tu pojawia się problem. można to rozwiązać selectem z dużą ilością joinów (tyle joinów ile poziomów kategorii jest powyżej naszego elementu), albo zastosować rozwiązanie algorytmiczne:

1. pobierz dane dla aktualnego elementu: SELECT * FROM categories WHERE id = [ID];
2. jeśli parent_id dla aktualnego elementu jest inny niż NULL wykonaj:
   1. SELECT * FROM categories WHERE id = [aktualny.parent_id]
   2. ustaw aktualny na właśnie pobrany

jest to rozwiązanie typu z wieloma zapytaniami, ale rozwiązanie z joinami ma wszystkie wady zapytań z “metody wielu tabel" (nota bene tam też można było zastosować podejście algorytmiczne, realizowane w aplikacji klienckiej)

5. pobranie listy wszystkich elementów “pod" danym elementem (wylosowanym)
dane wejściowe:

• ID : id elementu

niestety – i tym razem zapisanie tego w postaci pojedynczego zapytania będzie problematyczne. w tym przypadku należy zastosować rozwiązanie rekurencyjne:

1. pobierz listę wszystkich elementów bezpośrednio pod podanym elementem (vide punkt 3)
2. dla każdego z elementów powtórz powyższy punkt
3. powtarzaj tak długo aż dojdziesz do sytuacji gdy już nie ma elementów poniżej.

6. sprawdzenie czy dany element jest “liściem" (czy ma pod-elementy)
dane wejściowe:

• ID : id elementu

>SELECT count(*) from categories WHERE parent_id = [ID];

jeśli zwróci 0 – to dany element jest liściem.

7. pobranie głównego elementu w tej gałęzi drzewa w której znajduje się dany (wylosowany) element

• ID : id elementu

niestety – jedyną słuszną metodą jest użycie algorytmu z punktu 4 i pobranie ostatniego elementu (takiego dla którego algorytm się kończy, bo parent_id jest NULLem.

podstawową zaletą tego rozwiązania jest to, że stała struktura tabel jest w stanie przechowywać dowolną ilość danych w drzewie – niezależnie od ilości elementów czy poziomów zagłębień.
wadami jest głównie konieczność korzystania z pewnych rozwiązań algorytmicznych zamiast użycia zapytań bazodanowych.

część z was pewnie kiedyś zaprojektowała system gdzie był przechowywany numer nip.
numer nip jaki jest każdy wie – 10 cyfr, rozdzielonych myślnikami. zasadniczo – myślniki są nieważne. ale czasem ktoś (klient, urząd skarbowy, ktokolwiek) czepia się jak mu się np. nip zmieni z 522-186-96-44 na 522-18-69-644. niby ten sam. ale nie taki sam.
z tego powodu nip powinno się przechowywać w postaci takiej jak user podał.
ale – czy wyszukując nip mamy pewność, że wiemy w jakiej postaci "myślnikowej" dane są wpisane? a co jeśli mamy wpisane "522-186-96-44", a szukamy "522-18-69-644"?
czyli do wyszukiwania przydałoby się aby pamiętać bez myślników.
najprostszą wersją jest zrobienie dwóch kolumn: nip_original, nip_search. ale to jest brzydkie.
ładniej można zrobić to poprzez np. coś takiego:
mamy tabelkę:

create table test (id serial primary key, nip text not null, nazwa text not null);

i na niej zakładamy indeks w ten sposób:

create unique index test_idx on test (regexp_replace(nip, '[^0-9]', '', 'g'));

po czym sprawdzamy:

# explain analyze select * from test where regexp_replace(nip, '[^0-9]', '', 'g') = '1234567890';
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 Index Scan using test_idx on test  (cost=0.00..8.29 rows=1 width=54) (actual time=0.167..0.167 rows=0 loops=1)
   Index Cond: (regexp_replace(nip, '[^0-9]'::text, ''::text, 'g'::text) = '1234567890'::text)
 Total runtime: 0.261 ms
(3 rows)

super.
teraz .. dobrze by było jakby dało się wyszukiwać prefixowo – aby np. w aplikacji się "podpowiadało" samo – po wpisaniu kolejnych cyfr.
aby to zrobić musimy sięgnąć po tzw. index opclass (uwaga – to jest konieczne tylko jeśli wasze locale jest inne niż C – ale pewnie jest inne):

drop index test_idx;
create unique index test_idx on test (regexp_replace(nip, '[^0-9]', '', 'g') text_pattern_ops);

no i test:

# explain analyze select * from test where regexp_replace(nip, '[^0-9]', '', 'g') like '1234%';
                                                                                     QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=13.40..922.28 rows=500 width=54) (actual time=0.240..0.457 rows=7 loops=1)
   Filter: (regexp_replace(nip, '[^0-9]'::text, ''::text, 'g'::text) ~~ '1234%'::text)
   ->  Bitmap Index Scan on test_idx  (cost=0.00..13.27 rows=500 width=0) (actual time=0.162..0.162 rows=7 oops=1)
         Index Cond: ((regexp_replace(nip, '[^0-9]'::text, ''::text, 'g'::text) ~>=~ 1234'::text) AND (regexp_replace(nip, '[^0-9]'::text, ''::text, 'g'::text) ~<~ '1235'::text))
 Total runtime: 0.593 ms
(5 rows)

wow.
co prawda trzeba za każdym razem pisać tego regexp_replace'a.
czy na pewno trzeba? nie. wystarczy zrobić wrappera widokiem:

# create view test_view as select *, regexp_replace(nip, '[^0-9]', '', 'g') as search_nip from test;

i potem:

# explain analyze select * from test_view where search_nip like '123%';
                                                                                    QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test  (cost=13.40..923.53 rows=500 width=54) (actual time=0.375..7.384 rows=96 loops=1)
   Filter: (regexp_replace(nip, '[^0-9]'::text, ''::text, 'g'::text) ~~ '123%'::text)
   ->  Bitmap Index Scan on test_idx  (cost=0.00..13.27 rows=500 width=0) (actual time=0.199..0.199 rows=96 loops=1)
         Index Cond: ((regexp_replace(nip, '[^0-9]'::text, ''::text, 'g'::text) ~>=~ '123'::text) AND (regexp_replace(nip, '[^0-9]'::text, ''::text, 'g'::text) ~<~ '124'::text))
 Total runtime: 88.251 ms

super. działa. wyszukuje niezależnie od minusów, dane trzymamy tylko raz, mamy searcha prefixowego. czego chcieć więcej?