July 15th, 2006 by depesz | Tags: | 8 comments »
Did it help? If yes - maybe you can help me?

zapewne każdy kto choć raz robił coś ciut większego w bazach danych zetknął się z problemem przechowywania zmian w tabelkach.

ci co nie mają triggerów, muszą używać kodu po stronie aplikacji klienckiej. co ma swoje wady.

z drugiej strony – w postgresie triggery są. i działają 🙂 więc można do roboty zaprząc postgresa.

załóżmy, że mamy bardzo prostą tabelkę z danymi:

CREATE TABLE objects (
id BIGSERIAL,
o_type TEXT NOT NULL DEFAULT '',
PRIMARY KEY (id)
);
chcemy zapisywać sobie modyfikacje. robimy więc tabelkę na zapis historii zmian:
CREATE TABLE history_objects (
id BIGSERIAL,
modified_on TIMESTAMP NOT NULL DEFAULT now(),
modification_type TEXT NOT NULL DEFAULT '',
modified_id INT8,
modified_o_type TEXT,
PRIMARY KEY (id)
);

rekordy w history_object zawierają:

  • modified_on – kiedy dana modyfikacja miała miejsce
  • modification_type – typ modyfikacji – insert, update czy delete.
  • modified_id – id zmodyfikowanego rekordu
  • modified_o_type – o_type w tym rekordzie

proste i miłe. tworzymy więc dwa triggery:

CREATE OR REPLACE FUNCTION trg_objects_ui() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
INSERT INTO history_objects (modified_id, modified_o_type, modification_type) VALUES (NEW.id, NEW.o_type, TG_OP);
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_objects_ui AFTER INSERT OR UPDATE ON objects FOR EACH ROW EXECUTE PROCEDURE trg_objects_ui();
 
CREATE OR REPLACE FUNCTION trg_objects_d() RETURNS TRIGGER AS
$BODY$
DECLARE
BEGIN
INSERT INTO history_objects (modified_id, modified_o_type, modification_type) VALUES (OLD.id, OLD.o_type, TG_OP);
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_objects_d AFTER DELETE ON objects FOR EACH ROW EXECUTE PROCEDURE trg_objects_d();i testujemy:

testujemy:

> select * from objects;
id | o_type
----+--------
(0 rows)
 
> select * from history_objects;
id | modified_on | modification_type | modified_id | modified_o_type
----+-------------+-------------------+-------------+-----------------
(0 rows)
 
> INSERT INTO objects (o_type) VALUES ('bleble');
INSERT 0 1
 
> INSERT INTO objects (o_type) VALUES ('bleble2');
INSERT 0 1
 
> UPDATE objects SET o_type = 'xxx' WHERE o_type = 'bleble';
UPDATE 1
 
> DELETE FROM objects WHERE o_type = 'bleble2';
DELETE 1
 
> select * from objects;
id | o_type
----+--------
1 | xxx
(1 row)
 
> select * from history_objects;
id |        modified_on         | modification_type | modified_id | modified_o_type
----+----------------------------+-------------------+-------------+-----------------
1 | 2006-07-15 12:14:56.138695 | INSERT            |           1 | bleble
2 | 2006-07-15 12:14:56.138695 | INSERT            |           2 | bleble2
3 | 2006-07-15 12:14:56.138695 | UPDATE            |           1 | xxx
4 | 2006-07-15 12:14:56.138695 | DELETE            |           2 | bleble2
(4 rows)

działa ślicznie.

ale jest jedno ale.

czasem chcielibyśmy logować więcej informacji. w projekcie nad którym pracują koledzy w firmy chcieli logować nazwę akcji webowej (coś jakby url) i nazwę użytkownika które spowodowały taką modyfikację.

tu pojawiają się schody – baza danych nie zna urla. baza danych nie zna nazwy użytkownika który się zalogował na www – wie tylko jaki user jest zalogowany do bazy danych, ale to jest zazwyczaj zawsze jeden i ten sam user – niezależnie od tego na kogo się użytkownik loguje via www.

hmm. trzeba więc logować z poziomu aplikacji, a nie bazy.

no tak, ale jeśli user w jednej akcji powoduje modyfikacje wielu rekordów czy wielu tabel – sprawa zaczyna się komplikować.

na szczęście jest rozwiązanie.

wystarczy założyć dodatkową tabelkę do której będziemy dodawać nowy rekord przy rozpoczynaniu procedury obsługi każdego requestu, i będziemy tam wpisywać url'a i nazwę usera. a potem tylko baza musi umieć z tego skorzystać.

