add-column-with-domain-constraints.sql

-- This is showing how hard it is to do "live migrations" when you use domain constraints.
-- The ALTER TABLE below looks pretty harmless but, depending on how big your table is, can
-- cause downtime as Postgres rechecks the whole table.

SET statement_timeout TO 0;

DROP TABLE IF EXISTS users;
DROP EVENT TRIGGER IF EXISTS warn_on_table_rewrite;
DROP DOMAIN IF EXISTS non_empty_text_299;

SET statement_timeout TO 1000; -- stop at 1 second

-- Warn us if we accidentally make Postgres recheck the whole table
CREATE OR REPLACE FUNCTION warn_on_table_rewrite()
RETURNS event_trigger AS $$
BEGIN
  RAISE NOTICE 'Rewrite triggered on table %', pg_event_trigger_table_rewrite_oid()::regclass;

  -- Or we could just error:
  -- RAISE EXCEPTION 'Rewrite triggered on table %', pg_event_trigger_table_rewrite_oid()::regclass;
END;
$$ LANGUAGE 'plpgsql';

CREATE EVENT TRIGGER warn_on_table_rewrite ON table_rewrite EXECUTE FUNCTION warn_on_table_rewrite();

-- We constrain some fields with length constaints.
CREATE DOMAIN non_empty_text_299 AS text
 CONSTRAINT length_constraint CHECK ( LENGTH(VALUE) BETWEEN 1 AND 299 );

-- Schema V1
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  first_name non_empty_text_299 NOT NULL,
  last_name non_empty_text_299 NOT NULL
);

-- Insert a bunch of rows
-- Split up into multiple statements so we don’t trigger statement_timeout above
INSERT INTO users (first_name, last_name)
  SELECT 'First Name' || g.id, 'Last Name ' || g.id
  FROM generate_series(1, 500000) AS g (id) ;
INSERT INTO users (first_name, last_name)
  SELECT 'First Name' || g.id, 'Last Name ' || g.id
  FROM generate_series(1, 500000) AS g (id) ;
INSERT INTO users (first_name, last_name)
  SELECT 'First Name' || g.id, 'Last Name ' || g.id
  FROM generate_series(1, 500000) AS g (id) ;
INSERT INTO users (first_name, last_name)
  SELECT 'First Name' || g.id, 'Last Name ' || g.id
  FROM generate_series(1, 500000) AS g (id) ;
INSERT INTO users (first_name, last_name)
  SELECT 'First Name' || g.id, 'Last Name ' || g.id
  FROM generate_series(1, 500000) AS g (id) ;
INSERT INTO users (first_name, last_name)
  SELECT 'First Name' || g.id, 'Last Name ' || g.id
  FROM generate_series(1, 500000) AS g (id) ;
INSERT INTO users (first_name, last_name)
  SELECT 'First Name' || g.id, 'Last Name ' || g.id
  FROM generate_series(1, 500000) AS g (id) ;
INSERT INTO users (first_name, last_name)
  SELECT 'First Name' || g.id, 'Last Name ' || g.id
  FROM generate_series(1, 500000) AS g (id) ;

-- Schema V2
-- I want to add a migration for a user with a nullable middle name.
-- How to do it without having to recheck all the rows above?
-- As is, it will end up taking longer than 1 second.
-- TEXT works fines, but our custom constraint doesn’t.

-- This fails:
ALTER TABLE users ADD COLUMN middle_name non_empty_text_299 NULL;

-- The problem is there’s no "NOT VALID" for a domain constraint. An
-- idea is to just remove the constraint, alter table, then re-add the
-- constraint within a transaction.

-- It’s not very well documented that Postgres would behave like this.
-- The source code has a short mention here:
-- https://github.com/postgres/postgres/blob/3db72ebcbe20debc6552500ee9ccb4b2007f12f8/src/backend/commands/tablecmds.c#L6920-L6927

-- But docs don’t really mention it. Someone wrote a patch that might help a little bit:
-- https://commitfest.postgresql.org/38/3604/review/

-- This works:
-- SET statement_timeout TO 10000; ALTER DOMAIN non_empty_text_299 DROP CONSTRAINT length_constraint; SET statement_timeout TO 10000;
-- ALTER TABLE users ADD COLUMN middle_name non_empty_text_299 NULL;
-- ALTER DOMAIN non_empty_text_299 ADD CONSTRAINT length_constraint CHECK ( LENGTH(VALUE) BETWEEN 1 AND 299 ) NOT VALID;

-- This takes a while, but does not lock the table
-- SET statement_timeout TO 10000; ALTER DOMAIN non_empty_text_299 VALIDATE CONSTRAINT length_constraint;