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:
- 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.
- 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.
- 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:
- 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.
- 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;