polish disclaimer begin;

w celu trenowania języka, oraz by poszerzyć teoretyczny zasięg bloga będę teraz starał się pisać po anglijsku. wytykanie błędów mile widziane.

polish disclaimer commit;

ok, so you're trying to build something that needs random-text record identifiers. perhaps a new tinyurl-kind-of-service.

and you are thinking about a way to implement random text generation in a way that:

• there will be no direct information which record was added just after given one (knowing it's textual id). i mean – the text id's cannot be sequential like a, b, c, d, …
• the code should be as small as possible. we do not want to start with 40-characters behemoths just to make sure no-one can know which ones were before another.
• it should be as simple as possible.

requirements 1 and 2 are almost contrary, but we can manage.

first – let's assume we will generate random id's out of these characters: a-z, A-Z, 0-9. this gives us 62 different characters.

now. let's do it that way:

1. assume current_length to be 1
2. generate random string of length = current_length
3. if this id is already taken, increment current_length and repear from step 2
4. voila. new id generated.

it matches both first and second requirement from list.

as for simplicity.

let's try to implement:

first, let's create a test table with unique constraint on text-id field (i keep numerical id “just in case"):

CREATE TABLE test_table (
id          BIGSERIAL,
random_code TEXT  NOT NULL DEFAULT '',
something   TEXT ,
PRIMARY KEY (id)
);
CREATE UNIQUE INDEX ui_test_table_random_code ON test_table (random_code);

now, let's create function for random string generation:

CREATE OR REPLACE FUNCTION get_random_string(string_length INT4)
RETURNS TEXT
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
possible_chars TEXT = '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';
output TEXT = '';
i INT4;
pos INT4;
BEGIN
FOR i IN 1..string_length LOOP
pos := 1 + CAST( random() * ( LENGTH(possible_chars) - 1) AS INT4 );
output := output || substr(possible_chars, pos, 1);
END LOOP;
RETURN output;
END;
$BODY$;

code looks simply i guess. in case it doesn't – ask question in comments area.

now. the master-code, which is a trigger:

CREATE OR REPLACE FUNCTION trg_test_table_get_random_code()
RETURNS TRIGGER
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
string_length INT4 = 1;
temprec RECORD;
new_string TEXT;
BEGIN
LOOP
new_string := get_random_string(string_length);
SELECT COUNT(*) INTO temprec FROM test_table WHERE random_code = new_string;
IF temprec.count = 0 THEN
NEW.random_code := new_string;
EXIT;
END IF;
string_length := string_length + 1;
IF string_length >= 30 THEN
raise exception 'random string of length == 30 requested. something''s wrong.';
END IF;
END LOOP;
RETURN NEW;
END;
$BODY$;
CREATE TRIGGER trg_test_table_get_random_code BEFORE INSERT ON test_table FOR EACH ROW EXECUTE PROCEDURE trg_test_table_get_random_code();

basically this trigger is implementation of algorithm i wrote couple of lines above.

is it done? basically yes.

after 10 inserts i got this content of table:

# SELECT * FROM test_table;
id | random_code | something
----+-------------+-----------
1 | x           | x
2 | B           | x
3 | w           | x
4 | U           | x
5 | b           | x
6 | OE          | x
7 | N           | x
8 | zn          | x
9 | Y           | x
10 | JY          | x
(10 ROWS)

if you know your database-things you will see that the code has one serious (or not serious, depending on your view) problem.

if two inserts will happen at the same time, it is possible that one of them will raise exception of unique violation.

this is because there is a race condition between select count(*) and actual insert which takes place only after trigger finishes.

is there no hope? there is. but we have to modify the way we insert data to test table.

until now, i was able to simply: insert into test_table (something) values (‘x'); and it called my trigger code which set the random_code to whatever ‘s appropriate.

but if i want this to be a more fault-proof, i need to throw away the trigger, and force client to do inserts using select's.

like this:

CREATE OR REPLACE FUNCTION smart_insert(IN in_something TEXT, OUT new_key TEXT)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
string_length INT4 = 1;
BEGIN
LOOP
new_key := get_random_string(string_length);
BEGIN
INSERT INTO test_table(something, random_code) VALUES (in_something, new_key);
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing
END;
string_length := string_length + 1;
IF string_length >= 30 THEN
raise exception 'random string of length == 30 requested. something''s wrong.';
END IF;
END LOOP;
END;
$BODY$;

and now, i can:

SELECT smart_insert('x');

done.

or is it?

what if you'd like to be able to generate these text keys in more than one table? (for simplicity sake let's assume all of them have the same fields).

in such a case we would modify the function to be:

CREATE OR REPLACE FUNCTION smart_insert(IN TABLE_NAME TEXT, IN in_something TEXT, OUT new_key TEXT)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
string_length INT4 = 1;
use_sql TEXT;
BEGIN
LOOP
new_key := get_random_string(string_length);
BEGIN
use_sql := 'INSERT INTO ' || quote_ident( TABLE_NAME ) || ' (something, random_code) VALUES (' || quote_literal(in_something) || ', ' || quote_literal(new_key) || ')';
EXECUTE use_sql;
RETURN;
EXCEPTION WHEN unique_violation THEN
-- do nothing
END;
string_length := string_length + 1;
IF string_length >= 30 THEN
raise exception 'random string of length == 30 requested. something''s wrong.';
END IF;
END LOOP;
END;
$BODY$;

and then i could do:

SELECT smart_insert('test_table', 'xxx');

jak część z was wie byłem ostatnio na pgcon'ie.

tu od razu informacyjnie – byłem dzięki mojemu pracodawcy – firmie eo networks, któremu niniejszym publicznie bardzo dziękuję za umożliwienie mi wzięcia udziału w tej imprezie – jeśli szukacie pracy i znacie się na javie, bazach danych (głównie postgres, ale slyszałem też o jakichś projektach na innych bazach) – warto się odezwać.

wracając do meritum.

byłem tam na prezentacji olega bartunova nt. nowego tsearcha. nowego – nie znaczy, że będzie tsearch3. nowego – czyli tsearch2 zintegrowanego z samym postgresem.

jak się uda wszystko co zaplanowali to będzie tak w 8.3, ale jak się nie uda – no cóż. zobaczymy.

na prezentacji podpatrzyłem jedną rzecz którą wam tu teraz pokażę: wyszukiwanie pełnotekstowe z “poprawianiem" literówek (fuzzy-full text search).

Continue reading tsearch – instalacja, testy, rozszerzanie

weszlo do cvs'a, wiec już chyba mogę opisać.

rzecz jest prosta.

od jakiegoś czasu w psql'u można używać zmiennych. przykład:

# \SET a 123
# \SET b '''depesz'''
# SELECT :a, ':b';
?COLUMN? | ?COLUMN?
----------+----------
123 | depesz
(1 ROW)

zastosowanie jest oczywiste – prostsze pisanie sql'i z jakimiś zmiennymi.

minusy są dwa – utrudniona składnia do definiowania zmiennych tekstowych (potrójne apostrofy). i dwa – skoro i tak można tu ustawić tylko w skrypcie sql to funkcjonalność znikoma.

tak było do teraz.

ostatnio pojawił się patch na psql'a (i wszedł do cvs'a) dodający polecenie \prompt.

