Блог

В PostgreSQL 13 у меня есть таблица, определяемая как таковая :

 create table my_table (
   id serial8 primary key,
   title varchar(50),
   description varchar(512),
   french_vector_text tsvector generated always as (to_tsvector('french', coalesce(title, '') || ' ' || coalesce(description, ''))) stored
);

create index IDX_FULL_TEXT on my_table using GIN(french_vector_text);
 

Это прекрасно работает. Однако, когда я пытаюсь изменить столбец, используемый для создания вектора, вот так

 alter table my_table
alter column title type varchar(100);
 

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

 alter table my_table
drop column french_vector_text;

alter table my_table
alter column title
type varchar(100);

alter table my_table
add column french_vector_text tsvector generated always as (to_tsvector('french', coalesce(title, '') || ' ' || coalesce(description, ''))) stored;

create index IDX_FULL_TEXT on my_table using GIN(french_vector_text);
 

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

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

Я думал о триггере, но предпочел бы сохранить сгенерированный столбец. Поэтому я попытался создать IMMUTABLE функцию как таковую :

 create function my_french_vector(text_to_transform varchar) returns tsvector as $ 
BEGIN   
    return to_tsvector('french', text_to_transform);
end 
$ LANGUAGE plpgsql
immutable;
 

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

Что касается моего вопроса, есть ли способ изменить столбец, используемый в сгенерированном значении, без необходимости удалять его и воссоздавать все это после?

Я бы вообще не использовал сгенерированный столбец, а определил индекс в выражении:

 CREATE INDEX french_text_idx ON my_table
   (to_tsvector('french', coalesce(title, '') || ' ' || coalesce(description, '')));
 

Затем измените запрос на

 ... WHERE to_tsvector('french', coalesce(title, '') || ' ' || coalesce(description, ''))
             @@ to_tsquery('french', 'whatever')
 

и он будет использовать индекс.

При добавлении нового столбца создайте новый индекс ( CONCURRENTLY ), включающий новый столбец, затем измените запрос в приложении, а затем удалите старый индекс.

Если вы не хотите изменять запрос в приложении, определите представление в таблице, содержащее вычисляемый столбец с индексированным выражением, тогда все, что вам нужно сделать, — CREATE OR REPLACE это представление.