Re: 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 MODE
just 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 <joel(at)trustly(dot)com> 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 <craig(at)2ndquadrant(dot)com>
> wrote:
> > On 21 December 2016 at 19:01, Joel Jacobson <joel(at)trustly(dot)com> 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: +46703603801
*Trustly.com <http://trustly.com/> | Newsroom
<http://www.mynewsdesk.com/trustly_en> | LinkedIn
<https://www.linkedin.com/company/trustly-group-ab> | **Twitter
<https://twitter.com/Trustly>*

* <https://trustly.com/>*