polecenie to pyta (zadanym tekstem) po czym odpowiedź użytkownika zapisuje do podanej zmiennej.

perfekcyjna sprawa gdy chcemy np. sparametryzować patch-bazodanowy.

przykład:

# \prompt 'Numeryczny identyfikator backupu? ' backup_id
Numeryczny identyfikator backupu? 123
# \echo :backup_id
123
# CREATE TABLE backup_:backup_id AS SELECT * FROM pg_proc;
SELECT
# SELECT COUNT(*) FROM backup_123;
COUNT
-------
2007
(1 ROW)

sweet 🙂

kolega pokazał mi pewną sytuację. miał taką tabelę:

                 TABLE "public.staty"
COLUMN  |              Type              | Modifiers
---------+--------------------------------+-----------
DATA    | timestamp(0) without time zone | NOT NULL
size    | integer                        | NOT NULL
proto   | integer                        | NOT NULL
macfrom | integer                        | NOT NULL
macto   | integer                        | NOT NULL
Indexes:
"blah123" PRIMARY KEY, btree (macto, DATA, proto, macfrom)
"dupa" btree (DATA)
"f1" btree (macfrom)
"f2" btree (macto)
"fs" btree (size)
CHECK constraints:
"proto check" CHECK (proto < 65536 AND proto > -1)
Foreign-KEY constraints:
"staty_fk" FOREIGN KEY (macto) REFERENCES macs(id)
"staty_fk1" FOREIGN KEY (macfrom) REFERENCES macs(id)

