Hello, Let me try again... Here is simple example. To do: alter table users add column aaaa integer not null default 0; Table is big, updated, referenced etc (big - means that alter lock the table long enought to kill the system). Note that it is not my design - I have to do alter the table... but
Solution: 1. alter table users add column aaaa integer; -- short lock 2. alter table users alter column aaaa set default 0; 3. update users set aaaa = 0 where users.id between a and b; -- preparing for constraint - in small chunks 4. update users set aaaa = 0 where aaaa is null; 5. alter table users alter column aaaa set not null; Works, but I hate it. I would like to do: alter table users add column aaaa integer not null default 0; - with something like "concurrently" or "no check" - and let PG to do the job. In that case I expect PG to update meta data, and for updated rows set default - in other case they can not satisfy check. It would be great that step 3 has been done, but I understand it can be a problem. I see that breaking operation integrity is needed. I have a script with some parameters that do it almost automatically. What I want to point is that PG becomes more and more popular. People use it for bigger and bigger databases. In that case typical alter can be a PITA. If something can be done by DB, I would like it to be done in this way - as safer and faster way. In this particular case - I expect DB to take care about new and updated data. Correcting older rows is nice to have. That parameter can be stored to inform everybody - that some data may not satisfy check or null can be found instead of default. Look at commit_delay / commit_siblings. System is faster, but if something go wrong - something (else) will be lost. It is DBA decision what to choose. If DB keeps all information in pg_class, pg_attribute everyone can get how the changes ware made. -- Regards, Michał Zaborowski (TeXXaS) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers