Генерация последовательности заказов

Question

Есть ли способ генерировать какой-то порядковый идентификатор для записей таблицы?

Предположим, что у нас есть два потока, выполняющих запросы:

Тема 1:

begin;
insert into table1(id, value) values (nextval('table1_seq'), 'hello');
commit;

Тема 2:

begin;
insert into table1(id, value) values (nextval('table1_seq'), 'world');
commit;

Вполне возможно (в зависимости от времени), что внешний наблюдатель увидит запись (2, "мир") перед (1, "привет").

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

Итак, есть ли способ получить записи в том порядке, в котором они были вставлены? Может быть, OID могут помочь?

Answer 1

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

Ну, есть конкретные столбцы системы Postgres cmin а также ctid Вы могли бы злоупотребить до некоторой степени.

ID кортежа (ctid) содержит номер блока файла и положение в блоке для строки. Так что это представляет текущий физический порядок на диске. Более поздние дополнения будут иметь больший ctid нормально. Ваше утверждение SELECT может выглядеть так

SELECT *, ctid   -- save ctid from last row in last_ctid
FROM   tbl
WHERE  ctid > last_ctid
ORDER  BY ctid

ctid имеет тип данных tid, Пример: '(0,9)'::tid

Однако он не стабилен как долгосрочный идентификатор, так как VACUUM или любой одновременный UPDATE или некоторые другие операции могут изменить физическое местоположение кортежа в любое время. Хотя на время транзакции она стабильна. И если вы просто вставляете и ничего больше, это должно работать локально для ваших целей.

Я бы добавил столбец с отметкой времени по умолчанию now() в добавок к serial колонка...

Я бы также позволил колонке по умолчанию заполнить ваш id столбец (а serial или же IDENTITY колонка). Это извлекает число из последовательности на более позднем этапе, чем явная выборка, а затем вставляет его, тем самым сводя к минимуму (но не устраняя) окно для состояния гонки - вероятность того, что более низкая id будет вставлен позже. Подробные инструкции:

• Столбец таблицы автоинкремента

Answer 2

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

Это верно, даже если вы не заключаете вставки в явный begin / commit, Фиксация транзакции, даже если она выполняется неявно, по-прежнему не обязательно выполняется в том же порядке, в котором была вставлена ​​строка, в которую она была вставлена. Это зависит от решений по планированию процессора операционной системы и т. Д.

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

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

Вы можете использовать консультативную блокировку, но эффект тот же.

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

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

Я думаю, вам следует отступить назад и посмотреть, почему у вас есть это требование и, учитывая это требование, почему вы используете отдельные одновременные вставки.

Может быть, вам лучше делать групповые вставки с небольшими блоками из одного сеанса?

Answer 3

Мы нашли другое решение с недавними серверами PostgreSQL, похожее на ответ @erwin, но с txid.

При вставке строк вместо использования последовательности вставьте txid_current() как идентификатор строки. Этот идентификатор монотонно увеличивается при каждой новой транзакции.

Затем при выборе строк из таблицы добавьте в предложение WHERE id < txid_snapshot_xmin(txid_current_snapshot()),

txid_snapshot_xmin(txid_current_snapshot()) соответствует индексу самой старой еще открытой транзакции. Таким образом, если строка 20 зафиксирована перед строкой 19, она будет отфильтрована, поскольку транзакция 19 все еще будет открыта. Когда транзакция 19 зафиксирована, обе строки 19 и 20 станут видимыми.

Если транзакция не открыта, снимок xmin будет идентификатором текущей запущенной транзакции. SELECT заявление.

Возвращенные идентификаторы транзакций являются 64-битными, старшие 32 бита являются эпохой, а младшие 32 бита являются фактическим идентификатором.

Вот документация этих функций: https://www.postgresql.org/docs/9.6/static/functions-info.html

Кредиты на canderso за идею.

Answer 4

Если вы имеете в виду, что каждый запрос, если он видит world грести он должен также видеть hello тогда вам нужно будет выполнить:

begin;
lock table table1 in share update exclusive mode;
insert into table1(id, value) values (nextval('table1_seq'), 'hello');
commit;

это share update exclusive mode это самый слабый режим блокировки, который является самоисключающим - только один сеанс может удерживать его одновременно.

Имейте в виду, что это не сделает эту последовательность без пропусков - это другая проблема.