PostgreSQL шпаргалка

При инициализации создаются три одинаковые базы данных:

• template0 используется для восстановления из логической резервной копии и никогда недолжна меняться
• template1 служит шаблоном для всех остальных баз данных, которые может создать пользователь в этом кластере
• postgres представляет собой обычную базу данных, которую можно использовать по своему усмотрению

---

$ createdb mydb - создания базы данных

$ dropdb mydb - удалить базу данных

Cоздание таблицы

CREATE TABLE name_table (
city varchar(80),
temp_lo int, -- comment
prcp real,
date date
);

Удаление таблицы

DROP TABLE name_table;

PostgreSQL поддерживает стандартные типы SQL: int, smallint, real, double precision, char(N), varchar(N), date, time, timestamp и interval, а также др.
PostgreSQL можно расширять, создавая набор собственных типов данных.

Добавление строк в таблицу

INSERT INTO name_table (city, temp_lo, temp_hi, prcp, date)
VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');

Выполнение запроса

SELECT * FROM weather
WHERE city = 'San Francisco' AND prcp > 0.0; --указывается логическое выражение (проверка истинности), которое служит фильтром строк

SELECT * FROM weather
ORDER BY city; --порядок сортировки

SELECT DISTINCT city --убрать дублирующиеся строки
FROM weather;

Соединения таблиц

SELECT * FROM weather w JOIN cities c ON w.city = c.name;

Агрегатные функции
count (количество)
sum (сумму)
avg (среднее)
max (максимум)
min (минимум)

Изменение данных

UPDATE weather
SET temp_hi = temp_hi - 2 WHERE date > '1994-11-28';

Удаление данных

DELETE FROM weather WHERE city = 'Hayward';

---

Транзакции
Транзакция — объединяет последовательность действий в одну операцию «всё или ничего».

BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- ...
COMMIT; --блок транзакции

BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
SAVEPOINT my_savepoint; --точки сохранения
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
-- ошибочное действие... забыть его и использовать счёт Уолли
ROLLBACK TO my_savepoint; --возврат
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Wally';
COMMIT;

Оконные функции
Оконная функция выполняет вычисления для набора строк, некоторым образом связанных с текущей строкой.
Вызов оконной функции всегда содержит предложение OVER. Предложение PARTITION BY разделяет строки по группам.

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname)
FROM empsalary;

---

Нормализация

Нормализация это процесс приведения базы данных к нормальным формам с целью избавления от логической избыточности.

Транзакция

Транзакция - это элементарная операция в базе данных. Транзакция может состоять и из нескольких операций: в этом ключе - это логически целостная процедура, в которой должны быть выполнены либо все операции - либо ни одна из них.
Транзакция начинается с команды BEGIN и заканчивается командой COMMIT либо отменяется командой ROLLBACK.

Требования, предъявляемые к транзакции. ACID

• Атомарность (atomicity). Это свойство означает, что либо транзакция будет зафиксирована в базе данных полностью, т. е. будут зафиксированы результаты выполнения всех ее операций, либо не будет зафиксирована ни одна операция транзакции.
• Согласованность (consistency). Это свойство предписывает, чтобы в результате успешного выполнения транзакции база данных была переведена из одного согласованного состояния в другое согласованное состояние.
• Изолированность (isolation). Во время выполнения транзакции другие транзакции должны оказывать по возможности минимальное влияние на нее.
• Долговечность (durability). После успешной фиксации транзакции пользователь должен быть уверен, что данные надежно сохранены в базе данных и впоследствии могут быть извлечены из нее, независимо от последующих возможных сбоев в работе системы.

Выполнение транзакций

Можно выполнять транзакции последовательно или параллельно.
При параллельных транзакций могут возникать "феномены":

• Потерянное обновление (lost update)
  Когда разные транзакции одновременно изменяют одни и те же данные, то после фиксации изменений может оказаться, что одна транзакция перезаписала данные, обновленные и зафиксированные другой транзакцией.
• «Грязное» чтение (dirty read)
  Транзакция читает данные, измененные параллельной транзакцией, которая еще не завершилась. Если эта параллельная транзакция в итоге будет отменена, тогда окажется, что первая транзакция прочитала данные, которых нет в системе.
• Неповторяющееся чтение (non-repeatable read)
  При повторном чтении тех же самых данных в рамках одной транзакции оказывается, что другая транзакция успела изменить и зафиксировать эти данные. В результате тот же самый запрос выдает другой результат.
• Фантомное чтение (phantom read)
  Транзакция повторно выбирает множество строк в соответствии с одним и тем же критерием. В интервале времени между выполнением этих выборок другая транзакция добавляет новые строки и успешно фиксирует изменения.
  В результате при выполнении повторной выборки в первой транзакции может быть получено другое множество строк.
• Аномалия сериализации (serialization anomaly)
  Результат успешной фиксации группы транзакций, выполняющихся параллельно, не совпадает с результатом ни одного из возможных вариантов упорядочения этих транзакций, если бы они выполнялись последовательно.

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

Для двух транзакций, скажем, A и B, возможны только два варианта упорядочения при их последовательном выполнении:
1 сначала A, затем B
2️ сначала B, затем A

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

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

Уровни изоляции в SQL

Изолированность транзакции показывает то, насколько сильно влияют друг на друга параллельно выполняющиеся транзакции.

• Read Uncommitted
  Уровень, имеющий самую плохую согласованность данных, но самую высокую скорость выполнения транзакций. Каждая транзакция видит незафиксированные изменения другой транзакции (феномен грязного чтения). Данный уровень можно использовать, например, для примерных расчетов чего-либо.
  Однако в PostgreSQL требования, предъявляемые к этому уровню, более строгие, чем в стандарте: чтение «грязных» данных на этом уровне не допускается.

• Read Committed Для этого уровня параллельно исполняющиеся транзакции видят только зафиксированные изменения из других транзакций. Не допускается чтение «грязных» (незафиксированных) данных.

• Repeatable Read
  Не допускается чтение «грязных» (незафиксированных) данных и неповторяющееся чтение.
  Т.е. мы не видим в исполняющейся транзакции измененные и удаленные записи другой транзакцией. Но все еще видим вставленные записи из другой транзакции.
  В PostgreSQL на этом уровне не допускается также фантомное чтение.

• Serializable
  Не допускается ни один из феноменов, перечисленных выше, в том числе и аномалии сериализации.
  Мы получаем максимальную согласованность данных, никакие лишние данные не зафиксируются.
  Конкретный уровень изоляции обеспечивает сама СУБД с помощью своих внутренних механизмов. Его достаточно указать в команде при старте транзакции.

Блокировки

СУБД позволяют также создавать блокировки данных как на уровне отдельных строк, так и на уровне целых таблиц.
Команда SELECT имеет предложение FOR UPDATE, которое позволяет заблокировать отдельные строки таблицы с целью их последующего обновления.
SELECT * FROM table WHERE column_name ~ 'some text' FOR UPDATE;

---

Документация PostgreSQL на русском