On Wed, 5 Aug 2020 at 08:36, Raj Gandhi <raj01gandhi@gmail.com> wrote: > The following alter table with default set to very large text used to work in Postgres 10 but fails in Postgres 12 with ERROR: row is too big: size 12960, maximum size 8160
I didn't go to the trouble of debugging this, but I imagine this is due to "Allow ALTER TABLE to add a column with a non-null default without doing a table rewrite" mentioned in https://www.postgresql.org/docs/11/release-11.html
In PG10 the table would have been rewritten when you add a NOT NULL column with a DEFAULT. From PG11 onwards no rewrite takes place and the default value is stored in pg_attribute. Since pg_attribute does not have a TOAST table, it's not possible to add NOT NULL columns which have default values that won't fit in a heap page.
> The following two variants works in Postgres 12 without any error: > > create table test (id int); > alter table test1 add column license text > alter table test1 alter column license SET DEFAULT '<insert default text with size more than 8160 >'
This behaves differently since existing rows won't receive the DEFAULT value. Only new rows will. PostgreSQL12 does not need to store the missing value in pg_attribute when you do this. Existing rows will just have a NULL value for the new column.
> create table test (id int, license text DEFAULT '<insert default text with size more than 8160 >' );
Likewise. No missing value needs to be stored here as no rows exist that need that value. Rows that are added with no value specified for the license column will just have the DEFAULT value, which is the one stored in pg_attrdef.
