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.

na razie tylko link do informacji. jutro postaram się zrobić pełen opis zmian z uwzględnieniem tego co w/g mnie najważniejsze.

najnowsza wersja postgresa z cvs'u ma wersję … 8.3devel!

w cvs'ie powstały branche REL8_2_0 i REL8_2_STABLE.

na webie jeszcze nie obwieścili nowiny – jak tylko to zrobią dam znać i opiszę co się zmieniło.

oczywiście jest też szansa, że do wypuszczenia jeszcze dużo czasu, a head ma wersję 8.3devel bo pojawiły się patche wykraczające poza 8.2 🙂

niesamowita prędkość, super sql. to wszystko nic nie znaczy gdy baza po prostu nie działa. a błędy zdarzają się przecież wszystkim.
co wtedy ma znaczenie? support.

wczoraj natknąłem się na krytyczny błąd w postgresie 8.2 rc1. i co dalej? timeline:

1. 11:38 – wysyłam na pgsql-general informacje o błędzie, core'a, log oraz konfigi
2. 12:16 – dostaję informację by cośtam jeszcze podesłać
3. 12:33 – podsyłam
4. 12:54 – prośba o binarkę postgresa
5. 14:16 – wysyłam ją (nie było mnie godzinę przy biurku)
6. 16:03 – włącza się tom lane podpowiadając jakiś szczegół
7. 16:06 – potwierdzenie, że błąd zreplikowany u developera
8. 17:25 – dostaję informację, że bug poprawiony, zmiany zacommitowane i dodatkowo podziękowania za zgłoszenie błędu

łącznie – niecałe 6 godzin! (w tym godzina spowolnienia przez moją nieobecność).

z tego co słyszę to reakcja na błędy w bazach komercyjnych jest liczona w dniach albo tygodniach. a nie godzinach.

aha. poprawka oczywiście działa.

serwis tweakers.net zajmuje się benchmarkowaniem różnych rzeczy – m.in. serwerów. im zawdzięczam swoją fascynację kontrolerami raid areca, im zawdzięczam kilka innych ciekawostek jakich się dowiedziałem.

ostatnio przeprowadzili benchmark nowego serwera – maszyny dell power edge 1950, z nowymi procesorami intela i kupą innych zabawek.

maszyna oczywiście wypadła super.

ale nie o to mi chodzi.

jednym z przeprowadzonych testów była wydajność i skalowalność ich własnego serwisu (kopii) postawionego na testowanej maszynie.

testy te przeprowadzali na mysql'u 4.1.20, 5.0.20a, oraz snapshotcie postgresa 8.2 prosto z cvs'u 🙂

wyniki?

tak wygląda wykres (przypominam, te wykresy porównują zachowanie systemu na jednej bazie na wielu różnych maszynach) od mysql'a 4.1:

a tak od mysql'a 5.0:

ok?

to teraz wykres dla postgresa:

na wypadek jeśli nie łapiecie o co mi chodzi – nie, nie chodzi mi wcale o to, że postgres robi więcej requestów na sekundę – to kwestia wtórna, optymalizowalna i łatwo fałszowalna.

to co mnie w mysql'u boli to to, że w pewnym momencie, wraz ze wzrostem ilości klientów prędkość obsługi dramatycznie spada. oczywiście najlepiej to widać na niagarze przy mysql 5.0, ale na każdym serwerze mysql ma tendencje spadkowe po osiągnięciu około 10 jednoczesnych zapytań. postgres natomiast nie.

oczywiście – tweakersi mogli zafałszować dane. true. ale ten schemat zachowania przewijał mi się wiele razy gdy czytałem o mysql'u. i chyba dlatego jakoś nigdy nie mogłem tej bazy polubić.

tak jak już d- mi zauważył pojawiła się druga część podpowiedzi nt. tunintowania aplikacji – napisana oczywiście przez josha berkusa.

w tej części podpowiedzi:

• delete jest kosztowny
• używanie prepare/execute dla wywoływania zapytań w pętli
• używanie connection-pool'i (buforów połączeń?)