9

This question already has an answer here:

Trying to add a NOT NULL constraint to a table with 1 billion rows. I cannot afford a table lock for more than a couple of seconds. Is there a way to prevent a full table scan during the alter table statement? I created an index on the column hoping it would be used but that doesn't seem to work. May be a check constraint? Other options? Thank you!

marked as duplicate by Evan Carroll postgresql Aug 29 '18 at 6:29

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • What on earth are you running that you cannot afford a table lock for more than a couple of seconds?The Universe software? – Mihai Jun 8 '14 at 18:24
  • 1
    :) collecting real time data but only for a very small part of the universe. – Volker Hauf Jun 9 '14 at 5:55
  • Just thoughts... That's weird it uses full table scan if you have an index. If it would be using the index, as index lookup for a non-existing value should be lighting fast... so the lock will not be a problem. – kan May 20 '15 at 21:23
7

Is there a way to prevent a full table scan during the alter table statement?

At this time there is no supported, safe way to do that with PostgreSQL.

Some kind of ALTER TABLE ... ADD CONSTRAINT ... CONCURRENTLY would be nice, but nobody's implemented it. Same with the alternative of adding a NOT VALID constraint that still affects new rows, and that you then VALIDATE later - it'd be good, and it's something everyone knows is needed but nobody's had the time or funding to add yet.

In theory you could directly modify the system catalogs to add the constraint if you know it is true and valid. In practice, well, it's generally not a great idea.

So no, there isn't really a way.

  • 1
    Thank you! In case somebody wants to implement: While adding constraints concurrently would be fantastic, just looking at indexes before scanning the entire table would already be very helpful. – Volker Hauf Jun 9 '14 at 6:02
  • In the off chance that the column on which you want to add 'not null' is unique, you might be able to use the technique described here: http://stackoverflow.com/a/20006502. – Brian Hahn Dec 18 '14 at 20:54
  • 1
    For the record: The NOT VALID feature has since been added for CHECK and FK constraints. Related: dba.stackexchange.com/questions/75613/… or dba.stackexchange.com/questions/158499/… – Erwin Brandstetter Dec 20 '16 at 0:37
  • 1
    You still have to VALIDATE the constraint later though, there's no way to force it to be considered valid. You can, however, twiddle the catalogs to lie about that much more easily than creating a constraint from whole cloth correctly. – Craig Ringer Dec 20 '16 at 11:30
4

One potential alternative is to create a check constraint using NOT VALID, then validating the check constraint later. This method requires holding an ACCESS EXCLUSIVE lock only for the duration to create the constraint, which should be on the order of milliseconds. The VALIDATE command will perform a time-consuming full table scan to validate the constraint, but it holds a less restrictive SHARE UPDATE EXCLUSIVE lock.

As for trade-offs, I've been unable to find any documentation that mentions internal mechanism differences between a standard NOT NULL constraint and a check constraint that validates a column is not null. I recall digging up a forum post that eluded to a potential performance difference, but have lost the link, so this is unconfirmed.

ALTER TABLE table ADD CONSTRAINT table_value_not_null_check CHECK (value IS NOT NULL) NOT VALID;

ALTER TABLE table VALIDATE CONSTRAINT table_value_not_null_check;

Sources:

https://www.postgresql.org/docs/9.4/static/sql-altertable.html https://www.postgresql.org/docs/9.4/static/explicit-locking.html

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