Как я могу иметь порядковый ключ в Postgres и иметь возможность автоматически обновляться при вставке новой строки в середине?

Допустим, у меня есть простая таблица в Postgres 9.5:

CREATE TABLE "public"."outcomes" (
    "id" serial,
    "ordinal" int NOT NULL,
    "outcome" varchar,
    PRIMARY KEY ("id")
);

Мне нужно поддерживать произвольный порядок, определенный ordinal. Мне нужно иметь возможность вставить строку между другими строками, как определено их порядковым номером.

У меня есть некоторые основные данные в таблице:

INSERT INTO "public"."outcomes"("ordinal", "outcome") VALUES('1', 'foo');
INSERT INTO "public"."outcomes"("ordinal", "outcome") VALUES('2', 'bar');
INSERT INTO "public"."outcomes"("ordinal", "outcome") VALUES('3', 'baz');

Я хочу иметь возможность запускать это:

INSERT INTO "public"."outcomes"("ordinal", "outcome") VALUES('2', 'glorb');

и иметь строки «bar» и «baz» автоматически увеличивающие свой порядковый номер.

Я дошел до добавления отложенного уникального ограничения:

ALTER TABLE outcomes
ADD CONSTRAINT outcomes_ordinal_key
UNIQUE (ordinal) DEFERRABLE INITIALLY IMMEDIATE

и попытался вставить таким образом:

INSERT INTO outcomes("ordinal", "outcome")
VALUES('2', 'glorb')
ON CONFLICT (ordinal)
DO UPDATE SET "ordinal" = "outcomes"."ordinal" + 1 WHERE "outcomes"."ordinal" >= EXCLUDED.ordinal;

но это дает эту ошибку:

ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters

а если ограничение не является отложенным, оно немедленно терпит неудачу, потому что при попытке обновить порядковый номер bars с 2 на 3 возникает конфликт с порядковым номером baz.

Как я могу сделать это с помощью Postgres?


postgresql ordinal
person micahbf    schedule 30.05.2016    source источник
comment
При этом вы сражаетесь с таблицами кучи и БД - порядок почти никогда не имеет значения для РСУБД. Стандартный ответ — использовать оконную функцию, например row_number(), и определить свой заказ, используя другие данные. Однако требование произвольности порядка останавливает вас от использования этого подхода. TBH, я бы пересмотрел ваши требования - расскажите нам о вашей реальной проблеме, а не о том, где вы падаете с предложенным вами решением.   -  person Ben    schedule 30.05.2016
comment
@Ben Проблема в том, что я отслеживаю серию матчей между игроками. Иногда кто-то может забыть сразу забить матч и вернуться и сделать это позже, после того, как были сыграны другие матчи. Порядок матчей важен, потому что (отдельный) алгоритм подсчета очков для игроков зависит от рейтинга игроков на момент проведения матча (т. е. по состоянию на предыдущий матч).   -  person micahbf    schedule 30.05.2016
comment
Разве вы не можете просто разрешить пользователю изменять ввод даты и времени в этом случае? Предотвращение возможности злоупотреблений — это проблема UX, и вы можете использовать дату и время для заказа. Не похоже, что у вас, вероятно, будет много параллелизма, что хорошо, но последующее обновление всех этих строк всегда будет адским, если ваше приложение любого размера, если вы пойдете по текущему маршруту.   -  person Ben    schedule 30.05.2016
comment
Да, проблема еще больше усугубляется историческими данными, которые не имеют даты и времени, и в этом случае их просто нужно упорядочить по первичному ключу. Таким образом, оконная функция была немного громоздкой, и я надеялся, что явный порядок может быть проще. Но я согласен, что это, вероятно, не так.   -  person micahbf    schedule 30.05.2016
comment
Я думаю, что с этим легко справиться... row_number() over (order by <datetime_column> desc nulls last, <pk> desc) - вы можете указать два порядка, и, поскольку вы используете ПК только тогда, когда нет даты, это должно работать - вы также получаете разрешение на конфликт в случае, когда два пользователя вставляют строки точно в в то же время.   -  person Ben    schedule 30.05.2016
comment
Я обычно решаю это, сохраняя эти порядковые номера с шагом. например 10,20,30,40 и так далее (старый добрый БЕЙСИК-трюк с льняными цифрами из 80-х). тогда вы всегда можете вставить что-то между ними. И довольно легко перенумеровать строки в одном операторе обновления, когда промежутки становятся небольшими.   -  person a_horse_with_no_name    schedule 30.05.2016
comment
@a_horse_with_no_name Я думаю, что в случае с OP он мог бы даже использовать серийный номер second с приращением (stepsize) = 10 (и начиная с правильной позиции: текущий максимум (порядковый номер) * 10) /uglyhack   -  person wildplasser    schedule 31.05.2016
comment
stackoverflow.com/a/14092775/905902 предыдущий ответ на очень похожий вопрос.   -  person wildplasser    schedule 31.05.2016
comment
Вы можете использовать десятичные числа вместо целых чисел. Значением по умолчанию по-прежнему является nextval последовательности, но для вставки между A и B порядковое значение для новой строки равно (A+B)/2.0.   -  person Daniel Vérité    schedule 31.05.2016

