Объяснение мема Postgres - Уровень 7: зона Черной дыры

Добро пожаловать в зону Черной дыры! Поздравляем, Вы добрались до самых глубин знаний о PostgreSQL. Приготовьтесь к эзотерическим и передовым темам, на которые отваживаются только самые смелые!

Проблема Хэллоуина (Halloween problem)

Halloween Problem - это ошибка базы данных, о которой должен знать разработчик системы баз данных.

В день Хэллоуина 1976 года несколько инженеров работали над запросом, который должен был повысить зарплату каждому сотруднику, зарабатывающему менее $25 000, на 10%:

Увеличение зарплаты < 25 000 на 10 %:

update employee set salary = salary + (salary / 10) where salary < 25000

Определение таблицы:

create table employee(id integer primary key, salary integer);
insert into employee values (1, 17000), (2, 27000)

Этот запрос успешно выполнялся в базе данных, но после его завершения все сотрудники в базе данных заработали не менее $25 000. Это объясняется тем, что обновленные строки также были видны механизму выполнения запроса, и поскольку критерий соответствия в предложении where все еще был истинным, база данных продолжала увеличивать зарплату сотрудников до тех пор, пока она не превысила 25 000 долларов.

Внимание

PostgreSQL НЕ имеет такой проблемы. Halloween Problem - это ошибка в проектировании базы данных; очевидно, что любая база данных, содержащая эту ошибку, ненадежна.

Dee и Dum

Таблица dee - это таблица, в которой нет столбцов, но есть одна строка. Она играет роль True.
Таблица dum - это таблица, в которой нет ни столбцов, ни строк. Она играет роль False.

Эти теоретические таблицы и терминология были созданы Хью Дарвеном. Более подробно о применении этих таблиц в PostgreSQL можно прочитать в статье Создание таблиц Dum и Deeв PostgreSQL. Для их реализации используются триггерные функции PostgreSQL.

Поледовательные типы (SERIAL)

Типы данных smallserial, serial и bigserial не являются настоящими типами, а представляют собой просто удобное средство для создания столбцов с уникальными идентификаторами (подобное свойству AUTO_INCREMENT в некоторых СУБД):

Serial:

create table tablename (
    colname serial
);

Аналогичный запрос:

create sequence tablename_colname_seq as integer;
create table tablename (
    colname integer not null default nextval('tablename_colname_seq')
);
alter sequence tablename_colname_seq owned by tablename.colname;

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

Значение, выделенное из последовательности, все равно "израсходуется", даже если строка, содержащая это значение, никогда не будет успешно вставлена в столбец таблицы. Это может произойти, например, в случае отката транзакции. Именно поэтому типы serial считаются нетранзакционными, так как они не откатывают свое значение в случае отката транзакции.

create table counter(c serial primary key);
-- CREATE TABLE; Query returned successfully.
 
insert into counter values (default);
-- INSERT 0 1; Query returned successfully. <- uses id 1
 
insert into counter values (default);
-- INSERT 0 1; Query returned successfully. <- uses id 2
 
begin;
insert into counter values (default);
abort;
-- ROLLBACK; Query returned successfully. <- uses id 3, rollback doesn't give it back
 
insert into counter values (default);
-- INSERT 0 1; Query returned successfully. <- uses id 4
 
table counter;
-- c
--
-- 1
-- 2
-- 4 <- the number 3 is missing

allballs

Строка 'allballs' при переводе во время превратится во время полуночи (00:00:00). Это связано с тем, что "allballs" означает "все нули". Этот жаргон исторически использовался в военных переговорах.

select 'allballs'::time;

-- time
--
-- 00:00:00

Дополнительная информация: “allballs” в PostgreSQL

fsyncgate

fsync является системным вызовом ОС, и в Linux он используется для синхронизации состояния ядра файла с устройством хранения. Другими словами, он гарантирует, что данные, записанные в файл, действительно записаны на устройство хранения и сохраняются, передавая все измененные внутриядерные данные файла на диск или другое устройство хранения.

Термин "fsyncgate 2018" относится к скандалам и спорам вокруг проблем надежности системного вызова fsync в списке рассылки PostgreSQL (как "PostgreSQL неправильно использовал fsync в течение 20 лет").

Этот вопрос был поднят Крейгом Рингером:

Привет всем!

Некоторое время назад я столкнулся с проблемой, когда пользователь столкнулся с повреждением данных после ошибки хранения. PostgreSQL сыграл определенную роль в этом повреждении...

TL;DR: Pg должен ПАНИКОВАТЬ при возврате EIO в fsync(). Повторное выполнение fsync() не является нормальным, по крайней мере, в Linux. Когда fsync() возвращает успех, это означает "все записи с момента последнего fsync попали на диск", но мы предполагаем, что это означает "все записи с момента последнего УСПЕШНОГО fsync попали на диск".

...

Дополнительная информация: Работа PostgreSQL' с ошибкой fsync() небезопасна и чревата потерей данных

Каждый оператор SQL фактически является соединением

Каждый оператор SQL может быть представлен с помощью join.

select age, age * age as age_squared from person;

Например, в приведенном выше запросе вместо того, чтобы вычислять возраст * возраст явно, мы могли бы просто найти его в таблице квадратичных функций (со столбцами x и xx):

select age, xx as age_squared from person join squares on age = x;

Дополнительная информация: JOIN: Окончательная проекция

`NULL`

NULL - достаточно хитрое явление. Согласны?