Конфликт последовательности ключа и правила ON INSERT

Имеется таблица с автонумерацией первичного ключа:

CREATE SEQUENCE sq_test_id
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 65535
  START 1
  CACHE 1;
 
CREATE TABLE test (
  id integer NOT NULL DEFAULT NEXTVAL('sq_test_id'::regclass),
  "name" character varying(60),
  CONSTRAINT test_id PRIMARY KEY (id)
)

Во избежание ошибок дублирования ключа id при вставке задано правило:
CREATE OR REPLACE RULE test_insert AS
  ON INSERT TO test
  WHERE (EXISTS ( SELECT id
           FROM test
          WHERE id = new.id))
  DO INSTEAD
    UPDATE test
    SET name = new.name
    WHERE id = new.id;

(если запись с заданным id существует, INSERT заменяется на UPDATE)

Добавляем записи:
INSERT INTO test (name) VALUES('item1');
INSERT INTO test (name) VALUES('item2');
INSERT INTO test (name) VALUES('item3');
INSERT INTO test (name) VALUES('item4');
...
Получаем: (SELECT * FROM test)

 id    | name |
-------+-------+
     1 | item1 | 
     4 | item2 | 
     9 | item3 | 
    16 | item4 | 
    25 | item5 | 

Хотелось бы в id получить последовательную нумерацию, а не квадратичную.

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

5.3.3. Ограничения

5.3.3. Ограничения уникальности
5.3.4. Первичные ключи
http://postgresql.ru.net/manual/ddl-constraints.html

Речь идет не об ограничениях

Речь идет не об ограничениях и проверках ключа, а о его автонумерации.
На ключ повешена последовательность, без правила (если его отключить) она работает нормально.
Так же и правило само по себе работает: при вставке новых ключей строки добавляются, при вставке повторно - обновляется существующая строка.
Но при этом значение последовательности скачет квадратично. Дело, видимо, в условии правила - "SELECT id FROM test" образует вложенные циклы вызова nextval(). Независимо, была ли вставлена новая запись или нет (напр. INSERT INTO test VALUES(1, 'new_name'); ) - значение последовательности "убегает".

Может есть какие идеи как проверить дублирование ключей без SELECT'а?

1. >Во избежание ошибок

1.
>Во избежание ошибок дублирования ключа id
ну тогда как-то не вразумительное.
2. Автонумерация обычно создается конструкцией:
CREATE SEQUENCE Seq minvalue 1;
Create table t (id int primary key default naxtval('Seq'));

Никто вам не гарантирует

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

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

При использовании

При использовании последовательного первичного ключа есть очень не приятные моменты
1. в многопользовательском режиме работы - могут возникнуть повторения.
2. в случае если будет необходимость удаления записей внутри диапазона, прийдется перебирать весь диапазон.
А т.к. первичный ключ призван создавать уникальность то все манипуляции с последовательностью строго не рекомендуются, кроме как получение следующего значения.
И потом, достаточно сложно представить себе задачу, в которой требуется использовать строго последовательный суррогатный ключ. Если данная необходимость связана с отображением информации пользователю, то может подойти запрос вида:
SELECT (SELECT count(t1.id) FROM tbl t1 WHERE t1.id<t.id)+1 ordrnum, t.id  FROM tbl t ORDER BY t.id LIMIT 1000
Но лучше конечно оформить ввиде процедуры с выводом значения счетчика
ocntr и oid выходные переменные процедуры

  ocntr=0;
  FOR oid IN 
             SELECT  t.id  FROM tbl t loop
 ocntr=ocntr+1;
 RETURN next;
 end loop;

Опции просмотра комментариев

Выберите предпочитаемый вами способ показа комментариев и нажмите "Сохранить настройки" для активации изменений.

Back to top

(С) Виктор Вислобоков, 2008-2023