kasowanie zbyt starych danych

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;

7 thoughts on “kasowanie zbyt starych danych”

  1. nie prosciej dodac pole z numerkiem, autowypelniac go przy insercie (na przyklad z sekwencji), w triggerze pobrac go z sekwencji, odjac ILESTAM, i usunac wsyzstko co starsze ?? zabawa w cachowanie count(*) smierdzi 🙂

  2. 1. nie zastosujesz tego do count’ów “per type” – za dużo sekwencji byś musiał mieć
    2. nawet przy całej tabeli – cały pomysł rozjedzie się przy “dziurach” w numeracji. a pemiętaj, że sekwencje nie gwarantują numeracji bez dziur – ogólnie – zagwarantowanie numerowania bez dziur jest skomplikowanym tematem – zdecydowanie bardziej niż to się wydaje na pierwszy rzut oka.

  4. jesli w tablicy ma byc max iles danych, to dziury nie martwia. Mysle ze barzdziej chodzilo czlowiekowi o to zeby tablica nie zajmowala bog wie ile na dysku 🙂
    a ostateczna ilosc wierszy zwracanych i tak ogranicza sie przy pomocy LIMIT 🙂
    dalo by sie dopisac do postgresa sekwencje bez dziur (przy pomocy use-counterow, ale to pchanie sie w problemy ala GC, bagno).
    Cala magia sekwencji polega na tym ze mozna je zastoswac w kilku tablicach, a jeden czy dwa numerki moga wypasc przy testowaniu, lub usuwaniu rekordow ktore je uzywaja.

  5. @gregi:
    dziury martwią jak liczysz ilość rekordów przez odjęcie min() od max()
    a to właśnie zasugerowałeś:
    pobrac z sekwencji, odjac ilestam i skasowac starsze.
    załóżmy, że pobvierasz z sekwencji w triggerze wartosc “15000”. a masz trzymać 500 rekordów. czy to oznacza, że aksujesz wszystkie <= 14500 ? a co jeśli w numeracji były dziury? dodatkowo - limit nie wpływa na to ile masz danych na dysku - własnie chodzi o zuzycie miejsca. reasumując: sekwencji używać się nie da do ograniczania ilości rekordów, a limit nie ogranicza ilości rekordów na dysku.

  6. Dlaczego coś nie w tym stylu?

    CREATE RULE test5limit AS
    DELETE FROM test WHERE id NOT IN
    (SELECT id FROM test ORDER BY id DESC LIMIT 5);

  7. @newfuntek:
    rozwiązanie fajne, ale nigdy nie lubiłem i nie polubię ruli. uważam, że są sprzeczne ze zdrowym rozsądkiem.

    dlatego ich nie używam i przy projektowaniu rozwiązań – nie zajmuję się nimi.

Comments are closed.