current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 0 down vote favorite

I would like to add a check constraint to a very large table. Something like:

ALTER TABLE accounts ADD CONSTRAINT "nonnegative_balance" CHECK balance >= 0;

Unfortunately Postgres (9.3, at least) blocks reads or writes until the constraint check has been completed. I verified this by starting a transaction, running the ALTER TABLE, then opening a second transaction and checking that I couldn't read or write from the table until the first transaction completed.

Is there any way I can add this CHECK constraint without locking the table?

share|improve this question
    
This is not specific to the check constraint. All (or nearly all) DDL statements require an exclusive lock on the table - this is also nothing special to Postgres most DBMS work that way for DDL statements. – a_horse_with_no_name Dec 4 '15 at 18:42
up vote 2 down vote accepted

You can create a NOT VALID CHECK constraint, which will enforce the constraint going forward, but will not check the entire table for validation upon creation. At some later date, you can attempt to VALIDATE the constraint (when a lock on the table is ok)

Please review the documentation - Quote below:

ADD table_constraint [ NOT VALID ]

This form adds a new constraint to a table using the same syntax as CREATE TABLE, plus the option NOT VALID, which is currently only allowed for foreign key and CHECK constraints. If the constraint is marked NOT VALID, the potentially-lengthy initial check to verify that all rows in the table satisfy the constraint is skipped. The constraint will still be enforced against subsequent inserts or updates (that is, they'll fail unless there is a matching row in the referenced table, in the case of foreign keys; and they'll fail unless the new row matches the specified check constraints). But the database will not assume that the constraint holds for all rows in the table, until it is validated by using the VALIDATE CONSTRAINT option.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.