Timeline for Enforce NOT NULL for set of columns with a CHECK constraint only for new rows
Current License: CC BY-SA 3.0
8 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| May 23, 2017 at 12:40 | history | edited | CommunityBot |
replaced http://stackoverflow.com/ with https://stackoverflow.com/
|
|
| Feb 9, 2016 at 18:18 | vote | accept | CommunityBot | ||
| Feb 4, 2016 at 5:07 | history | edited | jpmc26 | CC BY-SA 3.0 |
modified constraint to work the way the OP Wants
|
| Feb 4, 2016 at 5:02 | comment | added | jpmc26 |
@LucasPossamai It is probably much faster than you think. I just tested on a table with over 14 million rows. Adding the column took 273023 ms (4.5 minutes). The UPDATE took 349422 ms (5.8 minutes). Applying a similar CHECK took 20996 ms (21 seconds). That's a total of about 10 minutes. This was all on a pretty low powered machine (4 GB RAM, dual core CPU). You have a little less than 3 times the number of rows. If the runtime grows linearly, which I expected it would, you're talking about a half hour of downtime, extremely reasonable in many contexts. But do test the runtimes yourself.
|
|
| Feb 4, 2016 at 4:45 | comment | added | jpmc26 | I seriously doubt that it's impossible. It might take a long time, but that's a very different problem. Whether you can deal with that time frame depends on your constraints. You can test how long it takes, and once you know, you can make a decision. Can you afford to be offline that long? Could you put the database/application in a read only state while it runs? (For the latter, you could possibly make the change in a clone and then switch over to the new database.) | |
| Feb 4, 2016 at 4:34 | comment | added | user83914 | Your answer is very interesting. However, I'd have to update more than 40m rows. It's quite impossible =\ | |
| Feb 4, 2016 at 3:53 | history | edited | jpmc26 | CC BY-SA 3.0 |
added 382 characters in body
|
| Feb 4, 2016 at 3:25 | history | answered | jpmc26 | CC BY-SA 3.0 |