There's more…

Indexes that depend on a dropped column are automatically dropped as well. All other objects that depend on the column(s), such as foreign keys from other tables, will cause the ALTER TABLE statement to be rejected. You can override this and drop everything in sight using the CASCADE option, as follows:

ALTER TABLE x 
DROP COLUMN  last_update_timestamp
CASCADE;

Adding a column with a non-null default value can be done with ALTER TABLE … ADD COLUMN … DEFAULT …, as shown above, which however holds an AccessExclusive lock for the whole duration of the command, which is not short as 100% of the rows must be rewritten.

The script introduced in the recipe "Using psql variables" in this chapter is an example of how to do the same without holding an AccessExclusive lock for a long time. This lighter solution has only another tiny difference: it doesn’t use a single transaction, which would be pointless since it would hold the lock until the end.

If any row is inserted by another session between the ALTER TABLE and the UPDATE, and that row has a NULL value for the new column, then that value will be updated together with all the rows that existed before the ALTER TABLE, which is OK in most cases, but not in all, depending on the data model of the application.

A proper solution would involve using two sessions, to ensure that no such writes can happen in between, with a procedure that can be sketched as follows:

  1. Open two sessions, and note their PIDs
  2. In session 1, BEGIN a transaction, then take an ACCESS EXCLUSIVE lock on the table, which will be granted
  3. Immediately after, but in session 2, BEGIN a transaction, then take a SHARE lock on the table, which will hang waiting for session 1
  4. In a third session, display the ordered wait queue for locks on session 1 as follows:
SELECT *
FROM pg_stat_activity
WHERE pg_blocking_pids(pid) @> array[pid1]
ORDER BY state_change;

(here pid1 is the PID of session 1) and check that PID2 is the second one in the list; if not, it means that step 3 was not fast enough, so ROLLBACK both sessions and repeat from step 1.

  1. In session 1, perform ALTER TABLE and then COMMIT
  2. In session 2 (which will be unblocked by the previous step, and will therefore acquire the SHARE lock straight away), perform the UPDATE and then the COMMIT