z 3 milionami rekordów. i na tym zapytanie:

SELECT a.i AS dupa, SUM(COALESCE(b.size, 0)) AS to_, SUM(COALESCE(f.size, 0)) AS FROM
FROM (SELECT
((current_timestamp-'1 month'::INTERVAL) + INTERVAL '1 second'*c.it) AS inter,
i
FROM(
SELECT i, i * ((EXTRACT(epoch FROM NOW())::INTEGER
-EXTRACT(epoch FROM (now()-'1 month'::INTERVAL))::INTEGER)/600)
AS it FROM generate_series(0,599) i
) AS c
) AS a
LEFT JOIN
(SELECT  DATA, SIZE FROM staty WHERE macto='$mac' ) b
ON (
b.DATA > (a.inter-(((EXTRACT(epoch FROM NOW())::INTEGER
-EXTRACT(epoch FROM (now()-'1 month'::INTERVAL))::INTEGER)/600)||' second')::INTERVAL )
AND
b.DATA < a.inter
)
LEFT JOIN
(SELECT  DATA, SIZE FROM staty WHERE macfrom='$mac' ) f
ON(
f.DATA > (a.inter-(((EXTRACT(epoch FROM NOW())::INTEGER
-EXTRACT(epoch FROM (now()-'1 month'::INTERVAL))::INTEGER)/600)||' second')::INTERVAL )
AND
f.DATA < a.inter
)
GROUP BY a.i ORDER BY a.i

iiiś. poza “urodą" zapytania powalał czas: 120-200 sekund!

no muszę przyznać, że miałem bardzo duży problem by zrozumieć.

pierwsza rzecz – stwierdziłem, że przepiszę to zapytanie do postaci czytelnej.

i tu pojawił sie problem – w kodzie wielokrotnie występują pewne stałe wyliczane – now() – ‘1 month' czy to samo dzielone na 600.

jak będę je powtarzał za każdym razem to czytelność szlag trafi. na szczęście przypomniało mi się, że takie rzeczy można łatwo obejść aliasami.

chwila pisania i mamy zapytanie:

SELECT
g.i,
SUM(COALESCE(f.size, 0)) AS from_,
SUM(COALESCE(t.size, 0)) AS to_
FROM
generate_series(0,599) g(i),
staty f,
staty t,
(
SELECT
now() - '1 month'::INTERVAL AS START,
'1 second'::INTERVAL * EXTRACT(epoch FROM (now() - (now() - '1 month'::INTERVAL)))/600 AS div
) x
WHERE
f.macfrom = '$mac'
AND t.macto = '$mac'
AND f.DATA BETWEEN (x.start + g.i * x.div) AND (x.start + (g.i + 1) * x.div)
AND t.DATA BETWEEN (x.start + g.i * x.div) AND (x.start + (g.i + 1) * x.div)
GROUP BY
g.i

