CODEWITHSUNDEEP
postgresql
kabanek
kabanek

Reputation: 323

Will making column nullable lock the table for reads?

I want to make one field in my database nullable.

ALTER TABLE answers ALTER COLUMN author_id DROP NOT NULL;

The problem is that I have a lot of data in the database and I'm not sure if the alter command will block the table for write operations.

I know that changing the size or type of the column will cause the row exclusive lock https://www.postgresql.org/docs/current/static/explicit-locking.html#LOCKING-TABLES.

Will the alter I lock the table for writes too or it's safe to use?

Upvotes: 3

Views: 659

Answers (1)

user330315
user330315

Reputation:

Yes, the ALTER will require an exclusive lock on the table (including blocking write access to the table)

However, the duration of the lock is extremely short and does not depend on the size of the table. It is essentially only an update the internal system tables.

Upvotes: 2

Related Questions