From: | Raj Gandhi <raj01gandhi(at)gmail(dot)com> |
---|---|
To: | David Rowley <dgrowleyml(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Postgres 12 - default value for text column |
Date: | 2020-08-04 21:50:27 |
Message-ID: | CALU_HCOT7dti-pdRYXHS3fLMqf9HGHedosevBZnZ+1O76bf-Sg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Great, thanks David for the explanation.
On Tue, Aug 4, 2020 at 4:59 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> On Wed, 5 Aug 2020 at 08:36, Raj Gandhi <raj01gandhi(at)gmail(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | fight.mufasa | 2020-08-05 03:25:13 | Re:ERROR: XX000: cannot update SecondarySnapshot during a parallel operation |
Previous Message | David Rowley | 2020-08-04 20:59:13 | Re: Postgres 12 - default value for text column |