(hurra, (dzięki (projektowi (pl/scheme))) (wreszcie) (będziemy (się (mogli))) (w postgresie) (cieszyć (z kodu (składającego się (z samych (nawiasów))))))

przeczytałem właśnie o interesującym module – orafce. moduł ten zawiera ponad 100 funkcji których celem jest emulowanie funkcji bazodanowych z oracle'a i plvision (taki pakiet do oracle'a).

funkcje w orafce są kompatybilne na poziomie api z wersjami z oracle'a co powoduje, że ich użycie mocno uprości ewentualną migrację między tymi platformami.

wśród funkcji zawartych w pakiecie są m.in.:

• kilka funkcji podstawowych – typu next_day, last_day
• funkcje kalendarza biznesowego – bazujące na pakiecie plvdate z plvision
• spory zestaw funkcji do pracy na stringach
• obsługa komunikacji między procesowej

zwrócić należy uwagę na fakt iż nie ma w tym pakiecie niczego czego nie dałoby się uzyskać w postgresie inaczej – jednakże sensem istnienia tego pakietu nie jest dodawanie funkcjonalności, a ułatwianie migracji.

miałem o tym napisać później, ale skoro merlin już opisał advisory locki, nie pozostaje mi nic innego niż
opisać je u siebie.

advisory locki są to zupełnie nowe locki nie powiązane z żadnymi fizycznymi obiektami w bazie. służą one do
przekazywania informacji między sesjami i transakcjami.

najbardziej szczególną cechą tych locków jest to, że działają poza mechanizmem transakcji. dzięki temu możliwe jest używanie
ich do celów do których standardowe locki sie nie nadawały.

przykładem użycia z mojego podwórka jest zakładanie locka na konkretne źródło rss'ów w dnewsach. do tej pory musiałem mieć
oddzielną tabelkę i w niej trzymać wpisy. do tego dochodziły kwestie czyszczenia wpisów gdyby program ściągający dane padł i nie
skasował swojego locka.

z drugiej strony – mogłem uzyć locków wewnątrz transakcji, ale to by oznaczało trzymanie długo trwających transakcji – a one z
definicji są złe (psują systemy replikacyjne).

advisory locki robią to co trzeba.

