Great, thanks David for the explanation. On Tue, Aug 4, 2020 at 4:59 PM David Rowley <dgrowle...@gmail.com> wrote:
> On Wed, 5 Aug 2020 at 08:36, Raj Gandhi <raj01gan...@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. > > David >
