СУБД. Лекция 4

Транзакции, триггеры и процедуры

• Транзакции:
  • Обеспечение долговечности. Журнал транзакций;
  • Обеспечение изолированности и атомарности. MVCC;
  • Уровни изолированности транзакций.
• Хранимые процедуры и функции
• Триггеры:
  • Использование триггеров для поддержания целостности и бизнес-логики;
  • Недостатки триггеров.
• View

Организационные моменты

• Не забывайте отмечаться на лекциях!
• Оставляйте обратную связь по завершению лекции!

Транзакция

Это множество операций, которые переводят базу данных из одного корректного состояния в другое корректное состояние (согласованность) при условии, что транзакция выполнена полностью (атомарность) и без помех со стороны других транзакций (изоляция).

BEGIN;

UPDATE accounts SET balance = balance - 100.00
    WHERE name = 'Alice';

UPDATE accounts SET balance = balance + 100.00
    WHERE name = 'Bob';

COMMIT; -- фиксация, если все хорошо
ROLLBACK; -- откат, если что-то пошло не так 

ACID

ACID

ACID описывает требования к транзакционной системе, обеспечивающие наиболее надёжную и предсказуемую её работу. Требования ACID были в основном сформулированы в конце 70-х годов Джимом Греем.

• Atomicity — Атомарность
• Consistency — Согласованность
• Isolation — Изолированность
• Durability — Долговечность

Atomicity — Атомарность

Атомарность гарантирует, что никакая транзакция не будет зафиксирована в системе частично. Будут либо выполнены все её подоперации, либо не выполнено ни одной.

Consistency — Согласованность

Транзакция, достигающая своего нормального завершения и, фиксирующая свои результаты, сохраняет согласованность базы данных.

Другими словами, каждая успешная транзакция по определению фиксирует только допустимые результаты.

Isolation — Изолированность

Во время выполнения транзакции параллельные транзакции не должны оказывать влияние на её результат.

Durability — Долговечность

Независимо от проблем на нижних уровнях (к примеру, обесточивание системы или сбои в оборудовании) изменения, сделанные успешно завершённой транзакцией, должны остаться сохранёнными после возвращения системы в работу.

Журнал транзакций

В простейшем случае журнализация изменений заключается в последовательной записи всех изменений, выполняемых в базе данных.

Записывается следующая информация:

• порядковый номер, тип и время изменения;
• идентификатор транзакции;
• объект, подвергшийся изменению (номер хранимого файла и номер блока данных в нём, номер строки внутри блока);
• предыдущее состояние объекта и новое состояние объекта.

Журнал содержит отметки начала и завершения транзакции, и отметки принятия контрольной точки.

Журнал транзакций

Общий алгоритм:

• изменения пишутся в журнал транзакций (состояние до и после) и изменяются страницы БД в памяти;
• в журнал транзакций сбрасывается маркер успешного завершения транзакции;
• журнал транзакций сбрасывается на диск;
• данные страниц БД изменяются;
• данные страниц БД сбрасываются на диск.

Приблизительные значения IOPS

Групповой коммит

Журнал транзакций. Бонус

На базе журнала транзакций так же реализуется ряд дополнительных возможностей:

• Point in time recovery;
• Репликация.

Изолированность. MVCC

MVCC

MultiVersion Concurrency Control

• Разные пользователи могут одновременно работать с одними и теми же данными;
• Каждый пользователь видит свой изолированный срез данных;
• Изменения, вносимые пользователем, никому не видны до завершения транзации.

UPDATE IN PLACE 1: TABLE

UPDATE IN PLACE 2: SCAN

UPDATE IN PLACE 3: UPDATE

UPDATE IN PLACE 4: UPDATED

UPDATE IN PLACE 5: INSERT

UPDATE IN PLACE 6: DELETE

UPDATE IN PLACE 7

UPDATE IN PLACE 8: REALITY

MVCC в PostgreSQL

MVCC – механизм, позволяющий каждой транзакции видеть свой «слепок» (snapshot) базы данных на определенный момент времени, хотя данные на текущий момент уже могли измениться.

В PostgreSQL MVCC обеспечивается тем, что данные не удаляются, старые версии строк остаются с отметками об окончании их актуальности, параллельно заводятся новые версии строк. Специальный процесс VACUUM удаляет старые версии строк.

MVCC в PostgreSQL

Специальные атрибуты

