PostgreSQL - Alter Column used for a Fulltext Generated Column

Question

On PostgreSQL 13, I have a table define as such :

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);

It works fine. However, when I try to alter a column used to generate the vector, like this

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

I get an error telling me I can't alter a column used to calculate another, which is fair. I made it work by dropping the vector column with the index, altering the title column, and then recreating the column and index.

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);

Once again it worked fine, but in reality, the column is the aggregation of 7 columns, some with fairly large text, and the table may contain a couple ten thousand of records. So the operation took some time to complete.

Even if altering the schema isn't an everyday operation, I'm wondering if there is a more efficient solution that doesn't require dropping the column and fully recreates the index.

I thought about a trigger, but i would rather keep the generated column. So i tried creating an IMMUTABLE function as such :

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

And use it instead of the plain to_tsvector. But obviously, it doesn't work either as the column is still referenced.

As for my question, is there a way to alter a column used in a generated value, without having to drop it and recreate the whole thing after?

user_6464308 · Answer

I would not use a generated column at all, but define the index on an expression:

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

Then modify the query to

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

and it will use the index.

When a new column is added, create a new index (CONCURRENTLY) that includes the new column, then change the query in your application, then drop the old index.

If you don't want to change the query in the application, define a view on the table that contains a calculated column with the indexed expression, then all you have to do is CREATE OR REPLACE the view.

PostgreSQL - Alter Column used for a Fulltext Generated Column

1 Answers