Date: Wed, 29 Nov 2017 11:50:31 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Sergei Kornilov <sk@zsrv.org>, Tom Lane <tgl@sss.pgh.pa.us>,
	"pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: using index or check in ALTER TABLE SET NOT NULL
Message-ID: <20171129165031.GP4628@tamriel.snowman.net>
References: <81911511895540@web58j.yandex.ru>
 <CA+TgmoZKR4dTR7KyoM4jfJN2J-eNoBRG2_XHdZa1f0VUbK1xXQ@mail.gmail.com>
 <20171129152135.GN4628@tamriel.snowman.net>
 <9878.1511970441@sss.pgh.pa.us>
 <370481511970756@web3j.yandex.ru>
 <CA+Tgmoa5NKz8iGW_9v7wz=-+zQFu=E4SZoaTaU1znLaEXRYp-Q@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="YGhOADTaczX5WSp6"
Content-Disposition: inline
In-Reply-To: 
 <CA+Tgmoa5NKz8iGW_9v7wz=-+zQFu=E4SZoaTaU1znLaEXRYp-Q@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk


--YGhOADTaczX5WSp6
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Robert,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Wed, Nov 29, 2017 at 10:52 AM, Sergei Kornilov <sk@zsrv.org> wrote:
> > I agree this. Thinking a little about idea of index scan i can not give=
 reasonable usecase which required index. My target problem of adding NOT N=
ULL to big relation without long downtime can be done with ADD CONSTRAINT N=
OT VALID, VALIDATE it in second transaction, then SET NOT NULL by my patch =
and drop unneeded constraint.
>=20
> Yes, I had the same thought.

I have to admit that the case I was thinking about was the one you
outline below..

> Thinking a little bit further, maybe the idea you had in mind using
> the index scan was that some indexes offer cheap ways of testing
> whether ANY nulls are present in the column.  For example, if we had a
> non-partial btree index whose first column is the one being made NOT
> NULL, we could try an index scan - via index_beginscan() /
> index_getnext() / index_endscan() - trying to pull exactly one null
> from the index, and judge whether or not there are nulls present based
> only whether we get one.  This would be a lot cheaper than scanning a
> large table, but it needs some careful thought because of visibility
> issues.  It's not sufficient that the index contains no nulls that are
> visible to our snapshot; it must contain no nulls that are visible to
> any plausible current or future snapshot.  I doubt that test can be
> written in SQL, but it can probably be written in C.  Moreover, we
> need to avoid not only false negatives (thinking that there is no NULL
> when there is one) but also false positives (thinking there's a NULL
> in the column when there isn't, and thus failing spuriously).  But it
> seems like it might be useful if someone can figure out the details of
> how to make it 100% correct; one index lookup is sure to be a lot
> quicker than a full table scan.

This was along the lines I was thinking also (though I had thought SPI
might be reasonable given our usage of it with FKs, but if it'd require
uglier warts than what SPI already has, then just finding an
appropriate index and using the internal methods would be best).

As for conflicting snapshots, isn't the lock we're taking already
AccessExclusive..?

Thanks!

Stephen

--YGhOADTaczX5WSp6
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJaHuVXAAoJEO1sijiDR2RVuXUQAJrg8hVv9d0FtXIQobxAPJIW
VV05LIxDopppC8DrlIR8TSuJu5RAHQCBMtZ16yP1E/Q7Ac+m1EIBavD6yuGVDgX9
GX0ko8frm6KOdaRvnQEPW7TLA6hH2RM8MdgDmlw6W3zUvP/9iBpebV/6afh4BVuF
CBd71dEO/bexH+ZBMCeOQ4qfPEEKD0i3KaWo8LTEFJF7WZXvJY9DjNMZcT1n6fHg
f41br3+m9ERlcA7/l6He6wtw58Rw6sDTmzyIm4UrjfwUuuexsT4OcHeGbR0eZYdF
fOSuZ9AVc93JiUQ3mMlhNJ/hPqCT9QRif+uaHv4sesofx3GfHHtDORjjxMQqvOkL
pvNy/aEX+4NYiIOqciw0GoVhl2k9ACjFTWTlhYtoicWQXTvXMHXHN1sTGgiVtw3F
d3NSlUHXVGZPUGnkTD8vlA8UJVjOt8ETF1ER+EthbNz1WoX8NI8Icgmof5EaW7PT
ux1B37WU6NDU2Grj39lMg+7lRedR8WvUGTbcwdITgepPHJs5dFJ8E/FJrDDZU+B/
sqPkg8rVdT2dtcg0CECOmIuGs6Ek57TV/cKAhswFM2Qyktt97wS2ByhrUAgXPbS7
thOID4qxhhCUV99fjpF1AMTe8v5Nvl2wYarmk6k8LZWe+YmsmzC+BwSSmRbjrVRC
UcP+gPVf7NLOimkTVgLf
=4q1o
-----END PGP SIGNATURE-----

--YGhOADTaczX5WSp6--