SQL Ready | Базы Данных
Почему ctid нельзя использовать как постоянный идентификатор!
В PostgreSQL каждая строка имеет системную колонку ctid. Она содержит физический адрес текущей версии строки в таблице: номер страницы и позицию строки внутри этой страницы.
Иногда ctid используют для поиска, удаления или диагностики отдельных записей, но важно понимать, что это не постоянный идентификатор строки. Значение ctid может измениться в процессе обычной работы базы данных, поэтому использовать его в прикладной логике нельзя.
Для начала создадим простую таблицу с первичным ключом и несколькими тестовыми записями, чтобы наглядно проследить, как изменяется значение ctid.
CREATE TABLE users (
id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name TEXT
);
Теперь добавим несколько строк, с которыми будем работать в дальнейших примерах.
INSERT INTO users (name)
VALUES
('Alice'),
('Bob'),
('Charlie');
Посмотрим, какое значение ctid PostgreSQL присвоил каждой записи после вставки.
SELECT
ctid,
id,
name
FROM users;
Результат может выглядеть так:
ctid | id | name
------+----+--------
(0,1) | 1 | Alice
(0,2) | 2 | Bob
(0,3) | 3 | Charlie
Теперь изменим одну из строк. На первый взгляд кажется, что PostgreSQL просто обновит существующую запись, однако механизм хранения данных работает иначе.
UPDATE users
SET name = 'Robert'
WHERE id = 2;
После выполнения UPDATE снова посмотрим значения ctid и сравним их с предыдущим результатом.
SELECT
ctid,
id,
name
FROM users;
Теперь можно увидеть, что значение ctid изменилось. Например:
ctid | id | name
------+----+---------
(0,1) | 1 | Alice
(0,4) | 2 | Robert
(0,3) | 3 | Charlie
Это происходит из-за механизма MVCC. При выполнении UPDATE PostgreSQL не изменяет строку на месте, а создаёт её новую версию, которая получает новый физический адрес (ctid). Старая версия строки некоторое время остаётся в таблице и может быть видима другим транзакциям в зависимости от их снимка данных.
Изменение ctid происходит не только при UPDATE. Любые операции, которые физически переписывают таблицу, также приводят к изменению физических адресов строк. Например:
VACUUM FULL users;
или
CLUSTER users USING users_pkey;
Поэтому использовать ctid в качестве внешнего ключа, хранить его в приложении или считать постоянным идентификатором записи нельзя.
При этом ctid остаётся полезным инструментом для служебных задач. Один из самых распространённых случаев — удалить одну запись среди полностью одинаковых дубликатов, когда значения всех пользовательских столбцов совпадают и отличить строки обычными средствами невозможно.
Например, создадим отдельную таблицу без ограничений уникальности:
CREATE TABLE duplicate_users (
name TEXT
);
Добавим одинаковые строки:
INSERT INTO duplicate_users (name)
VALUES
('Alice'),
('Alice'),
('Bob');
Удалим только одну из двух одинаковых строк:
DELETE
FROM duplicate_users
WHERE ctid = (
SELECT ctid
FROM duplicate_users
WHERE name = 'Alice'
LIMIT 1
);
В результате останется одна строка с именем Alice. В этом примере ctid определяется и используется в рамках одного SQL-запроса, поэтому используется актуальный физический адрес версии строки и не возникает проблемы с использованием устаревшего значения.
🔥 ctid — это физический адрес текущей версии строки, а не её постоянный идентификатор. Для связи данных всегда используйте первичный ключ, а ctid оставьте для диагностических и служебных операций.
➡️ SQL Ready | #практика
25 · 2.3K ·