(tu zwracam uwagę na subselect () x – to on robi za aliasy do stałych wyliczanych.

odtrąbiłem sukces. ale przedwcześnie. zapytanie wykonywało się w zasadniczo tym samym czasie = 128 – 160 sekund.

powiedziałem koledze by pozakładał indeksy wielopolowe:

CREATE INDEX some_name ON staty (macfrom, DATA);
CREATE INDEX some_other_name ON staty (macto, DATA);

te dwa indeksy i vacuum później (oj, dużo później) mamy efekt. zerowy. moje zapytanie nadal jest wolne. co ciekawe – oryginalne zapytanie nagle przyspieszyło do około 3.7 sekundy.

zastanowiłem się czemu. i nagle – olśnienie. zapytanie (w całości) operuje na dokładnie wszystkich danych z tabeli – tzn. ten miesiąc dla którego agregujemy dane to więcej niż danych jest w bazie – podobno koło tygodnia.

co oznacza, że musimy wyselectować całość. a że maszyna ma mało ramu, to szybciej sobie radzi z liczeniem popaczkowanym w małe części niż całości na raz.

tak więc kolejny rewrite:

SELECT
g.i,
(
SELECT SUM(SIZE) FROM staty WHERE macfrom = '$mac' AND DATA BETWEEN (x.start + g.i * x.div) AND (x.start + (g.i + 1) * x.div)
) AS from_,
(
SELECT SUM(SIZE) FROM staty WHERE macto = '$mac' AND DATA BETWEEN (x.start + g.i * x.div) AND (x.start + (g.i + 1) * x.div)
) AS to_
FROM
generate_series(0,599) g(i),
(
SELECT
now() - '1 month'::INTERVAL AS START,
'1 second'::INTERVAL * EXTRACT(epoch FROM (now() - (now() - '1 month'::INTERVAL)))/600 AS div
) x

i mamy czas – 170ms.

refleksje na przyszłość:

• joiny są szybkie, chyba, że operujemy na dużych zbiorach danych na maszynach z małą ilością ramu
• subselecty nie zawsze są wolne. zazwyczaj tak. ale w tym przypadku – gdzie tak naprawdę nic nie łączy danych z dwóch kopii tabeli staty – działają super
• subselect jako metoda aliasowania wartości – czad. upraszcza pisanie, trochę przyspiesza i ułatwia zrozumienie kodu 🙂

wykryto właśnie poważny błąd w postgresie w obsłudze funkcji typu “security definer". błąd tyczy się wszystkich wersji postgresa od kiedy to wprowadzono – czyli od 7.3.

błąd pozwala na wykonanie dowolnego kodu z podwyższonymi uprawnieniami i daje się wykorzystać podobno w przypadku większości funkcji zdefiniowany jako security definer (ale nie wszystkich).

na szybko kod można poprawić i zabezpieczyć ustawiając na sztywno w funkcji search_path'a.

prace nad poprawieniem tego trwają.

mysql ma fajną rzecz – drop table if exists.

pozwala to na pisanie bezpiecznych skryptów sql'owych które nawet przy obudowaniu w transakcję się prawidłowo wykonają.

przykład?

załóżmy, że chcemy stworzyć tabelę i cośtam jeszcze zrobić – wszystko w transakcji.

BEGIN;
CREATE TABLE test (x serial PRIMARY KEY, test_value TEXT);
...
COMMIT;

wygląda niewinnie.

no tak, ale co jeśli ta tabela już istnieje – bo np. ktoś ma zainstalowaną wcześniejszą wersję bazy? trywiał zmieniamy na:

BEGIN;
DROP TABLE test;
CREATE TABLE test (x serial PRIMARY KEY, test_value TEXT);
...
COMMIT;

i już działa.

ale nie. jeśli jednak odpalimy tego sql'a na bazie w której naszej tabeli nie ma – drop table sie nie uda, więc cała transakcja zostanie zrollbackowania.

no cóż, to może tak?

DROP TABLE test;
BEGIN;
CREATE TABLE test (x serial PRIMARY KEY, test_value TEXT);
...
COMMIT;

teraz – drop jest poza transakcją, więc jak się wywali to nie problem.

hmm .. ale co jeśli tabela test jest, ale nasze zapytania po create table się wywalą? tabela test zostanie permanentnie skasowana, a polecenie zakładające zostanie zrollbackowane. tragedia.

czy nie da się tego sensownie zrobić? da się.

trzeba użyć funkcji execute() o której pisałem wczoraj.

dzięki niej, mogę zrobić swojego sql'a tak:

BEGIN;
SELECT EXECUTE('DROP TABLE test') WHERE EXISTS (SELECT * FROM information_schema.tables WHERE table_schema = 'public' AND TABLE_NAME = 'test');
CREATE TABLE test (x serial PRIMARY KEY, test_value TEXT);
COMMIT;

i uzyskuję w ten sposób funkcjonalny odpowiednik DROP TABLE … IF EXISTS;

oczywiście składnia jest trudniejsza, ale nie wydaje się to być dużym problemem – jeśli jest – no cóż. zawsze można napisać własną funkcję która zrobi ‘drop table' tylko jeśli tabela istnieje. ale wykorzystanie ogólnego mechanizmu ‘execute' wydaje się być zdecydowanie potężniejsze

co jakiś czas pojawia mi się potrzeba masowego nadania praw. np. – mam nowego człowieka i muszę mu dać prawa do odczytu wszystkich tabelek w bazie. niestety postgresql nie obsługuje składni typu ‘GRANT … ON *'.

cóż więc pozostaje. pl/pgsql 🙂

zacznijmy od podstaw. funkcja która daje uprawnienia do czytania wskazanych tabel danemu użytkownikowi:

CREATE OR REPLACE FUNCTION grant_select_to(in_username TEXT, in_table_regexp TEXT) RETURNS void AS $BODY$
DECLARE
temprec RECORD;
use_sql TEXT;
BEGIN
FOR temprec IN SELECT c.relname FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE c.relkind = 'r' AND c.relname ~* in_table_regexp AND n.nspname = 'public' LOOP
use_sql := 'GRANT SELECT ON TABLE ' || temprec.relname || ' TO ' || in_username;
raise notice 'sql to run: [%]', use_sql;
EXECUTE use_sql;
END LOOP;
RETURN;
END;
$BODY$ LANGUAGE plpgsql;

i już. teraz można:

SELECT grant_select_to('depesz', '.');

co nada użytkownikowi depesz prawa do wszystkich (pasujących do regexpa ‘.', czyli wszystkich) tabel (relname = ‘r') w schemie public (inne schemy to zazwyczaj rzeczy systemowe).

do tego funkcja wypisze wszystkie wykonane sql'e.

no tak, ale co zrobić gdy chcemy dac inne prawa? albo kilku różnym użytkownikom różne zestawy praw? no i jeszcze dochodzą schemy – jak z nich korzystamy, to nie wystarczy dać prawa do tabel w schemach – trzeba dać też prawo ‘USAGE' do schemy.

można oczywiście napisać bardzo fajną funkcję która to wszystko zrobi. ale może zamiast tego, podejdziemy do sprawy sprytniej.

najpierw – to dzięki czemu mogę w plpgsql'u robić takie sztuczki to fakt, że jest tam funkcja execute. której w zwykłym sql'u nie ma. dodajmy ją więc:

CREATE OR REPLACE FUNCTION EXECUTE(in_sql TEXT) RETURNS void AS $BODY$
DECLARE
BEGIN
EXECUTE in_sql;
RETURN;
END;
$BODY$ LANGUAGE plpgsql;

co teraz?

zacznijmy od takiego sql'a:

SELECT * FROM information_schema.tables WHERE table_schema = 'public';

to nam wyselectuje jakieś tam dane nt. tabelek w schemie public.

a jak zmienię tego sql'a na:

SELECT 'grant select on table ' || TABLE_NAME || ' to depesz' FROM information_schema.tables WHERE table_schema = 'public';

wygląda interesująco.

więc kolejna drobna modyfikacja:

SELECT EXECUTE('grant select on table ' || TABLE_NAME || ' to depesz') FROM information_schema.tables WHERE table_schema = 'public';

efekt – działa. w dodatku – ponieważ zapytanie które wykonuję mogę dowolnie zmieniać, to nadawanie różnych praw staje sie dziecinnie proste. filtrowanie po nazwie tabeli – trywiał, wystarczy dodać “and table_name ~* ‘…'".

dodatkowo – funkcja execute() ma też inne zastosowania. ale o nich następnym razem.

znajomy z pracy (yo, tmarc) zapytał mnie jak zrobić pewien myk. chodzi o tabelę w której będzie trzymał dane, ale nie więcej niż x rekordów. tzn. on chce robić inserty, ale by baza sama dbała o to by najstarsze usunąć.

obiecałem nad tym usiąśc i oto wynik.

rozpatrzmy najpierw najprostszy przykład:

tabelka:

CREATE TABLE test (id serial, event_when timestamptz NOT NULL DEFAULT now(), event_type text NOT NULL);

załóżmy, że chcemy trzymać w niej tylko 5 rekordów. nigdy więcej.

gramy. robię triggera:

CREATE OR REPLACE FUNCTION trg_test_i() RETURNS TRIGGER AS
$BODY$
DECLARE
use_count INT4;
BEGIN
SELECT COUNT(*) INTO use_count FROM test;
IF use_count > 5 THEN
use_count := use_count - 5;
DELETE FROM test WHERE id IN (SELECT id FROM test ORDER BY id ASC LIMIT use_count);
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_test_i AFTER INSERT ON TEST FOR EACH ROW EXECUTE PROCEDURE trg_test_i();

wykorzystuję tu fakt iż kolejno wstawiane rekordy będą miały kolejne numery id. wszystko fajnie. kod działa.

ale ma jedną kolosalną wadę. przy każdym insert'cie wykonuje count(*). a to jest złe.

poprawmy to więc tak, aby nie było tych count'ów. kasujemy triggery (dane w tabelce mogą zostać) i:

najpierw – stworzę tabelę która ma cache'ować wynik count(*):

CREATE TABLE test_count (id serial PRIMARY KEY, TABLE_NAME TEXT NOT NULL UNIQUE, records INT4 NOT NULL DEFAULT 0);

potem – piszę jeszcze raz triggera – tym razem poza sprawdzeniem i ewentualnym skasowaniem – podbija on wartość countera:

CREATE OR REPLACE FUNCTION trg_test_i() RETURNS TRIGGER AS
$BODY$
DECLARE
use_count INT4;
BEGIN
UPDATE test_count SET records = records + 1 WHERE TABLE_NAME = 'test';
SELECT records INTO use_count FROM test_count WHERE TABLE_NAME = 'test';
IF use_count > 5 THEN
use_count := use_count - 5;
DELETE FROM test WHERE id IN (SELECT id FROM test ORDER BY id ASC LIMIT use_count);
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_test_i AFTER INSERT ON TEST FOR EACH ROW EXECUTE PROCEDURE trg_test_i();

skoro podbijamy counter przy insertach, to trzeba go obnizac przy delete'ach:

CREATE OR REPLACE FUNCTION trg_test_d() RETURNS TRIGGER AS
$BODY$
DECLARE
use_count INT4;
BEGIN
UPDATE test_count SET records = records - 1 WHERE TABLE_NAME = 'test';
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_test_d AFTER DELETE ON TEST FOR EACH ROW EXECUTE PROCEDURE trg_test_d();

i na koniec – wstawmy do tabeli z countami dane prawdziwe – aby nie liczył od zera gdy już są tam jakieś rekordy:

INSERT INTO test_count (TABLE_NAME, records) SELECT 'test', COUNT(*) FROM test;

jest zdecydowanie lepiej.

co prawda tabelę test_count trzeba często vacuumować, ale to jest do zrobienia: tabelka jest mała, więc można i co 5 minut puszczać na niej vacuum. w dodatku autovacuum powinien się nią ładnie zająć.

co jeszcze? a co by było gdybyśmy chcieli limitować ilość rekordów, ale nie w całej tabeli, a “per" event_type? czyli max. 5 ostatnich eventów każdego typu?

no cóż. kasujemy nasze triggery, tabelkę z countami (zostawiamy tabelkę z danymi) i lecimy.

najpierw – tabelka cache, musi trzymać też dane nt. tego który to event_type:

CREATE TABLE test_count (id serial PRIMARY KEY, TABLE_NAME TEXT NOT NULL, event_type TEXT NOT NULL, records INT4 NOT NULL DEFAULT 0, UNIQUE (TABLE_NAME, event_type));

teraz triggery. ponieważ musimy uwzględnić update'y (tak, musimy – nawet jeśli aplikacja *nigdy* nie wysyła update'ów, to przecież nie chcemy by nasza kontrola ilości rekordów została zwalona gdy ktoś zrobi update'a z konsoli psql):

CREATE OR REPLACE FUNCTION trg_test_i() RETURNS TRIGGER AS
$BODY$
DECLARE
tempint INT4;
BEGIN
UPDATE test_count SET records = records + 1 WHERE TABLE_NAME = 'test' AND event_type = NEW.event_type;
GET DIAGNOSTICS tempint = ROW_COUNT;
IF tempint = 0 THEN
INSERT INTO test_count (TABLE_NAME, event_type, records) VALUES ('test', NEW.event_type, 1);
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_test_i AFTER INSERT ON TEST FOR EACH ROW EXECUTE PROCEDURE trg_test_i();
CREATE OR REPLACE FUNCTION trg_test_d() RETURNS TRIGGER AS
$BODY$
DECLARE
use_count INT4;
BEGIN
UPDATE test_count SET records = records - 1 WHERE TABLE_NAME = 'test' AND event_type = OLD.event_type;
RETURN OLD;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_test_d AFTER DELETE ON TEST FOR EACH ROW EXECUTE PROCEDURE trg_test_d();
CREATE OR REPLACE FUNCTION trg_test_u() RETURNS TRIGGER AS
$BODY$
DECLARE
tempint INT4;
BEGIN
IF (NEW.event_type = OLD.event_type) THEN
RETURN NEW;
END IF;
UPDATE test_count SET records = records - 1 WHERE TABLE_NAME = 'test' AND event_type = OLD.event_type;
UPDATE test_count SET records = records + 1 WHERE TABLE_NAME = 'test' AND event_type = NEW.event_type;
GET DIAGNOSTICS tempint = ROW_COUNT;
IF tempint = 0 THEN
INSERT INTO test_count (TABLE_NAME, event_type, records) VALUES ('test', NEW.event_type, 1);
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_test_u AFTER UPDATE ON TEST FOR EACH ROW EXECUTE PROCEDURE trg_test_u();

zwracam tu uwagę na trzy rzeczy:

1. przy wstawianiu rekordów sprawdzam czy update się udał i jak nie – robię insert to tabeli z countami. to konieczne, aby się nie okazało, że countery nie działają, bo w test_count nie ma rekordów dla nich.
2. taki test nie do końca wystarcza. nie chciało mi się więcej pisać, ale taki test: update, get diagnostics, if() then insert – stwarza ryzyko race condition i aby napisać to w pełni poprawnie należałoby się uciec do obsługi wyjątków w pl/pgsql'u, ale ponieważ nie to jest celem tego wpisu – na razie to olewam.
3. ponieważ musimy wziąść pod uwagę update'y – do tych triggerów w ogóle nie wstawiłem kasowania starych rekordów. zrobię to w innym triggerze:

CREATE OR REPLACE FUNCTION trg_test_count_u() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
IF NEW.table_name = 'test' THEN
IF (NEW.records > 5) THEN
DELETE FROM test WHERE id = (SELECT id FROM test WHERE event_type = NEW.event_type ORDER BY id ASC LIMIT 1);
END IF;
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_test_count_u AFTER UPDATE ON test_count FOR EACH ROW EXECUTE PROCEDURE trg_test_count_u();

i już. trzeba zwrócić tu uwage na 2 rzeczy:

1. zrobiłem if'a na nazwę tabeli. wiem, że zamiast tego można użyć ‘EXECUTE', ale execute jest wolne. jeśli tabel które zliczamy jest mało – lepiej użyć takich if'ów.
2. zwracam uwagę na to, że trigger kasuje tylko 1 rekord. a mimo to wszystko działa poprawnie – tzn. jeśli przed założeniem triggera miałem 11 rekordów z tym samym event_type, to po wstawieniu kolejnego 7 najstarszych zostanie skasowane i zostanie tylko 5. wiecie czemu?

i to zasadniczo na tyle. jakieś pytania?INSERT INTO test_count (table_name, event_type, records) SELECT ‘test', event_type, count(*) FROM test group BY event_type;