Re: Is it possible to speed up addition of "not null"?

On Thu, Feb 02, 2012 at 02:08:51PM +0000, Florian Weimer wrote:
> * hubert depesz lubaczewski:
>
> > I tried with some indexes, but I can't get the time to something
> > reasonable, so here is my question: is there any way I could make the
> > "not null" constraint *fast*?
>
> You coul patch pg_attribute directly.  I'm not sure if that's still safe
> in current versions, though.

it is interesting option. especially since pg_reorg will recreate the
table anyway.

does anyone of you see any problems with it?

procedure would look like:
1. update pg_attribute set attnotnull = true where attrelid = 'my_table'::regclass and attname = 'not-null-column';
2. delete from my_table where not-null-column is null; -- this shouldn't
   do anything, as I know that there are no null values, but just in
   case
3. pg_reorg of the table.

Best regards,

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/