Thread: GENERATED STORED columns and table rewrites?
Hello,
Does adding a GENERATED STORED column need to rewrite the table (like adding a column with a DEFAULT before 11)? Neither the ALTER TABLE docs [1] nor the generated column docs [2] discuss this. The former has a very nice Tip regarding DEFAULT columns--maybe we should clarify GENERATED STORED behavior either way? It seems like a very similar issue. Experimenting with this, \timing suggests that there is work proportional to the size of the table, but I'd like to understand the behavior better (and other users probably would, too).
Does adding a GENERATED STORED column need to rewrite the table (like adding a column with a DEFAULT before 11)? Neither the ALTER TABLE docs [1] nor the generated column docs [2] discuss this. The former has a very nice Tip regarding DEFAULT columns--maybe we should clarify GENERATED STORED behavior either way? It seems like a very similar issue. Experimenting with this, \timing suggests that there is work proportional to the size of the table, but I'd like to understand the behavior better (and other users probably would, too).
Thanks,
Yes, the system will do a full table rewrite to compute the value and store it. Unfortunately, I believe it is an access exclusive lock during that entire time.