Блог

Главная - Вопросы по программированию - Вставка результатов в непоследовательные идентификаторы

Вставка результатов в непоследовательные идентификаторы

#postgresql

#postgresql

Вопрос:

У меня есть следующий запрос:

 INSERT INTO hosts (name, domain, ip)
SELECT name, domain, ip
FROM staging_hosts
ON CONFLICT (ip) DO UPDATE
SET name = excluded.name, domain = excluded.domain;

Это работает нормально, если не считать того факта, что идентификаторы в моей таблице «hosts» не увеличиваются последовательно. Например, я получу следующие новые идентификаторы после выполнения этого запроса:

 114855
114859
114873
114977
117389
115326

Столбец ID в таблице hosts является последовательным, поэтому я не уверен, почему идентификаторы последовательно увеличиваются.

Комментарии:

1. Поскольку serial sequence не гарантируется, что a будет меньше пробелов. Последовательность увеличивается каждый раз, когда на нее ссылаются в команде, независимо от того, используется она или нет. ON CONFLICT «Записывает» число каждый раз, когда оно выполняет UPDATE вместо an INSERT , поэтому вы получаете пробел. Далее каждый сеанс получает свой собственный кэш чисел, поэтому, если у вас параллельные сеансы, они будут использовать start в другом месте. Для получения дополнительной информации см. Последовательность, в частности раздел Примечания.

2. О, хорошо, это имеет смысл — есть ли способ выполнить эту операцию «upsert», чтобы минимизировать пробелы / и / или полностью избежать пробелов?

3. Почему вас волнует «пробел»? Это просто числа для идентификации строки, и все. Для любой другой функциональности этих чисел вам лучше избегать использования последовательности.

4. Вам не нужно беспокоиться о пробелах. Все просто. Просто двигайтесь дальше, здесь нет ничего, что вам нужно для «оптимизатора».

5. Если вы используете serial , вы получите пробелы, которые являются частью пакета и являются побочным продуктом его производительности под нагрузкой. Если существует какое-либо установленное законом и / или бизнес-правило, которое не требует пробелов, тогда вам понадобится какой-то другой процесс. Поиск по «последовательности postgresql без пробелов». Просто знайте, что решения зависят от блокировки и работают заметно медленнее и / или могут потребовать повторных попыток.

Ответ №1:

Если вы настаиваете на минимизации пробелов, вы можете сделать это, включив примеры таблиц:

 CREATE TABLE foo (
  id SERIAL PRIMARY KEY,
  ip INT,
  name TEXT,
  UNIQUE(ip) );

CREATE TABLE staging (
  ip INT,
  name TEXT );

INSERT INTO foo (ip,name) VALUES (1,'hello'),(2,'abc'),(10,'world');
INSERT INTO staging VALUES (1,'byebye'),(2,'def'),(11,'world');

WITH s AS ( 
    SELECT COALESCE( foo.id, nextval('foo_id_seq'::regclass)) AS id, 
    staging.ip,staging.name
    FROM staging LEFT JOIN foo USING (ip))
INSERT INTO foo (id,ip,name) SELECT * FROM s
ON CONFLICT(id) DO UPDATE 
SET ip=excluded.ip, name=excluded.name;

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

Метки: