Skip to content

Instantly share code, notes, and snippets.

@jjb

jjb/Safely setting NOT NULL on a column in Postgres 12+.md

Last active April 29, 2024 11:49
Show Gist options
  • Star 15 You must be signed in to star a gist
  • Fork 3 You must be signed in to fork a gist
  • Save jjb/fab5cc5f0e1b23af28694db4fc01c55a to your computer and use it in GitHub Desktop.
Save jjb/fab5cc5f0e1b23af28694db4fc01c55a to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Safely setting NOT NULL on a column in Postgres 12+.md

This recipe is a work in progress and has never been run as-is.

  • timeouts are in ms
  • lock timeout: in postgres, when a statement that wants a restrictive lock waits on another lock, other statements that want locks can't jump the queue. so even though the statement that is waiting might only take a very short amount of time, when it starts running, while it is waiting no other statements can begin. So we set the lock timeout pretty low and retry if we don't get it.
  • statement timeout: we set a short statement timeout before statements which do lock and which we expect to take a short amount of time, just in case something about our assumptions/understanding is wrong and the statement ends up taking a long time. if this happens the statement will bail early without causing harm, and we can investigate what is wrong with our assumptions.
  • deadlock timeout: for two reasons
    • main reason: from pg docs: "When log_lock_waits is set, this parameter also determines the length of time to wait before a log message is issued about the lock wait. If you are trying to investigate locking delays you might want to set a shorter than normal deadlock_timeout"
    • additional benefit: just in case there's an actual deadlock, we want to check and bail as soon as possible (very unlikely for that to be the case for these types of statements though)
SET client_min_messages=debug;
SET log_lock_waits=on;

SET deadlock_timeout='1';
SET lock_timeout='1000';

SET statement_timeout='5000';

-- locks, should be very fast
ALTER TABLE my_table
  ADD CONSTRAINT my_table_column1_not_null CHECK (column1 IS NOT NULL) NOT VALID;

SET statement_timeout='0'; -- no statement timeout

-- doesn't lock, incurs table scan
ALTER TABLE my_table validate CONSTRAINT my_table_column1_not_null;

SET statement_timeout='5000';

-- locks, should be very fast because of new feature in PG 12, see links below
ALTER TABLE my_table ALTER COLUMN column1 SET NOT NULL;

-- locks, should be very fast
ALTER TABLE my_table DROP CONSTRAINT my_table_column1_not_null;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment