Date: Wed, 29 Nov 2017 10:21:35 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Sergei Kornilov <sk@zsrv.org>,
	"pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: using index or check in ALTER TABLE SET NOT NULL
Message-ID: <20171129152135.GN4628@tamriel.snowman.net>
References: <81911511895540@web58j.yandex.ru>
 <CA+TgmoZKR4dTR7KyoM4jfJN2J-eNoBRG2_XHdZa1f0VUbK1xXQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="wCcyVEMiq6dzt3Xj"
Content-Disposition: inline
In-Reply-To: 
 <CA+TgmoZKR4dTR7KyoM4jfJN2J-eNoBRG2_XHdZa1f0VUbK1xXQ@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk


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

Robert,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Tue, Nov 28, 2017 at 1:59 PM, Sergei Kornilov <sk@zsrv.org> wrote:
> > I write patch to speed up ALTER TABLE SET NOT NULL by check existed che=
ck constraints or indexes. Huge phase 3 with verify table data will be skip=
ped if table has valid check constraint cover "alteredfield IS NOT NULL" co=
ndition or by SPI query if found index with compatible condition or regular=
 amsearchnulls index on processed field.
>=20
> Doing this based on the existence of a valid constraint which implies
> that no nulls can be present seems like a good idea.  Doing it based
> on an index scan doesn't necessarily seem like a good idea.  We have
> no guarantee at all that the index scan will be faster than scanning
> the table would have been, and a single table scan can do multiple
> verification steps if, for example, multiple columns are set NOT NULL
> at the same time.

Isn't the first concern addressed by using SPI..?

As for the second concern, couldn't that be done with a more complicated
query through SPI, though things might have to be restructured some to
make it possible to do that.

Just, generally speaking, this is definitely something that I think we
want and neither of the above concerns seem like they're technical
reasons why we can't use something like this approach, just needs to
perhaps be reworked to handle multiple columns in a single query.

Thanks!

Stephen

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

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

iQIcBAEBCgAGBQJaHtB/AAoJEO1sijiDR2RVphIQAKwCyFyIrAR8qpmk1ZB1AICJ
SsXVwf2sc/3KpLT3vrmpIk13jLc0VpeCh2y2rb9ssenkpYe7bacoB0+riY/pXRcD
zAOzOIIgf6/UpI9Cg1bMqkIHnjAzKgKrvOas3oGdyizQayrWMuMb2Z8EMsUeXOf+
dq7PCQdU3QRRLVfd1o7l5uj1eIcUdEuqCAJjJsvk4iqGTGwvjAGupIbKx5y9+r6r
WqlSe9VPhn4CYyNQbM5gNSMyCqPSieTqKyJY4TT+ZRsLObYKhenii7pseWxTooiJ
gJyQXKhR46r4/tETWYfa2BFG7o0g0mCykbyFrBv108z/lG/hRXiHGBbIbFStJS2b
iZqBjf2MpcH5K0DoBhC0pH5aphbxGtLwuNnl8wmhMSvrtf47oaWAPW7RauVU/lNt
vcwNFqKPx5d8Vb9+0qgjiu1KYrYP4j1IHuaZ4aAFafrtYXJQbLBsAn1JgglEpjo3
JNQgI3MlHmD2//mw0IOERjumnTPn/+0Va3bsbDXyBIRaIq1cISuU5XFGrt6I9jnM
inzKSGc8fudFhcMR3M2Q6PiWbaAiqkw+ErKQj6uwByoFKuB4JqwYa6Dinkdv7YA5
7ywx3Jyq4k0ZtR68nK0H0dvuUUelVdo9QYcrHkPBd0xW3RKqkpwN7r1Z65XACREN
I09PUPIiTAJvyspjcqxN
=3w2d
-----END PGP SIGNATURE-----

--wCcyVEMiq6dzt3Xj--