aby baza mogła powiązać dane z tej nowej tabelki (nazwijmy ją sobie “actions"), musi być w stanie jakoś powiązać późniejsze insert'y, update'y i delete'y z wpisami w actions. najlepiej to zrobić używając pidu (numer identyfikacyjny procesu) backendu postgresa.

w postgresie jest funkcja która zwraca ten pid:

select pg_backend_pid();

dodajmy więc tabelkę actions, od razu z indeksem na to po czym będziemy szukać:

CREATE TABLE actions (
id BIGSERIAL,
backend_pid INT4 NOT NULL DEFAULT pg_backend_pid(),
action_on TIMESTAMP NOT NULL DEFAULT now(),
action_url TEXT,
action_user TEXT,
PRIMARY KEY (id)
);
CREATE INDEX ui_actions_bpao ON actions (backend_pid, action_on);

tabelkę history_objects modyfikujemy tak aby zawierała pola na urla i username (zamiast tego można dodać action_id, ale dodanie całych pól daje nam dodatkowe możliwości):

CREATE TABLE history_objects (
id BIGSERIAL,
modified_on TIMESTAMP NOT NULL DEFAULT now(),
modification_type TEXT NOT NULL DEFAULT '',
modification_url TEXT,
modification_user TEXT,
modified_id INT8,
modified_o_type TEXT,
PRIMARY KEY (id)
);

pozostało zmodyfikować triggery:

CREATE OR REPLACE FUNCTION trg_objects_ui() RETURNS TRIGGER AS
$BODY$
DECLARE
temprec actions%ROWTYPE;
BEGIN
SELECT * INTO temprec FROM actions WHERE backend_pid = pg_backend_pid() ORDER BY backend_pid desc, action_on desc LIMIT 1;
IF NOT FOUND THEN
temprec.action_url := 'unknown url. direct database access?';
temprec.action_user := 'database user: ' || CURRENT_USER;
END IF;
INSERT INTO history_objects (modified_id, modified_o_type, modification_type, modification_url, modification_user) VALUES (NEW.id, NEW.o_type, TG_OP, temprec.action_url, temprec.action_user);
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_objects_ui AFTER INSERT OR UPDATE ON objects FOR EACH ROW EXECUTE PROCEDURE trg_objects_ui();
 
CREATE OR REPLACE FUNCTION trg_objects_d() RETURNS TRIGGER AS
$BODY$
DECLARE
temprec actions%ROWTYPE;
BEGIN
SELECT * INTO temprec FROM actions WHERE backend_pid = pg_backend_pid() ORDER BY backend_pid desc, action_on desc LIMIT 1;
IF NOT FOUND THEN
temprec.action_url := 'unknown url. direct database access?';
temprec.action_user := 'database user: ' || CURRENT_USER;
END IF;
INSERT INTO history_objects (modified_id, modified_o_type, modification_type, modification_url, modification_user) VALUES (OLD.id, OLD.o_type, TG_OP, temprec.action_url, temprec.action_user);
RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_objects_d AFTER DELETE ON objects FOR EACH ROW EXECUTE PROCEDURE trg_objects_d();

no to pora na test. najpierw zobaczymy jak to działa gdy nie zapiszemy nic do actions:

> select * from objects;
id | o_type
----+--------
(0 rows)
 
> select * from history_objects;
id | modified_on | modification_type | modification_url | modification_user | modified_id | modified_o_type
----+-------------+-------------------+------------------+-------------------+-------------+-----------------
(0 rows)
 
> select * from actions;
id | backend_pid | action_on | action_url | action_user
----+-------------+-----------+------------+-------------
(0 rows)
 
> INSERT INTO objects (o_type) VALUES ('bleble');
INSERT 0 1
 
> INSERT INTO objects (o_type) VALUES ('bleble2');
INSERT 0 1
 
> UPDATE objects SET o_type = 'xxx' WHERE o_type = 'bleble';
UPDATE 1
 
> DELETE FROM objects WHERE o_type = 'bleble2';
DELETE 1
 
> select * from objects;
id | o_type
----+--------
1 | xxx
(1 row)
 
> select * from history_objects;
id |        modified_on         | modification_type |           modification_url           |   modification_user   | modified_id | modified_o_type
----+----------------------------+-------------------+--------------------------------------+-----------------------+-------------+-----------------
1 | 2006-07-15 12:58:54.16915  | INSERT            | unknown url. direct database access? | database user: depesz |           1 | bleble
2 | 2006-07-15 12:58:58.46624  | INSERT            | unknown url. direct database access? | database user: depesz |           2 | bleble2
3 | 2006-07-15 12:59:04.454578 | UPDATE            | unknown url. direct database access? | database user: depesz |           1 | xxx
4 | 2006-07-15 12:59:09.519242 | DELETE            | unknown url. direct database access? | database user: depesz |           2 | bleble2
(4 rows)

działa ładnie. to teraz zapiszmy coś do actions i powtórzmy test:

> insert into actions (action_url, action_user) values ('/jakis_url.html', 'admin');
INSERT 0 1
 
> INSERT INTO objects (o_type) VALUES ('A:bleble');
INSERT 0 1
 
> INSERT INTO objects (o_type) VALUES ('A:bleble2');
INSERT 0 1
 
> UPDATE objects SET o_type = 'A:xxx' WHERE o_type = 'A:bleble';
UPDATE 1
 
> DELETE FROM objects WHERE o_type = 'A:bleble2';
DELETE 1
 
> select * from objects;
id | o_type
----+--------
1 | xxx
3 | A:xxx
(2 rows)
 
> select * from history_objects;
id |        modified_on         | modification_type |           modification_url           |   modification_user   | modified_id | modified_o_type
----+----------------------------+-------------------+--------------------------------------+-----------------------+-------------+-----------------
1 | 2006-07-15 12:58:54.16915  | INSERT            | unknown url. direct database access? | database user: depesz |           1 | bleble
2 | 2006-07-15 12:58:58.46624  | INSERT            | unknown url. direct database access? | database user: depesz |           2 | bleble2
3 | 2006-07-15 12:59:04.454578 | UPDATE            | unknown url. direct database access? | database user: depesz |           1 | xxx
4 | 2006-07-15 12:59:09.519242 | DELETE            | unknown url. direct database access? | database user: depesz |           2 | bleble2
5 | 2006-07-15 13:00:15.098039 | INSERT            | /jakis_url.html                      | admin                 |           3 | A:bleble
6 | 2006-07-15 13:00:18.315004 | INSERT            | /jakis_url.html                      | admin                 |           4 | A:bleble2
7 | 2006-07-15 13:00:21.304025 | UPDATE            | /jakis_url.html                      | admin                 |           3 | A:xxx
8 | 2006-07-15 13:00:24.483061 | DELETE            | /jakis_url.html                      | admin                 |           4 | A:bleble2
(8 rows)

działa. i to całkiem ładnie. oczywiście mechanizm można dalej rozszerzać. dodawać nowe pola (ip, browser) czy z części rezygnować.

  1. 8 comments

  2. Jul 17, 2006

    Uhm: backend_pid = pg_backend_pid() to dodaje dużo smaczku do Twojego rozwiązania. Ale czy na pewno jest bezpieczne? Co w przypadku, jeżeli aplikacja korzysta z warstwy pośredniej do bazy danych, np.: hibernate?

  3. Jul 20, 2006

    nie wiem. nie uzywam hibernate’a.
    trzeba by potestowac lub poszukac w dokumentacji od uzywanego object2relation mappera jak to wyglada.

    depesz

  4. # Madej
    Sep 5, 2006

    SELECT * INTO temprec FROM actions WHERE backend_pid = pg_backend_pid() ORDER BY backend_pid desc, action_on desc LIMIT 1;

    Dlaczego tu jest sortowanie po backend_pid? Chyba powinno być po id. 🙂

  5. Sep 5, 2006

    po action_on powinno byc i jest. sortowanie po backend_pid jest dodane bo dzięki temu można użyć jeden index on actions (backend_pid, action_on) dzieki czemu wynik tego zapytania to bedzie po prostu odczytanie ostatniego rekordu z indeksu (backward index scan) i juz. czytaj – szybkie.

  6. # Madej
    Sep 5, 2006

    Racja, chciałem uzuskac ten sam efekt, tylko jakoś umknęło mi co to jest action_on.

  7. # Cikus
    Nov 4, 2008

    chciałem zastosować powyższe rozwiązanie do swojego projektu. Z poziomu bazy danych wszystko działa. Natomiast z poziomu php po zalogowaniu i dodaniu do tabeli actions kto się zalogował, trigger nie wpisuje w tabele jednej kolumny, właśnie tej kto się zalogował czyli kto dokonał zmian w tabeli objects. Reszte wstawia typ zmiany , id mienionego rekordu, zmiane. Czy ktoś pomoże rozszyfrować tą różnice wstawienia action_user z poziomu bazy i z poziomu php ?

  8. Nov 4, 2008

    @Cikus:
    zaloguj wszystkie zapytania które idą do bazy i porównaj czym się różnią.

    jak chcesz mogę to zdiagnozować dla ciebie, jako normalne zlecenie, ale to temat nie na komentarze – jeśli jesteś zainteresowany – napisz maila. depesz@depesz.com.

  1. 1 Trackback(s)

  2. May 22, 2007: Michał Bagrowski » session_handler

Sorry, comments for this post are disabled.