Re: Tip box on Adding a Column

On Fri, Nov 1, 2024 at 5:06 PM Marcelo Fernandes <marcefern7(at)gmail(dot)com> wrote:
>
> Hi folks,
>
> We have this Tip box under the "Adding a Column" header here:
>
> - https://www.postgresql.org/docs/current/ddl-alter.html#DDL-ALTER-ADDING-A-COLUMN
>
> That says:
>
> > From PostgreSQL 11, adding a column with a constant default value no longer
> > means that each row of the table needs to be updated when the ALTER TABLE
> > statement is executed. Instead, the default value will be returned the next
> > time the row is accessed, and applied when the table is rewritten, making the
> > ALTER TABLE very fast even on large tables.
>
> I'm just seeking clarification if this advice is true **even for** new columns
> declared with NOT NULL?
>
you can use event_trigger to test it.

https://www.postgresql.org/docs/current/event-trigger-definition.html

The table_rewrite event occurs just before a table is rewritten by
some actions of the commands ALTER TABLE and ALTER TYPE. While other
control statements are available to rewrite a table, like
CLUSTER and VACUUM, the table_rewrite event is not triggered by them.

---
following tests copy from
https://github.com/postgres/postgres/blob/d893a299ce68f56522073a1b43d65764a552ae0d/src/test/regress/sql/fast_default.sql#L47

CREATE OR REPLACE FUNCTION log_rewrite() RETURNS event_trigger
LANGUAGE plpgsql as
$func$
declare
this_schema text;
begin
RAISE NOTICE 'rewriting table % for reason %',
pg_event_trigger_table_rewrite_oid()::regclass,
pg_event_trigger_table_rewrite_reason();
end;
$func$;
CREATE EVENT TRIGGER has_rewrite ON table_rewrite EXECUTE PROCEDURE
log_rewrite();

create table t1(a int);
insert into t1 select 1;
alter table t1 add column b double precision not null default 11;
alter table t1 add column c double precision not null default random();
alter table t1 add column d timestamptz not null default now();
alter table t1 add column e timestamptz not null default current_timestamp;
alter table t1 ADD column f int DEFAULT (random() * 10000)::int;
alter table t1 ADD column g int not null DEFAULT (random() * 10000)::int;