Re: Lazy constraints / defaults

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> "=?ISO-8859-2?Q?Micha=B3_Zaborowski?="
>> <michal(dot)zaborowski(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

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