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

2016-12-22 Thread Robert Haas
On Wed, Dec 21, 2016 at 7:55 PM, Joel Jacobson wrote: > 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

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

2016-12-21 Thread Joel Jacobson
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

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

2016-12-21 Thread Joel Jacobson
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

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

2016-12-21 Thread Craig Ringer
On 21 December 2016 at 19:01, Joel Jacobson 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

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

2016-12-21 Thread Joel Jacobson
On Wed, Dec 21, 2016 at 4:24 PM, Craig Ringer wrote: >> Is anyone working on fixing this for PostgreSQL 10? > > Not as far as I know. > > IMO this and other similar cases should all be handled the same way: > create the constraint NOT VALID, then VALIDATE it while holding a

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

2016-12-21 Thread Craig Ringer
On 21 December 2016 at 16:48, Joel Jacobson wrote: > Hi hackers, > > I would be good if it would be possible to quickly set NOT NULL for an > existing column in a table > that have no rows where the column IS NULL and where there is a full > index on the column > allowing the