0

In postgres 9.1, will this query require a table lock? (inhibit other clients from writing, or writing and reading)

ALTER TABLE "foos" ADD COLUMN "bar" boolean DEFAULT 'f'

Are there some cases where it will, some where it won't?

Does the default value make it slower?

If it does lock the table, does it also have to operate on every row in the table? Or will the lock only be held for a split second?

Where can I learn more about what types of queries do and do not lock the table?

John Bachir
  • 22,495
  • 29
  • 154
  • 227

1 Answers1

1

Yes it will lock the table while it makes the modification.

  • If the table is large with many rows of data, this may take quite awhile.
  • If another connection has a lock of higher priority on the table currently, this action will wait for the lock to be cleared

To know more about PostgreSQL 9.1, check the excellent online documentation particularly the section on ALTER TABLE

Also, here is a related question, though for MySQL. It offers some tips and tricks that could be applied to PostgreSQL

Community
  • 1
  • 1
Joshua Berry
  • 2,230
  • 3
  • 21
  • 24
  • Locking in PostgreSQL and MySQL are very very different, you can't compare these two databases in locking behavior. What takes a split second in PostgreSQL, takes hours in MySQL... That's why we left MySQL behind. – Frank Heikens Dec 05 '12 at 17:58
  • @FrankHeikens can you elaborate more in an answer? i added a few more specific questions to my question above. – John Bachir Dec 05 '12 at 18:07
  • @FrankHeikens regardless of how different the locking mechanisms may be between mysql and PG, locking still comes into play given the op's question. – Joshua Berry Dec 05 '12 at 21:55