• ctid – физическое расположение кортежа: (страница,смещение)
• xmin – id транзакции, создавшей данную строку
• xmax – id транзакции, удалившей данную строку
• cmin – id команды транзакции, создавшей данную строку
• cmax – id команды транзакции, удалившей данную строку

MVCC 1: TABLE

MVCC 2: UPDATE

MVCC 3: UPDATE IN PROGRESS

MVCC 4: UPDATE IN PROGRESS

MVCC 5: UPDATED

MVCC 6: INSERT

MVCC 7: INSERTED

MVCC 8: DELETE

MVCC 9: DELETED

Проблемы MVCC

• MVCC работает идеально, когда мы пишем номер окончания транзакции, но мы его заранее не знаем;
• Надо как-то избавляться от "мертвых" кортежей.

Варианты реализации MVCC

Heap (PostgreSQL)

• Все кортежи лежат в общей "куче".
• Мертвые кортежи удаляются в фоне отдельным сборщиком мусора (AUTOVACUUM).
• Фиксация и откат транзакции имеют одинаковую стоимость.

Rollback segment (MySQL, Oracle)

• В страницах таблицы лежат только актуальные данные.
• Старые версии хранятся в журнале отката.
• Не требуется сборщик мусора и более эффективное использование дискового пространства.
• Долгий откат транзакции.

Уровни изолированности транзакций

• Read uncommitted - чтение незафиксированных данных
• Read committed (по-умолчанию) - чтение фиксированных данных
• Repeatable read - повторяемость чтения
• Serializable - упорядочиваемость

Потерянное обновление (Lost Update)

Потерянное обновление происходит в случае перезатирания изменений другой транзакцией до завершения транзакции, сделавшей изменения.

UPDATE tbl1 SET f2=f2+20 WHERE f1=1;

UPDATE tbl1 SET f2=f2+25 WHERE f1=1;

«Грязное» чтение (Dirty Read)

Чтение данных, добавленных или изменённых еще не завершенной транзакцией.

SELECT f2 FROM tbl1 WHERE f1=1;
f2
---------
100
(1 row)

UPDATE tbl1 SET f2=200 WHERE f1=1;

SELECT f2 FROM tbl1 WHERE f1=1;
f2
---------
200
(1 row)

ROLLBACK;

Неповторяющееся чтение (Non-Repeatable Read)

При повторном чтении в рамках одной транзакции ранее прочитанные данные оказываются изменёнными.

SELECT f2 FROM tbl1 WHERE f1=1;

UPDATE tbl1 SET f2=f2+1 WHERE f1=1;

COMMIT;

SELECT f2 FROM tbl1 WHERE f1=1;

Чтение "фантомов" (Phantom Reads)

Ситуация, когда при повторном чтении в рамках одной транзакции одна и та же выборка дает разные множества строк.

От неповторяющегося чтения оно отличается тем, что результат повторного обращения к данным изменился не из-за изменения/удаления самих этих данных, а из-за появления новых (фантомных) данных.

SELECT SUM(f2) FROM tbl1;

INSERT INTO tbl1 (f1,f2) VALUES (15,20);

COMMIT;

SELECT SUM(f2) FROM tbl1;

Аномалии сериализации

Ситуация, когда параллельное выполнение транзакций приводит к результату, невозможному при последовательном выполнении тех же транзакций.

SELECT SUM(value) FROM mytab
WHERE class = 1;
---------
30
(1 row)

SELECT SUM(value) FROM mytab
WHERE class = 2;
---------
300
(1 row)

INSERT INTO mytab (value, class)
VALUES (30, 2)

INSERT INTO mytab (value, class)
VALUES (300, 1)

COMMIT;

COMMIT;

Уровни изолированности транзакций

Уровни изолированности транзакций

Хранимые процедуры, функции и триггеры

Хранимые процедуры

CREATE TABLE test1 (a int);

CREATE PROCEDURE transaction_test1()
AS $$
BEGIN
	FOR i IN 0..9 LOOP
		INSERT INTO test1 (a) VALUES (i);
		IF i % 2 = 0 THEN
			RAISE NOTICE 'i=%, txid=% will be committed', i, txid_current();
			COMMIT;
		ELSE
			RAISE NOTICE 'i=%, txid=% will be rolledback', i, txid_current();
			ROLLBACK;
		END IF;
	END LOOP;
END
$$
LANGUAGE PLPGSQL;

Хранимые процедуры

В PostgreSQL хранимые процедуры доступны с версии PostgreSQL 11.

В более старых версиях PostgreSQL можно довольствоваться только функциями.

Чем отличаются хранимые процедуры от функций?

• Функции можно использовать в запросах
• Хранимые процедуры могут начинать транзакции

Хранимые процедуры

CREATE [ OR REPLACE ] PROCEDURE
    name ( [ [ argmode ] [ argname ]
    argtype [ { DEFAULT | = } default_expr ] [, ...] ] )
  { LANGUAGE lang_name
    | TRANSFORM { FOR TYPE type_name } [, ... ]
    | [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
    | SET configuration_parameter { TO value | = value | FROM CURRENT }
    | AS 'definition'
    | AS 'obj_file', 'link_symbol'
  } ...

Хранимые процедуры (+)

• Разделение логики с другими приложениями. Хранимые процедуры инкапсулируют функциональность; это обеспечивает связность доступа к данным и управления ими между различными приложениями.
• Изоляция пользователей от таблиц базы данных. Это позволяет давать доступ к хранимым процедурам, но не к самим данным таблиц.
• Обеспечивает механизм защиты.
• Улучшение выполнения как следствие сокращения сетевого трафика. С помощью хранимых процедур множество запросов могут быть объединены.

Хранимые процедуры (-)

• Повышение нагрузки на сервер баз данных в связи с тем, что большая часть работы выполняется на серверной части, а меньшая - на клиентской.
• Дублирование логики своего приложения в двух местах: серверный код и код для хранимых процедур, тем самым усложняя процесс манипулирования данными.
• Миграция с одной СУБД на другую (DB2, SQL Server и др.) может привести к проблемам.

Хранимые процедуры: пример

DROP FUNCTION IF EXISTS add(integer, integer);

CREATE FUNCTION add(integer, integer) RETURNS integer
    AS 'select $1 + $2;'
    LANGUAGE SQL
    IMMUTABLE
    RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION inc(i integer) RETURNS integer AS $$
        BEGIN
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;

SELECT inc(42), add(2, 3);

Хранимые процедуры: пример

CREATE FUNCTION python_max (a integer, b integer)
  RETURNS integer
AS $$
  if a > b:
    return a
  return b
$$ LANGUAGE plpythonu;

CREATE FUNCTION perl_max (integer, integer) RETURNS integer AS $$
    my ($x, $y) = @_;
    if (not defined $x) {
        return undef if not defined $y;
        return $y;
    }
    return $x if not defined $y;
    return $x if $x > $y;
    return $y;
$$ LANGUAGE plperl;

PL/pgSQL: IF

IF boolean-expression THEN
    statements
[ ELSIF boolean-expression THEN
    statements
    ...]]
[ ELSE
    statements ]
END IF;

PL/pgSQL: LOOP

LOOP
    -- some computations
    EXIT WHEN count > 100;
    CONTINUE WHEN count < 50;
    -- some computations for count IN [50 .. 100]
END LOOP;

WHILE amount_owed > 0 LOOP
    -- some computations here
END LOOP;

WHILE NOT done LOOP
    -- some computations here
END LOOP;

PL/pgSQL: FOR

FOR i IN 1..10 LOOP
    -- i will take on the values 1,2,3,4,5,6,7,8,9,10 within the loop
END LOOP;

FOR i IN REVERSE 10..1 LOOP
    -- i will take on the values 10,9,8,7,6,5,4,3,2,1 within the loop
END LOOP;

FOR i IN REVERSE 10..1 BY 2 LOOP
    -- i will take on the values 10,8,6,4,2 within the loop
END LOOP;

PL/pgSQL: FOR target IN query LOOP

CREATE FUNCTION cs_refresh_mviews() RETURNS integer AS $$
DECLARE
    mviews RECORD;
BEGIN
    RAISE NOTICE 'Refreshing materialized views...';

    FOR mviews IN SELECT * FROM cs_materialized_views ORDER BY sort_key LOOP

        -- Now "mviews" has one record from cs_materialized_views

        RAISE NOTICE 'Refreshing view %s ...', quote_ident(mviews.mv_name);
        EXECUTE format('TRUNCATE TABLE %I', mviews.mv_name);
        EXECUTE format('INSERT INTO %I %s', mviews.mv_name, mviews.mv_query);
    END LOOP;

    RAISE NOTICE 'Done refreshing materialized views.';
    RETURN 1;