sesja (połącznie) które potrzebuje zakłada locka (locka zakłada sie na “liczbę" z zakresu int8, albo na parę liczb int4). lock
zostaje zdjęty gdy wywołam funkcję zdejmującą lub gdy padnie połączenie!

advisory locki są trzymane w pamięci dzięki czemu są baaaaaardzo szybkie – merlin przeprowadził prosty test zakładając 1000
advisory locków w około 6 milisekund!

dodatkowo – dzięki temu, że są trzymane w pamięci – odpadaję wszelkie bolączki mvcc – puchnące tabele, wolny odczyt,
vacuumowanie itd. advisory locki są błyskawiczne i całkowicie bezpieczne.

przykładowe użycie:

# select pg_advisory_lock(123);<br/>
 pg_advisory_lock<br/>
------------------<br/>
''<br/>
(1 row)<br/>
# select pg_advisory_unlock(123);<br/>
 pg_advisory_unlock<br/>
--------------------<br/>
 t<br/>
(1 row)

oczywiście advisory locki zapewniają wszystkie standardowe metody dostępu:

• pobranie locka blokujące
• pobranie nieblokujące
• locki exclusive
• locki shared

polecam przyjrzenie
się tej funkcjonalności – ma ona sporo niecałkiem oczywistych zastosowań.

dziś malutki wpis

w 8.2 w pliku konfiguracyjnym przy parametrach przy których jest pojęcie jednostki pojawiły się kody tych jednostek. np. shared_buffers, kiedyś podawane jako liczba bloków 8 kilobajtowych, teraz jest wyrażane po prostu jako 20000kB (na przykład).

znakomicie ułatwia to czytanie plików konfiguracyjnych – już nie trzeba się zastanawiać, czy dana wartość jest w bajtach, kilobajtach, blokach czy czymkolwiek innym.

lista parametrów objętych tą zmianą:

• authentication_timeout
• shared_buffers
• temp_buffers
• work_mem
• maintenance_work_mem
• max_stack_depth
• bgwriter_delay
• checkpoint_timeout
• checkpoint_warning
• effective_cache_size
• log_rotation_age
• log_rotation_size
• autovacuum_naptime
• deadlock_timeout

ta poprawka mnie osobiście ratuje życie, ale mam świadomość, że tyczy się relatywnie niewielkiej ilości osób.

tworzenie indeksu (create index, create unique index) jest operacją blokującą.

w czasie tworzenia wszelkie operacje insert/update/delete na tabeli na której jest robiony indeks są wstrzymane.

zazwyczaj nie jest to problem – create index trwa np. kilka sekund. ale pojawia się problem co z większymi bazami, np. serwisami aukcyjnymi. tabele są spore, ruch jest non stop. jeśli np. zakładałbym indeks na tabeli “aukcje" w której jest kilkadziesiąt milionów rekordów, to tworzenie indeksu może spokojnie potrwać kilkanaście minut.

a w tym czasie wszystkie insert/update/delete by były wstrzymane. co oznacza, że jak ktoś będzie chciał dodać nową aukcję, to będzie czekał, czekał, a po 3 minutach przeglądarka go rozłączy i wyświetli komunikat o time-out'cie. cienko. rzekłbym nawet, że fatalnie.

czy nic nie da się zrobić? wcześniej – nie dawało sie. ale teraz jest lekarstwo:

# CREATE INDEX CONCURRENTLY ble ON tabelka (pole);
# CREATE UNIQUE INDEX CONCURRENTLY ble_u ON tabelka (pole, inne_pole);

indeksy tworzone z klauzulą CONCURRENTLY nie blokują zapisów! czyli wszystko działa tip-top.

haczyki? zawsze są. najważniejszy jest taki, że tworzenie indeksów w ten sposób jest wolniejsze. ale to za bardzo nie przeszkadza.

z innych rzeczy:

• polecenie reindex nie porafi użyć “concurrently"
• jeśli create index concurrently się wywali – w systemie zostanie tworzony indeks zawieszony w dziwnym stanie – niby jest, ale system z niego nie korzysta – przed ponowieniem próby założenia tego indeksu trzeba go skasować.
• jeśli tworzymy indeks typu UNIQUE, to warunki unikalności są sprawdzane w trakcie tworzenia indeksu (dokładniej to w czasie drugiej fazy tworzenia indeksu). dzieje się tak mimo faktu iż nieistniejący jeszcze indeks nie może być użyty do wyszukiwania. jeśli w trakcie tworzenia indeksu polecenie create unique indeks wykryje powtarzające sie wartości – wywali się – i tu trzeba będzie skasować popsuty indeks (vide punkt wyżej)
• można robić nieblokująco zarówno indeksy zwykłe jak i wielopolowe, funkcyjne czy częściowe.
• można robić jednocześnie wiele indeksów w sposób nieblokujący, z tym, że nie więcej niż jeden na tabelę.
• zwykły create index może być użyty w transakcji, ale create index concurrently – już nie.
• tak samo jak przy zwykłych indeksach, tak samo przy tworzeniu nieblokującym nie są dopuszczone zmiany definicji tabeli na której tworzymy indeks

jak widać jest to funkcjonalność lekko (w/g mnie) jeszcze niewygładzona, ale zakładam, że rzeczy typu – pozostający “cień indeksu" po błędnym indeksowaniu – zostaną wyeliminowane dosyć szybko.

a poza tym – fenomenalna funkcjonalność. od zawsze mi jej brakowało.

to akurat poprawka malutka, ale jak dla mnie po prostu bogosławiona.

jak może wiecie polecenie update (a także delete) może aktualizować dane z pomocą innej tabeli. przykładowo:

# CREATE TABLE statusy (id serial PRIMARY KEY, kod TEXT);
NOTICE:  CREATE TABLE will create implicit sequence "statusy_id_seq" for serial column "statusy.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "statusy_pkey" for table "statusy"
CREATE TABLE
# INSERT INTO statusy (kod) VALUES ('new'), ('open'), ('resolved'), ('rejected');
INSERT 0 4
# CREATE TABLE zgloszenia (id serial, temat TEXT, status_id int NOT NULL references statusy (id));
NOTICE:  CREATE TABLE will create implicit sequence "zgloszenia_id_seq" for serial column "zgloszenia.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "zgloszenia_pkey" for table "zgloszenia"
CREATE TABLE
# INSERT INTO zgloszenia (temat, status_id) VALUES ('a', 1), ('b', 2), ('c', 3), ('d', 4);
INSERT 0 4
# ALTER TABLE zgloszenia add column status TEXT;
ALTER TABLE
# UPDATE zgloszenia SET status = s.kod FROM statusy s WHERE s.id = status_id;
UPDATE 4
# SELECT * FROM zgloszenia;

(4 rows)

fajne. ale – jeśli nazwy kolumn się powtarzają (np. ja mam w każdej tabeli pole “id" będące primary key'em) to trzeba postgresowi powiedzieć o które pole dokładnie mi chodzi. póki używam id z tabel dołączanych to nie problem – w “FROM" mogę podać alias na tabelę (w przykładzie powyżej: “s"). ale już tabeli którą aktualizuję – nie mogłem aliasować.

teraz już mogę. dzięki czemu nawet przy aktualizowaniu tabeli o długiej nazwie i używaniu w warunkach jej pól nie będę miał kosmicznie długiego sql'a – trudnego do czytania i poprawiania.

wygląda to na przykład tak:

UPDATE wynagrodzenia_pracownikow s SET pensje = sum(p.wynagrodzenie) FROM wynagrodzenia_pracownikow p WHERE p.przelozony_id = s.id;

ten sam mechanizm działa w przypadku delete'ów join-ujących tabele w celu stworzenia odpowiednich warunków.

post zaległy od wczoraj

w czasie projektowania i proponowania nowości do postgresa 8.2 padła idea by zacząć obsługiwać inserty wstawiające wiele rekordów. tak jak ma np. mysql:

INSERT INTO `tabele` (`pole1`, `pole2`) VALUES (1,1),(2,4),(2,7);

jak zawsze – panowie z core-teamu stwierdzili, że skoro robią już coś takiego, to może to trochę podkręcić.

wyszło z tego zupełnie nowe polecenie sql'owe: VALUES.

dzięki temu mogli zachować kompatybilność z standardowymi sql'ami, ale także wykorzystać to dla innych zastosowań.

zacznijmy od podstaw:

# CREATE TABLE x (id serial PRIMARY KEY, p_a TEXT, p_b TEXT);
NOTICE:  CREATE TABLE will create implicit sequence "x_id_seq" for serial column "x.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x_pkey" for table "x"
CREATE TABLE
# INSERT INTO x (p_a, p_b) VALUES ('standardowa', 'metoda');
INSERT 0 1
# INSERT INTO x (p_a, p_b) VALUES ('nowsza', 'metoda'), ('drugi', 'rekord'), ('następne', 'rekordy');
INSERT 0 3
# select * from x;

(4 rows)

fajne. tylko po co? no cóż. zrobiłem mały test. zrobiłem tabelkę:

# CREATE TABLE x (id serial PRIMARY KEY, liczba INT4);

i wstawiałem do niej dane (100000 kolejnych liczb) na kilka sposobów:

• copy x (liczba) from stdin;
• 100000 insertów – każdy po jednym rekordzie
• 100000 insertów – każdy po jednym rekordzie (ale z użyciem prepare i execute)
• 10000 insertów – każdy po dziesięć rekordów
• 10000 insertów – każdy po dziesięć rekordów (ale z użyciem prepare i execute)
• 5000 insertów – każdy po dwadzieścia rekordów
• 5000 insertów – każdy po dwadzieścia rekordów (ale z użyciem prepare i execute)

wyniki:

• copy: 0m1.045s
• insert (1): 1m5.473s
• insert + prepare (1): 1m3.483s
• insert (10): 0m8.500s
• insert + prepare (10): 0m7.552s
• insert (20): 0m4.065s
• insert + prepare (20): 0m3.656s

dla chętnych do powtórzenia testów/obejrzenia – skrypty oczywiście są dostępne.

graficznie wygląda to jeszcze bardziej interesująco:

jak widac przyspieszenie insertów jest znaczne. czy to wszystko? nie!

values można wykorzystać wszędzie tam gdzie potrzebujemy wielu rekordów. przykładowe zastosowanie – wyobraźmy sobie, że mamy pole typu int z zapisanymi statusami. statusów jest ledwie kilka. nie warto dla nich robić oddzielnej tabeli. ale jednak chcielibyśmy móc je w jakiś sposób odczytać z bazy … proszę:

# CREATE TABLE x (id serial PRIMARY KEY, liczba INT4);
NOTICE:  CREATE TABLE will create implicit sequence "x_id_seq" for serial column "x.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x_pkey" for table "x"
CREATE TABLE
# INSERT INTO x (liczba) VALUES (1), (2), (3), (2);
INSERT 0 4
# SELECT
x.id, x.liczba, k.kod
FROM x
join (VALUES (1, 'closed'), (2, 'new'), (3, 'open')) as k (id, kod) on x.liczba = k.id
;

(4 rows)

oczywiście powyższy efekt można uzyskać używając (CASE WHEN … END), ale to rozwiązanie jest krótsze i mocno czytelniejsze.

zwracam też uwagę na to, że w oparciu o VALUES można zdefiniować widok:

# CREATE VIEW statusy AS SELECT * FROM (VALUES (1, 'closed'), (2, 'new'), (3, 'open')) as k (id, kod);
CREATE VIEW
# select * from statusy;

(3 rows)

co jeszcze można z tym zrobić? sporo. pobawcie się sami i zastanówcie do czego można tego użyć.

dziś pierwszy odcinek o zmianach w postgresie 8.2. tematem odcinka są zapytania “… returning".

do zapytań modyfikujących dane – INSERT INTO, UPDATE, DELETE FROM zostało dodane rozszerzenie “RETURNING".

składnia wygląda następująco:

• INSERT INTO tabelka (pola) VALUES (costam) RETURNING wyrażenie;
• UPDATE tabelka SET pole = X WHERE cośtam RETURNING wyrażenie;
• DELETE FROM tabelka WHERE cośtam RETURNING wyrażenie;

przykłady użycia dla INSERT:

# CREATE TABLE x (id serial PRIMARY KEY, ble INT4);
NOTICE:  CREATE TABLE will create implicit sequence "x_id_seq" for serial column "x.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "x_pkey" for table "x"
CREATE TABLE
# insert into x (ble) values ('123') returning id;
id
----
1
(1 row)
INSERT 0 1
# insert into x (ble) values ('123') returning id, ble + 123;
id | ?column?
----+----------
2 |      246
(1 row)
INSERT 0 1
# insert into x (ble) values ('123') returning *;
id | ble
----+-----
3 | 123
(1 row)
INSERT 0 1
# insert into x (ble) values ('123') returning *, now(), (select count(*) from pg_class);
id | ble |              now              | ?column?
----+-----+-------------------------------+----------
4 | 123 | 2006-12-07 22:52:24.573777+01 |      209
(1 row)
INSERT 0 1
# select * from x;
id | ble
----+-----
1 | 123
2 | 123
3 | 123
4 | 123
(4 rows)
# insert into x (ble) select ble + 2 from x returning *;
id | ble
----+-----
5 | 125
6 | 125
7 | 125
8 | 125
(4 rows)
INSERT 0 4

miłe, czyż nie? głównym celem jest możliwość automatycznego pobierania wartości typu id – nadawanych z sekwencji. ale zadziała to także zwracając dane modyfikowane przez triggery.

to co jest po “RETURNING" musi być zgodne formatowo z tym co jest w standardowym select'cie między SELECT a FROM.

przykłady użycia dla UPDATE:

# update x set ble = ble * 2 where id % 2 = 0 returning *;
id | ble
----+-----
2 | 246
4 | 246
6 | 250
8 | 250
(4 rows)
UPDATE 4

zwracam uwagę na to, że returning zwraca nowe wartości! czyli tak po triggerowemu mówiąc: retyrning * oznacza return NEW; a nie return OLD;!

przykłady użycia dla DELETE:

# delete from x where id % 2 = 1 returning *;
id | ble
----+-----
1 | 123
3 | 123
5 | 125
7 | 125
(4 rows)
DELETE 4

jak widać składnia wszędzie jest podobna, miła łatwa i prosta. a do tego niesie sporo ułatwień. mnie osobiście cieszy INSERT … returning id; bo załatwia mi w piękny sposób odpowiadanie ludziom pytającym o id, i reagującym nerwowo na sugestie: select currval() : “ale czy jak dwa połączenia wstawią rekordy …"

eh,

gdzie nie spojrzę informacje o postgresie 8.2.

i informacje o tym co nowego.

stwierdziłem, że zrobię inaczej – zamiast suchych “dodano to i to" zrobię serię wpisów gdzie będę omawiał nowe feature'y wraz z przykładami użycia oraz opisem technologii.

pierwszy odcinek – dziś wieczorem lub jutro.

na potrzeby jednego z projektów miałem zaprojektować bazę. jednym z elementów bazy była tabelka z danymi użytkowników. ponieważ zbiór danych był spory i do tego zmienny, tabelka z użytkownikami została mocno uproszczona, ale za to zostały dodane tabelki na dane dodatkowe.

finalnie – tabelka z użytkownikami wyglądała mniej więcej tak:

CREATE TABLE users (
id         BIGSERIAL  ,
username   TEXT        NOT NULL DEFAULT '',
password   TEXT       ,
registered TIMESTAMPTZ NOT NULL DEFAULT now(),
active     BOOL        NOT NULL DEFAULT 'true',
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ui_users_username ON users (username);

małe, proste i łatwe. było. ale potem pojawiły się zmiany.

zmiana numer 1: klient którego konto zostanie wyłączone może założyć nowe konto o identycznej nazwie.

oops. username przestanie być unikalny? a może robić tabelę na dane archiwalne/usunięte? niestety – danych jako takich nie mogę kasować – muszą zostać w bazie.

krótki research pokazał, że przenoszenie danych do innych tabel (archiwum) nie wchodzi w grę – musiałbym zrobić kopię całej bazy jako archiwum. za dużo roboty. może coś prostszego?

pokombinowałem, przypomniałem sobie rozmaite rzeczy i zrobiłem:

DROP INDEX ui_users_username;
CREATE UNIQUE INDEX ui_users_username ON users (username) WHERE active = true;

co to robi? to proste – nadal mam indeks unikalny, ale tylko aktywnych kont. konta nieaktywne nie są indeksowane w ogóle, więc także nie są objęte limitem unikalności. całość działa ładne. do czasu.

zmiana numer 2: nazwy kont (ich unikalność) powinny nie pozwalać na dwa konta – typu “depesz" i “Depesz".

no, to to trywiał – mały trigger “BEFORE INSERT OR UPDATE" który mi username lowercase'uje i po sprawie. a jednak nie. nazwa użytkownika ma się wyświetlać tak jak on sobie zażyczył. jak sobie przy rejestracji wpisał “Depesz" to ma mu się tak wyświetlać. ale nie powinnismy dopuścić do rejestracji “depesz"‘a. oraz powinniśmy umożliwić mu zalogowanie sie zarówno jako “Depesz" jak i “depesz".

oops część 2. kombinuję. krok numer 1 – dodatkowe pole które trzyma nazwę konta w postaci “tak jak user podał", a username będę triggerował do lowercase'a. ale to jest brzydkie. i duplikuje mi dane. myślałem nad tym jakiś czas gdy nagle mnie olśniło: indeksy funkcyjne. wystarczy:

DROP INDEX ui_users_username;
CREATE UNIQUE INDEX ui_users_username ON users ( lower(username) ) WHERE active = true;

i po sprawie:

# insert into users (username) values ('depesz');
INSERT 0 1
 
# insert into users (username) values ('Hubert Lubaczewski');
INSERT 0 1
 
# insert into users (username) values ('hubert lubaczewski');
ERROR:  duplicate key violates unique constraint "ui_users_username"
 
# select * from users;
id |      username      | password |          registered           | active
----+--------------------+----------+-------------------------------+--------
1 | depesz             | [null]   | 2006-12-05 23:16:52.52406+01  | t
2 | Hubert Lubaczewski | [null]   | 2006-12-05 23:16:58.370124+01 | t
(2 rows)

podsumowując – mam tabelkę która trzyma bazowe informacje o użytkownikach, pilnując tego by mógł być tylko 1 aktywny o tej samej nazwie – gdzie “ta sama" jest sprawdzanie niezależnie od wielkości liter. i do tego nie zmieniamy danych wpisanych przez usera – i jeśli zażyczy sobie (rejestrując się) jakichś różnych wielkości liter – tak też to zrobimy i tak mu wyświetlimy. życie jest piękne.