[GENERAL] Adding a not null constraint faster
Is there a way to add a NOT NULL constraint to a column without having to lock the table while a sequential read happens? Seems like it should be possible to add an index on the column for null values, like: create index on t using btree(col_name) where col_name is null; Then when adding the not null constraint, pg could lock the table, use that index to see if there's any nulls, and if there's not, add the constraint. (That's not possible already, right? I couldn't figure it out if it was.) Right now, the strategy for adding a not null constraint to a column without any significant downtime involves creating a new table, as I understand it. Joe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 9.4 beta - pg_get_viewdef() and WITH CHECK OPTION
Hello, when playing with 9.4 beta I noticed that the result of pg_get_viewdef() will not include the new WITH CHECK OPTION clause when the view was created using it. Is that intended (if so: why?) or is this an oversight/bug? Regards Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Adding a not null constraint faster
Joe Van Dyk wrote: > Is there a way to add a NOT NULL constraint to a column without having > to lock the table while a sequential read happens? > > Seems like it should be possible to add an index on the column for > null values, like: > create index on t using btree(col_name) where col_name is null; > > Then when adding the not null constraint, pg could lock the table, use > that index to see if there's any nulls, and if there's not, add the > constraint. It is not possible right now. Ideally you should be able to add CONSTRAINT NOT NULL NOT VALID; that only inserts the catalog tuple and enforces the constraint on all future tuples, and so should be quick. In a later step you'd do VALIDATE CONSTRAINT, with a reduced lock, which runs the seqscan. To implement this we need to have NOT NULL in catalogs. There is an incomplete patch I wrote for this, but it needs some further work (along as, I imagine, some rebasing). You can see the latest version I had at http://www.postgresql.org/message-id/20130802040319.gf5...@eldon.alvh.no-ip.org -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
