СУБД PostgreSQL

Данный репозиторий является базой знаний об открытом программном обеспечении PostgreSQL - системе управления базами данных. Вся информация взята из общедоступных источников и несет исключительно образовательную цель.

 

Реляционная модель данных

Реляционная модель данных - это математическая модель, которая описывает способ организации и хранения данных в базе данных. Она основана на понятии "таблицы" или "реляции", которая состоит из строк и столбцов.

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

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

Преимущества реляционной модели данных:

1. Простота структуры и понятность модели,
2. Гибкость и возможность создания сложных запросов для извлечения данных,
3. Независимость от физической реализации данных,
4. Высокая надежность и целостность данных,
5. Поддержка множества операций, таких как сортировка, поиск, фильтрация и соединение данных.

Транзакции и транзакционность

Транзакция в базе данных представляет собой последовательность операций базы данных, которые выполняются как единое целое. Транзакция обладает следующими свойствами, известными как ACID-свойства:

1. Атомарность (Atomicity): (Всё или ничего) Транзакция либо выполняется полностью, либо не выполняется вообще. Если одна из операций внутри транзакции не может быть выполнена, то вся транзакция откатывается, и все изменения, сделанные до этого, отменяются.

2. Согласованность (Consistency): (Ограничения целостности и пользовательские ограничения) Транзакция должна приводить базу данных из одного согласованного состояния в другое согласованное состояние. Это означает, что после завершения транзакции должны быть выполнены все правила целостности БД.

3. Изолированность (Isolation): (Влияние параллельных процессов) Каждая транзакция должна выполняться изолированно от других транзакций. Изменения, внесенные одной транзакцией, должны быть видимы только после успешного завершения этой транзакции. Изолированность обеспечивается с помощью уровней изоляции транзакций. PostgreSQL поддерживает несколько уровней изоляции транзакций, которые позволяют контролировать видимость изменений, выполняемых другими транзакциями. Это позволяет избежать конфликтов одновременного доступа к данным и обеспечивает изоляцию транзакций друг от друга.

4. Долговечность (Durability): (Сохранность данных даже после сбоя) После успешного завершения, изменения, сделанные в транзакции, должны быть сохранены и доступны даже в случае сбоя системы или отключения питания. Это обеспечивается с помощью журнала предзаписи (WAL). Все изменения данных записываются сначала в журнал предзаписи перед тем, как они будут применены к реальной базе данных. Это позволяет восстановить данные в случае сбоя, воссоздавая состояние базы данных до сбоя с помощью журнала предзаписи.

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

-- Начало транзакции
BEGIN;
    -- Выполнение операций внутри транзакции
    UPDATE users
    SET balance = balance - 100
    WHERE user_id = 1;

    UPDATE products
    SET quantity = quantity - 1
    WHERE product_id = 100;

    -- Проверка результатов операций
    SELECT * FROM users WHERE user_id = 1;
    SELECT * FROM products WHERE product_id = 100;

-- Если все операции выполнены успешно, фиксируем транзакцию
COMMIT;

-- Если произошла ошибка или нужно отменить изменения, откатываем транзакцию
ROLLBACK;

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

Для отката к установленной точке сохранения предназначена команда ROLLBACK TO SAVEPOINT. Чтобы уничтожить точку сохранения, сохраняя изменения, произведённые после того, как она была установлена, применяется команда RELEASE SAVEPOINT.

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

Стандарт SQL требует, чтобы точка сохранения уничтожалась автоматически, когда устанавливается другая точка сохранения с тем же именем. В PostgreSQL старая точка сохранения остаётся, хотя при откате или уничтожении будет выбираться только самая последняя.

BEGIN;
    INSERT INTO table1 VALUES (1);
    SAVEPOINT my_savepoint;
    INSERT INTO table1 VALUES (2);
    SAVEPOINT my_savepoint;
    INSERT INTO table1 VALUES (3);

    -- откат ко второй точке сохранения
    ROLLBACK TO SAVEPOINT my_savepoint;
    SELECT * FROM table1;               -- вывод строк 1 и 2

    -- освобождение второй точки сохранения
    RELEASE SAVEPOINT my_savepoint;

    -- откат к первой точке сохранения
    ROLLBACK TO SAVEPOINT my_savepoint;
    SELECT * FROM table1;               -- вывод только строки 1
COMMIT;

По умолчанию psql работает в режиме автофиксации (\set AUTOCOMMIT on). Это приводит к тому, что любая команда, выданная без явного указания начала транзакции, сразу же фиксируется. Режим без автофиксации (\set AUTOCOMMIT off) неявно начинает транзакцию при первой выданной команде; изменения надо фиксировать самостоятельно.