Subject: Re: [HACKERS] SET NOT NULL [NOT VALID /
CONCURRENTLY]?



Attached is the function SET_NOT_NULL(_Schema name, _Table name, _Column name) which does the following:
1. LOCK TABLE %I.%I IN ACCESS EXCLUSIVE MODEjust like the normal DDL commands would do
2. SELECT EXISTS (SELECT 1 FROM %I.%I WHERE %I IS NULL)which is fast if there is an index on the column
3. UPDATE pg_catalog.pg_attribute SET attnotnull = TRUE    WHERE attrelid = %L::oid    AND   attname  = %L
Pragmatically, would this be a safe approach? 
On Wed, Dec 21, 2016 at 6:53 PM, Joel Jacobson <[email protected]> wrote:
If you are fully confident you have no NULL values,
e.g. if you have all your logics in db functions and you validate all
INSERTs to a table won't pass any NULL values,
and you have checked all the rows in a table are NOT NULL for the column,
would it be completely crazy to just set pg_attribute.attnotnull to
TRUE for the column?

Is anything else happening "under the hood" than just locking all rows
and verifying there are no NULL rows, and then setting attnotnull to
TRUE?


On Wed, Dec 21, 2016 at 6:37 PM, Craig Ringer <[email protected]> wrote:
> On 21 December 2016 at 19:01, Joel Jacobson <[email protected]> wrote:
>
>> Similar to what we (Trustly) did when we sponsored the FOR KEY LOCK
>> feature to improve concurrency,
>> we would be very interested in also sponsoring this feature, as it
>> would mean a great lot to us.
>> I don't know if this is the right forum trying to find someone/some
>> company to sign up for the task,
>> please let me know if I should mail to some other list. Thanks.
>
> You'll probably get mail off list.
>
> For what it's worth, there's a bit of a complexity here. PostgreSQL
> doesn't model NOT NULL as a true CONSTRAINT. Instead it's a column
> attribute. I suspect we would need to change that in order to allow a
> NOT VALID NOT NULL constraint to be created.
>
> That's at least partly why the docs say that "option NOT VALID [...]
> is currently only allowed for foreign key and CHECK constraints".
>
> Note that "[VALIDATE] acquires only a SHARE UPDATE EXCLUSIVE lock on
> the table being altered" so it's already suitable for what you need.
> The challenge is making it possible to create a NOT VALID constraint
> for NOT NULL.
>
> --
>  Craig Ringer                   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services



--
Joel Jacobson

Mobile: +46703603801
Trustly.com | Newsroom | LinkedIn | Twitter



--
Joel Jacobson

Mobile: +46703603801Trustly.com | Newsroom | LinkedIn | Twitter


Attachment: set_not_null.sql
Description: Binary data


--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Programming list archiving by: Enterprise Git Hosting