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 <j...@trustly.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 <cr...@2ndquadrant.com> > wrote: > > On 21 December 2016 at 19:01, Joel Jacobson <j...@trustly.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/>*
set_not_null.sql
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers