adding NOT NULL columns safely (with easy optional backfill proposal)

[jozef-slezak]

Just an idea:

• pgplex could detect PG18 while calculating plan
• instead ALTER TABLE xy ADD COLUMN col text NOT NULL there could be something like (for PG18 and little bit different and not that nice analogical expression for PG17):

ALTER TABLE xy ADD COLUMN col text; -- without not null
ALTER TABLE xy
ADD CONSTRAINT xy_col_not_null
NOT NULL col
NOT VALID;

• and eventually run ALTER TABLE xy VALIDATE CONSTRAINT xy_col_not_null; (manually or maybe the planner could later detect that the constraint is VALID now and remind on next run).

I understand that I can add new nullable columns and I understand that you are supporting PG17.
I really like the declarative part and missing backfills. Pgroll supports the backfills but I do not like that I can not really review the executed SQL.

Another idea is that backfills could be prepared and maintained via db triggers (if somebody not prefers on app level):

• I could maintains INSERT/UPDATE triggers that would do backfills for some scenarios (backfill DML would be inspired by pgroll sql)
• I could prepare backfills for new columns via ddl trigger that starts backfill (backfill DML would be inspired by pgroll sql):

CREATE OR REPLACE FUNCTION after_ddl()
RETURNS event_trigger
LANGUAGE plpgsql
AS $$
BEGIN
    -- inspect DDL command
    -- SELECT * FROM pg_event_trigger_ddl_commands();
   -- if new column xy.col added then specifix DML statement for backfill
END;
$$;

CREATE EVENT TRIGGER after_ddl ON ddl_command_end
EXECUTE FUNCTION after_ddl();