Re: Lazy constraints / defaults - Mailing list pgsql-hackers

This would be very useful for me, and would satisfy the OP's request.

Can we get a TODO?

On Mar 9, 2008, at 4:45 PM, Dawid Kuroczko wrote:

> On Sun, Mar 9, 2008 at 7:25 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> "Michał Zaborowski"  
>> <michal.zaborowski@gmail.com> writes:
>>>  I would like to be able to add CONSTRAINT and/or DEFAULT with out
>>> affecting old rows.
>>
>>  You mean without actually checking that the old rows satisfy the
>>  constraint?  There's approximately zero chance that that proposal
>>  will be accepted.
>
> I think the problem here is to minimize the time when table is held by
> exclusive lock,
> Something similar to the CREATE INDEX CONCURRENTLY (i.e. hold  
> exclusive lock
> for a jiffy, then do the actual work for the old tuples).
>
> So, the proposal would read as to add the ability to perform:
>
>   ALTER TABLE CONCURRENTLY ALTER COLUMN foo SET NOT NULL
> ...where exclusive lock would be held to place the constraint (so  
> all new
> tuples would satisfy it), lock would be released and the old tuples  
> would
> be checked to make sure the constraint is valid.
>
> Should a NULL value be found or should the backend die, the constraint
> should disappear or be marked invalid.
>
>>> Yes, it sounds strange, but... Let's say I have
>>> big table, I want to add new column, with DEFAULT and NOT NULL.
>>> Normally it means long exclusive lock. So - right now I'm adding  
>>> plain
>>> new column, then DEFAULT, then UPDATE on all rows in chunks, then  
>>> NOT
>>> NULL... Can it be little simpler?
>>
>>  Just do it all in one ALTER command.
>>
>>  alter table tab add column col integer not null default 42 check  
>> (col > 0);
>
> I think this will not solve the OP's problem.  He wants to minimize  
> the time
> a table is under exclusive lock, and this ALTER command will  
> effectively
> rewrite the whole table (to add new not null column).
>
> Probably a workable solution would be to play with inheritance:
> -- Add the NULL col colum:
>   ALTER TABLE tab ADD COLUMN col integer;
> -- Create a table which will have col NOT NULL
>   CREATE TABLE tab_new (LIKE tab INCLUDING DEFAULTS INCLUDING
> CONSTRAINTS INCLUDING INDEXES ) INHERITS (tab);
>   ALTER TABLE tab_new ALTER COLUMN col SET NOT NULL;
> -- Make the new values go to tab_new, if simple enough same might be
> done for UPDATEs
>   CREATE RULE insert_new AS ON INSERT TO tab DO INSTEAD INSERT INTO
> tab_new VALUES (NEW.*);
>
> -- Now, make a job which will do something like this:
>   START TRANSACTION ISOLATON LEVEL SERIALIZABLE;
>   UPDATE ONLY tab SET col = 42 WHERE id BETWEEN n AND n + 1000;
>   INSERT INTO tab_new SELECT * FROM ONLY tab WHERE id BETWEEN n AND  
> n + 1000;
>   -- or better:
>   -- INSERT INTO tab_new SELECT a,b,c,42 AS col FROM ONLY tab WHERE id
> BETWEEN n AND n + 1000 FOR UPDATE;
>   DELETE FROM ONLY tab WHERE id BETWEEN n AND n + 1000;
>   COMMIT;
>
> -- Finally, exhange parti^W^W get rid of old tab:
>   SELECT count(*) FROM ONLY tab; -- should be zero
>   ALTER TABLE tab RENAME TO tab_old;
>   ALTER TABLE tab_new RENAME TO tab;
>   ALTER TABLE tab NO INHERIT tab_old;
>
> Of course each step should be done in transaction, probably starting
> with explicit LOCK.  And extra care should be taken
> with respect to the UNIQUE constraints.  In short: unless you are 100%
> sure what you are doing, don't. :-)
>
>    Regards,
>       Dawid
>
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828