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;