Re: Lazy constraints / defaults

I am wondering whether people use ALTER TABLE ALTER COLUMN foo SET NOT
NULL enough to justify concurrency coding.

---------------------------------------------------------------------------

Decibel! wrote:
> 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
>
>

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +