Сгенерированный столбец Postgres не является неизменным
Я добавляю несколько столбцов в таблицу и хочу создать сгенерированный столбец, объединяющий их вместе, который я буду использовать для уникального индекса. Когда я пытаюсь добавить столбец, я получаю сообщение об ошибке ERROR: generation expression is not immutable.
Я следовал решению из этого вопроса и специально использую CASE и || для конкатенации строк, которые должны быть immutable.
ALTER TABLE tag
ADD COLUMN prefix VARCHAR(4) NOT NULL,
ADD COLUMN middle BIGINT NOT NULL,
ADD COLUMN postfix VARCHAR(4), -- nullable
-- VARCHAR size is 4 prefix + 19 middle + 4 postfix + 2 delimiter
ADD COLUMN tag_id VARCHAR(29) NOT NULL GENERATED ALWAYS AS
(CASE WHEN postfix IS NULL THEN prefix || '-' || middle
ELSE prefix || '-' || middle || '-' || postfix
END
) STORED;
CREATE UNIQUE INDEX unq_tag_tag_id ON tag(tag_id);
В списке рассылки postgres один из участников уточняет, что:
преобразование целого числа в текст, [...] не обязательно неизменный
Однако он не использует неизменяемую функцию преобразования целых чисел в текст. Кто-нибудь знает, существует ли он?
Кажется, это работает с приведением к text, т.е. middle::text
Ответы 2
Тест в 14.1 показывает, что причиной является неявное преобразование столбца bigint в text при конкатенации (даже без case)
Явное приведение к text не приводит к ошибке - middle::text
ALTER TABLE tag
ADD COLUMN prefix VARCHAR(4) NOT NULL,
ADD COLUMN middle BIGINT NOT NULL,
ADD COLUMN postfix VARCHAR(4), -- nullable
-- VARCHAR size is 4 prefix + 19 middle + 4 postfix + 2 delimiter
ADD COLUMN tag_id VARCHAR(29) NOT NULL GENERATED ALWAYS AS
(CASE WHEN postfix IS NULL THEN prefix || '-' || middle::text
ELSE prefix || '-' || middle::text || '-' || postfix
END
) STORED;
Ответ Marmite Bomber показывает решение; позвольте мне добавить объяснение.
Есть два оператора конкатенации для text:
SELECT oid, oprname,
oprleft::regtype,
oprright::regtype,
oprcode
FROM pg_operator
WHERE oprname = '||'
AND oprleft = 'text'::regtype;
oid │ oprname │ oprleft │ oprright │ oprcode
══════╪═════════╪═════════╪═════════════╪════════════
654 │ || │ text │ text │ textcat
2779 │ || │ text │ anynonarray │ textanycat
(2 rows)
Первый оператор объединяет text с text, второй объединяет text с чем-либо еще.
Давайте рассмотрим волатильность этих двух функций:
SELECT oid, proname, provolatile
FROM pg_proc
WHERE pronamespace = 'pg_catalog'::regnamespace
AND proname IN ('textcat', 'textanycat');
oid │ proname │ provolatile
══════╪════════════╪═════════════
1258 │ textcat │ i
2003 │ textanycat │ s
(2 rows)
Таким образом, если вы объедините text и bigint, эта операция не будет IMMUTABLE, но приведение bigint к text сначала создаст операцию IMMUTABLE.
Этот оператор не является неизменным не из-за объединения с целыми числами. Но anynonarray может быть любым типом данных, например timestamp with time zone, строковое представление которого зависит от текущей настройки timezone.
Спасибо за внутренний взгляд на двигатель. Однако я до сих пор не могу концептуально понять, почему объединение INT может сделать его неизменяемым. Можете ли вы показать пример? Или... может быть, должен быть третий вариант оператора для варианта (text.int)?
@TheImpaler Я добавил пояснение к ответу.
Спасибо за замечание. ИМХО, я думаю, что второй случай должен быть разделен на два разных, так как BIGINT ведет себя совсем по-другому по сравнению с TIMESTAMP WITH TIME ZONE.
@TheImpaler Вы предлагаете, чтобы у нас была функция конкатенации для каждого типа данных (или, по крайней мере, для каждого предпочтительного типа данных)? Вы можете отправить патч...
Ты прав. Жаловаться легко; принятие мер, с другой стороны, требует гораздо больше усилий. Не могу сделать это прямо сейчас, но я хотел бы помочь с PostgreSQL в будущем. Спасибо за ругань ;)
Хороший вопрос. Я так и не понял, почему этот пример работает для VARCHAR, но не для INT.