Will adding new generated column lock table in PostgreSQL 12

Question

It is not clear from PostgreSQL documentation: will adding new generated column lock table?

I have big table in my PostgreSQL 12 installation and I need to execute SQL-patch:

ALTER TABLE abc ADD COLUMN gen TEXT GENERATED AS ALWAYS (...) STORED;

So, in my case it is unacceptable to lock whole table for long time. Is it safe to add new generated column?

score 2 · Accepted Answer · answered Sep 08 '20 at 15:28

2

Since the generated column is STORED, PostgreSQL has to rewrite the table, so yes, this will take a long time.

Why don't you use a view to calculate the column on SELECT?

answered Sep 08 '20 at 15:28

Laurenz Albe

In my case I have a lot of READ queries and generating this column on the fly is not applicable (if using non-materialized views). I don't want use materialized views because of it's update complexity. Thx, I checked my table definition after adding generated column and made sure that Postgres adds default value for this column. Since this default value is not a literal so Postgres has to rewrite whole table – Dmitry Komarov Sep 11 '20 at 12:01

1 Answers1