archiwa playboya

magazyn playboy (wersja amerykanska, nie polska) obiecał, że zacznie wypuszczać swoje archiwa na dvd.

wydanie będzie zawierało okładki, pictoriale i stronę z żartami. całość ma być przeszukiwalna.

dane będą wydawane dekadami – pierwsze 2 dekady (1950-1959 i 1960 – 1969) mają wyjść w październiku i kosztować około $100 za sztukę (czyli za pojedynczą dekadę).

pudełko ma zawierać płytę dvd z materiałami, oraz 200 stronicową książkę z materiałami dodatkowymi.

yummy.

w szczególności – w pierwszej paczce będą zdjęcia z pierwszego numery playboya jaki został opublikowany – a w nim były zdjęcia marilyn monroe 🙂

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;

zagrożenie dla firmy

od dawna wiadomo, że największym zagrożeniem dla firmowych systemów informatycznych nie są hackerzy czy konkurencja, ale pracownicy. ci głupi i ci źli.

ostatnie badania tyczyły się tego którzy pracownicy sabotują firmowe systemy it. odpowiedź nie jest przesadnie szokująca – najczęściej są to informatycy (86%). dodatkowo – 90% osób robiących coś takiego ma wyższe od standardowych uprawnienia w sieci!

po czym poznać potencjalnego terrorystę firmowego? raport sugeruje:

  • gderliwi/narzekający
  • paranoiczni
  • spóźniający się często
  • kłócący się z kolegami
  • ogólnie kiepsko pracujący

hmm … mam paranoję. często narzekam. co do spóźniania – ciężko mi to ocenić – to samo z ogólnymi wynikami.

no ale kłócić się nigdy nie kłócę. jestem miły, uczynny i pomocny. 🙂 czyli to nie o mnie.

czyszczenie cache’a dyskowego

czasem przydałaby się możliwość wyczyszczenia cache'a dyskowego. jeśli potrzebujemy tego w kontekście jakiejś pobocznej partycji to wystarczy odmontować i zamontować.

ale co jeśli nie możemy tego zrobić? root file system. albo chociażby fs którego potrzebujemy.

neil conway wpadł na rozwiązanie, a ja je powtarzam za nim:

wystarczy:

echo 1 > /proc/sys/vm/drop_caches

przy czym ten plik istnieje dopiero od linuksa 2.6.16.

wykonanie polecania powoduje wyczyszczenie tych buforów które można (nie wszystkie można). efekt. na maszynie z uptime'em 12 minut, klasy desktop, zajętość buforów spadła z 512 na 140 megabajtów!.

po co to komu? najprościej – do benchmarków. słodkie.