Ответы (3)


arrow_upward
0
arrow_downward

Вам нужно создать триггер INSERT/DELETE

Вставить наверное так

UPDATE outcomes
SET ordinal = ordinal + 1
WHERE ordinal > NEW.ordinal
person Juan Carlos Oropeza    schedule 30.05.2016
comment
По сути, это правильный ответ, однако производительность приложения любого размера будет ужасающей. Вы также будете обновлять таблицу, в которую вы вставляете, что обычно вызывает проблемы с ACID (не могу вспомнить, как Postgres справляется с этим). - person Ben; 30.05.2016
comment
@Ben Возможно, ты прав, я просто пытаюсь помочь решить вопрос ОП. - person Juan Carlos Oropeza; 30.05.2016

arrow_upward
0
arrow_downward

Типы Enum на помощь:

CREATE TYPE public.ordinal_enum AS ENUM ('foo', 'bar', 'baz');

CREATE VIEW public.ordinal AS
SELECT row_number() OVER (ORDER BY oid) AS id,
       row_number() OVER (ORDER BY enumsortorder) AS ordinal,
       enumlabel::text AS outcome
FROM pg_enum 
WHERE enumtypid = 'public.ordinal_enum'::regtype;

-- SELECT * FROM public.ordinal ORDER BY 1;

ALTER TYPE public.ordinal_enum ADD VALUE IF NOT EXISTS 'glorb' BEFORE 'bar';

SELECT * FROM public.ordinal ORDER BY 1;

Легкий.

person Ezequiel Tolnay    schedule 31.05.2016

arrow_upward
0
arrow_downward

Основываясь на комментарии @Daniel, чтобы вставить средний порядковый номер:

insert into outcomes (ordinal, outcome)
select (2 + max(ordinal)) / 2, 'glorb'
from outcomes
where ordinal < 2
;
select * from outcomes order by ordinal;
 id | ordinal | outcome 
----+---------+---------
  1 |       1 | foo
  4 |     1.5 | glorb
  2 |       2 | bar
  3 |       3 | baz

insert into outcomes (ordinal, outcome)
select (2 + max(ordinal)) / 2, 'xorb'
from outcomes
where ordinal < 2
;
select * from outcomes order by ordinal;
 id | ordinal | outcome 
----+---------+---------
  1 |       1 | foo
  4 |     1.5 | glorb
  5 |    1.75 | xorb
  2 |       2 | bar
  3 |       3 | baz

Данные:

create table outcomes (
    id serial primary key,
    ordinal real not null,
    outcome varchar,
    unique (ordinal)
);
insert into outcomes (ordinal, outcome) values
    (1, 'foo'),(2, 'bar'),(3, 'baz');
person Clodoaldo Neto    schedule 31.05.2016