jak może wiecie w postgresie jest funkcja currval() zwracająca ostatnio nadane id z podanej sekwencji.

rozpatrzmy prosty przypadek:

# CREATE TABLE test (id serial PRIMARY KEY, pole int4);
CREATE TABLE
# INSERT INTO test (pole) SELECT * FROM generate_series(1, 10000);
INSERT 0 10000
# SELECT COUNT(*) FROM test;
 COUNT
-------
 10000
(1 ROW)
# SELECT currval('test_id_seq');
 currval
---------
   10000
(1 ROW)

wszystko wygląda ok. więc sprawdźmy jeszcze jeden insert mały:

# INSERT INTO test(pole) VALUES (12313);
INSERT 0 1
# SELECT currval('test_id_seq');
 currval
---------
   10001
(1 ROW)

nadal wszystko ok. i teraz:

# SELECT * FROM test WHERE id = currval('test_id_seq');
  id   | pole
-------+-------
 10001 | 12313
(1 ROW)
TIME: 93.358 ms

działa, ale coś wolno, sprawdźmy:

# EXPLAIN analyze SELECT * FROM test WHERE id = currval('test_id_seq');
                                            QUERY PLAN
--------------------------------------------------------------------------------------------------
 Seq Scan ON test  (cost=0.00..200.02 ROWS=1 width=8) (actual TIME=64.375..64.379 ROWS=1 loops=1)
   FILTER: (id = currval('test_id_seq'::regclass))
 Total runtime: 64.431 ms
(3 ROWS)

seq scan? sprawdźmy więc ręcznie podaną wartość:

# EXPLAIN analyze SELECT * FROM test WHERE id = 10001;
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 INDEX Scan USING test_pkey ON test  (cost=0.00..8.02 ROWS=1 width=8) (actual TIME=0.029..0.033 ROWS=1 loops=1)
   INDEX Cond: (id = 10001)
 Total runtime: 0.086 ms
(3 ROWS)

hmm .. tu jest dobrze. skąd seq scan? aby nie przynudzać z każdym zapytaniem, powiem tyle, że nie jest to kwestia błędnych typów czy czegoś tak oczywistego. problemem jest zmienność funkcji.

dokładniej: funkcja currval() jest zadeklarowana jako “volatile" – co oznacza, że jej wynik ma prawo zmienić się w czasie pojedynczego skanu tabeli. tak jak np. random(). to oznacza, że nie można użyć jej jako dostarczyciela wartości i potem tą wartością przeszukać indeksy.

cóż więc można zrobić – no cóż. trzeba powiedzieć postgresowi, że interesuje nas tylko pierwsza zwrócona wartość currvala – idealnie do tego nadają się podzapytania:

# EXPLAIN analyze SELECT * FROM test WHERE id = (SELECT currval('test_id_seq'));
                                                   QUERY PLAN
----------------------------------------------------------------------------------------------------------------
 INDEX Scan USING test_pkey ON test  (cost=0.01..8.03 ROWS=1 width=8) (actual TIME=0.047..0.050 ROWS=1 loops=1)
   INDEX Cond: (id = $0)
   InitPlan
     ->  RESULT  (cost=0.00..0.01 ROWS=1 width=0) (actual TIME=0.010..0.012 ROWS=1 loops=1)
 Total runtime: 0.187 ms
(5 ROWS)

jak widać jest już zdecydowanie lepiej.

dziś jest dzień postgresql'owy, więc kolejna informacja o nim.

jest taki program: sloccount. służy do liczenia ilości linii kodu, oraz estymowaniu kosztów stworzenia programu.

odpaliłem go na źródłach postgresa 8.2. oto wynik:

Totals grouped by language (dominant language first):
ansic:       479298 (94.01%)
yacc:         14698 (2.88%)
sh:            7805 (1.53%)
lex:           5349 (1.05%)
perl:          2608 (0.51%)
asm:             65 (0.01%)
python:          12 (0.00%)
<br/>
Total Physical Source Lines of Code (SLOC)                = 509,835
Development Effort Estimate, Person-Years (Person-Months) = 139.26 (1,671.14)
 (Basic COCOMO model, Person-Months = 2.4 * (KSLOC**1.05))
Schedule Estimate, Years (Months)                         = 3.50 (41.95)
 (Basic COCOMO model, Months = 2.5 * (person-months**0.38))
Estimated Average Number of Developers (Effort/Schedule)  = 39.84
Total Estimated Cost to Develop                           = $ 18,812,337
 (average salary = $56,286/year, overhead = 2.40).
SLOCCount, Copyright (C) 2001-2004 David A. Wheeler
SLOCCount is Open Source Software/Free Software, licensed under the GNU GPL.
SLOCCount comes with ABSOLUTELY NO WARRANTY, and you are welcome to
redistribute it under certain conditions as specified by the GNU GPL license;
see the documentation for details.
Please credit this data as "generated using David A. Wheeler's 'SLOCCount'."

(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 …" 🙂