END;
$$ LANGUAGE plpgsql;

PL/pgSQL: FOR target IN query LOOP

CREATE TABLE foo (fooid INT, foosubid INT, fooname TEXT);
INSERT INTO foo VALUES (1, 2, 'three');
INSERT INTO foo VALUES (4, 5, 'six');

CREATE OR REPLACE FUNCTION get_all_foo() RETURNS SETOF foo AS
$BODY$
DECLARE
    r foo%rowtype;
BEGIN
    FOR r IN
        SELECT * FROM foo WHERE fooid > 0
    LOOP
        -- can do some processing here
        RETURN NEXT r; -- return current row of SELECT
    END LOOP;
    RETURN;
END
$BODY$
LANGUAGE plpgsql;

SELECT * FROM get_all_foo();

Триггеры

CREATE [ CONSTRAINT ] TRIGGER name
       { BEFORE | AFTER | INSTEAD OF }{ event [ OR ... ] }
    ON table_name
    [ FROM referenced_table_name ]
    [ NOT DEFERRABLE | [ DEFERRABLE ]
        [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
    [ FOR [ EACH ] { ROW | STATEMENT } ]
    [ WHEN ( condition ) ]
    EXECUTE PROCEDURE function_name ( arguments )

where event can be one of:

    INSERT
    UPDATE [ OF column_name [, ... ] ]
    DELETE
    TRUNCATE

Триггеры: переменные

NEW

Тип данных RECORD. Переменная содержит новую строку базы данных для команд INSERT/UPDATE.

OLD

Тип данных RECORD. Переменная содержит старую строку базы данных для команд UPDATE/DELETE.

TG_NAME

Тип данных name. Переменная содержит имя сработавшего триггера.

TG_OP

Тип данных text. Строка, содержащая INSERT, UPDATE, DELETE или TRUNCATE, в зависимости от того, для какой операции сработал триггер.

TG_TABLE_NAME

Тип данных name. Имя таблицы, для которой сработал триггер.

Триггеры: пример

CREATE OR REPLACE FUNCTION process_emp_audit() RETURNS TRIGGER AS $emp_audit$
    BEGIN
        --
        -- Create a row in emp_audit to reflect the operation performed on emp,
        -- make use of the special variable TG_OP to work out the operation.
        --
        IF (TG_OP = 'DE­LETE') THEN
            INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
            RETURN OLD;
        ELSIF (TG_OP = 'UP­DATE') THEN
            INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
            RETURN NEW;
        ELSIF (TG_OP = 'IN­SERT') THEN
            INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
            RETURN NEW;
        END IF;
        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$emp_audit$ LANGUAGE plpgsql;

CREATE TRIGGER emp_audit
AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE PROCEDURE process_emp_audit();

Триггеры: пример

CREATE TABLE IF NOT EXISTS payment
(
    id         SERIAL                                NOT NULL PRIMARY KEY,
    amount     BYTEA                                 NOT NULL,
    currency   CHAR(3)                               NOT NULL,
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL,
);

CREATE TRIGGER modify_payment_updated_at
    BEFORE UPDATE
    ON payment
    FOR EACH ROW
EXECUTE PROCEDURE public.moddatetime(updated_at);

Представления (VIEW)

VIEW

Представление (VIEW)

Объект базы данных, являющийся результатом выполнения запроса к базе данных, определенного с помощью оператора SELECT, в момент обращения к представлению.

CREATE
    [ OR REPLACE ]
    [ TEMP | TEMPORARY ]
    [ RECURSIVE ]
VIEW name [ ( column_name [, ...] ) ]
    [ WITH ( view_option_name [= view_option_value] [, ... ] ) ]
    AS query
    [ WITH [ CASCADED | LOCAL ] CHECK OPTION ];

Преимущества VIEW

• Дает возможность гибкой настройки прав доступа к данным за счет того, что права даются не на таблицу, а на представление.
• Позволяет разделить логику хранения данных и программного обеспечения.
• Удобство в использовании за счет автоматического выполнения таких действий как доступ к определенной части строк и/или столбцов, получение данных из нескольких таблиц и их преобразование с помощью различных функций.

Ограничения VIEW

• Нельзя повесить триггер на представление;
• Таблицы и представления, присутствующие в определении представления должны существовать.

Особенности VIEW

CREATE VIEW v AS
    SELECT a.id, b.id FROM a, b;
-- ERROR: column "id" specified more than once
-- SQL-состояние: 42701

CREATE VIEW v (a_id, b_id) AS
    SELECT a.id, b.id FROM a,b;
CREATE VIEW v AS
    SELECT a.id a_id, b.id b_id FROM a,b;

Особенности VIEW

• Если в обоих операторах встречается условие WHERE, то оба этих условия будут выполнены как если бы они были объединены оператором AND.
• Если в определении представления есть конструкция ORDER BY, то она будет работать только в случае отсутствия во внешнем операторе SELECT, обращающемся к представлению, собственного условия сортировки. При наличии конструкции ORDER BY во внешнем операторе сортировка, имеющаяся в определении представления, будет проигнорирована.

Изменяемые VIEW

• Список FROM в запросе, определяющем представлении, должен содержать ровно один элемент, и это должна быть таблица или другое изменяемое представление.
• Определение представления не должно содержать предложения WITH, DISTINCT, GROUP BY, HAVING, LIMIT и OFFSET на верхнем уровне запроса.
• Определение представления не должно содержать операции с множествами (UNION, INTERSECT и EXCEPT) на верхнем уровне запроса.
• Список выборки в запросе не должен содержать агрегатные и оконные функции, а также функции, возвращающие множества.

Изменение VIEW

CREATE TABLE films (
    imdb        varchar(16) PRIMARY KEY,
    title       varchar(40) NOT NULL,
    kind        varchar(10)
);

CREATE OR REPLACE VIEW comedies AS
    SELECT *
    FROM films
    WHERE kind = 'Comedy'
    WITH CASCADED CHECK OPTION;

INSERT INTO comedies (imdb, title, kind)
    VALUES ('tt0114709', 'Toy Story 1', 'Animation');
-- ERROR:  new row violates check option for view "comedies"
-- DETAIL:  Failing row contains (tt0114709, Toy Story 1, Animation).

INSERT INTO comedies (imdb, title, kind)
    VALUES ('tt1156398', 'Zombieland', 'Comedy');

Materialized VIEW

CREATE MATERIALIZED VIEW my_matview AS SELECT * FROM mytab;

CREATE TABLE my_matview AS SELECT * FROM mytab;

Разница состоит в том, что материализованное представление впоследствии нельзя будет изменить непосредственно, а запрос, создающий материализованное представление, сохраняется точно так же, как запрос представления, и получить актуальные данные в материализованном представлении можно так.

REFRESH MATERIALIZED VIEW my_matview;

Регистронезависимый поиск

Регистронезависимый поиск. Способы

• COLLATION
• LOWER(name)
• CITEXT

COLLATION

Это ряд правил, согласно которым сортируются и сравниваются данные.

Простая сортировка обычно даёт не тот результат, который ожидается пользователем.

COLLATION

В разных языках разные преобразования в верхний/нижний регистр

SELECT
    LOWER('WINDOWS' COLLATE "en_US.utf8"),
    UPPER('linux' COLLATE "en_US.utf8")

SELECT
    LOWER('WINDOWS' COLLATE "tr_TR.utf8"),
    UPPER('linux' COLLATE "tr_TR.utf8")

COLLATION

В PostgreSQL поддержка COLLATION реализована на базе C-функции strcoll_l.

Это порождает ряд проблем:

• Список COLLATION-ов зависит от настроек операционной системы;
• Разное поведение COLLATION-ов под Linux/Windows/MacOS;
• Нет регистронезависимого сравнения.

С PostgreSQL 10 (окт 2019) появилась поддержка COLLATION на базе библиотеки ICU.

LOWER(name)

CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    name TEXT COLLATE "ru_RU.utf8",
    ...
);
CREATE UNIQUE INDEX uniq_name ON person (LOWER(name));

SELECT * FROM person WHERE LOWER(name) = LOWER('Jack Sparrow');

Проблемы:

• Нужно не забывать LOWER(name) во всех запросах.
• Если вы объявляете столбец как UNIQUE или PRIMARY KEY, неявно создаваемый индекс будет чувствительным к регистру.

CITEXT

CREATE EXTENSION IF NOT EXISTS citext;
CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    name CITEXT COLLATE "ru_RU.utf8",
    ...
);
CREATE UNIQUE INDEX uniq_name ON person (name);

SELECT * FROM person WHERE name = 'Jack Sparrow';