Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eK5bj-0001QB-H4 for pgsql-hackers@arkaria.postgresql.org; Wed, 29 Nov 2017 16:53:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eK5bj-0003b8-3E for pgsql-hackers@arkaria.postgresql.org; Wed, 29 Nov 2017 16:53:11 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eK5bi-0003ax-Sg for pgsql-hackers@lists.postgresql.org; Wed, 29 Nov 2017 16:53:10 +0000 Received: from forward5j.cmail.yandex.net ([2a02:6b8:0:1630::18]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1eK5be-000647-5q for pgsql-hackers@postgresql.org; Wed, 29 Nov 2017 16:53:10 +0000 Received: from mxback4j.mail.yandex.net (mxback4j.mail.yandex.net [IPv6:2a02:6b8:0:1619::10d]) by forward5j.cmail.yandex.net (Yandex) with ESMTP id 9F78421807; Wed, 29 Nov 2017 19:53:02 +0300 (MSK) Received: from web27j.yandex.ru (web27j.yandex.ru [2a02:6b8:0:1619::327]) by mxback4j.mail.yandex.net (nwsmtp/Yandex) with ESMTP id 3vMdADEZuZ-r1hG1rrT; Wed, 29 Nov 2017 19:53:01 +0300 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=zsrv.org; s=mail; t=1511974381; bh=5rXiihNggR+VbH1th9KCCoPJ01J33s5cGJLCcWs503U=; h=From:To:Cc:In-Reply-To:References:Subject:Message-Id:Date; b=MKLZN6mEM3PS3yZHkyWTLJmfS1MLx6lhOgURQSc4TnG0QzI7Sy9DoBosrLSMdtVse OuqIQ6cMWhqUoFkAc0DY7UmqSFJN4xiuS6mekZ//KOsMjjtOHIi8tXPRZbt2nK4fXu o8JpaOGC8/lMRmh8MognDJ9OeWKAM6aEyyv6QtKM= Authentication-Results: mxback4j.mail.yandex.net; dkim=pass header.i=@zsrv.org Received: by web27j.yandex.ru with HTTP; Wed, 29 Nov 2017 19:53:01 +0300 From: Sergei Kornilov To: Tom Lane , Stephen Frost Cc: Robert Haas , "pgsql-hackers@postgresql.org" In-Reply-To: <370481511970756@web3j.yandex.ru> References: <81911511895540@web58j.yandex.ru> <20171129152135.GN4628@tamriel.snowman.net> <9878.1511970441@sss.pgh.pa.us> <370481511970756@web3j.yandex.ru> Subject: Re: using index or check in ALTER TABLE SET NOT NULL MIME-Version: 1.0 Message-Id: <491651511974381@web27j.yandex.ru> X-Mailer: Yamail [ http://yandex.ru ] 5.0 Date: Wed, 29 Nov 2017 19:53:01 +0300 Content-Type: multipart/mixed; boundary="----==--bound.49167.web27j.yandex.ru" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------==--bound.49167.web27j.yandex.ru Content-Transfer-Encoding: 7bit Content-Type: text/plain Here is new patch with check only existed valid constraints and without SPI at all. Thanks ------==--bound.49167.web27j.yandex.ru Content-Disposition: attachment; filename="alter_table_set_not_null_by_constraints_only_v2.patch" Content-Transfer-Encoding: base64 Content-Type: text/x-diff; name="alter_table_set_not_null_by_constraints_only_v2.patch" ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL2NvbW1hbmRzL3RhYmxlY21kcy5jIGIvc3JjL2JhY2tl bmQvY29tbWFuZHMvdGFibGVjbWRzLmMKaW5kZXggZDk3OWNlMi4uN2FiNzU4MCAxMDA2NDQKLS0t IGEvc3JjL2JhY2tlbmQvY29tbWFuZHMvdGFibGVjbWRzLmMKKysrIGIvc3JjL2JhY2tlbmQvY29t bWFuZHMvdGFibGVjbWRzLmMKQEAgLTM3MCw2ICszNzAsOCBAQCBzdGF0aWMgT2JqZWN0QWRkcmVz cyBBVEV4ZWNEcm9wTm90TnVsbChSZWxhdGlvbiByZWwsIGNvbnN0IGNoYXIgKmNvbE5hbWUsIExP Q0tNTwogc3RhdGljIHZvaWQgQVRQcmVwU2V0Tm90TnVsbChSZWxhdGlvbiByZWwsIGJvb2wgcmVj dXJzZSwgYm9vbCByZWN1cnNpbmcpOwogc3RhdGljIE9iamVjdEFkZHJlc3MgQVRFeGVjU2V0Tm90 TnVsbChBbHRlcmVkVGFibGVJbmZvICp0YWIsIFJlbGF0aW9uIHJlbCwKIAkJCQkgY29uc3QgY2hh ciAqY29sTmFtZSwgTE9DS01PREUgbG9ja21vZGUpOworc3RhdGljIGJvb2wgaXNTZXROb3ROdWxs TmVlZHNUYWJsZVNjYW4oUmVsYXRpb24gcmVsLCBGb3JtX3BnX2F0dHJpYnV0ZSBhdHRyKTsKK3N0 YXRpYyBib29sIENvbnN0cmFpbnRJbXBsaWVkQnlSZWxDb25zdHJhaW50KFJlbGF0aW9uIHNjYW5y ZWwsIExpc3QgKnBhcnRDb25zdHJhaW50KTsKIHN0YXRpYyBPYmplY3RBZGRyZXNzIEFURXhlY0Nv bHVtbkRlZmF1bHQoUmVsYXRpb24gcmVsLCBjb25zdCBjaGFyICpjb2xOYW1lLAogCQkJCQlOb2Rl ICpuZXdEZWZhdWx0LCBMT0NLTU9ERSBsb2NrbW9kZSk7CiBzdGF0aWMgT2JqZWN0QWRkcmVzcyBB VEV4ZWNBZGRJZGVudGl0eShSZWxhdGlvbiByZWwsIGNvbnN0IGNoYXIgKmNvbE5hbWUsCkBAIC01 ODYzLDggKzU4NjUsMTAgQEAgQVRFeGVjU2V0Tm90TnVsbChBbHRlcmVkVGFibGVJbmZvICp0YWIs IFJlbGF0aW9uIHJlbCwKIAogCQlDYXRhbG9nVHVwbGVVcGRhdGUoYXR0cl9yZWwsICZ0dXBsZS0+ dF9zZWxmLCB0dXBsZSk7CiAKLQkJLyogVGVsbCBQaGFzZSAzIGl0IG5lZWRzIHRvIHRlc3QgdGhl IGNvbnN0cmFpbnQgKi8KLQkJdGFiLT5uZXdfbm90bnVsbCA9IHRydWU7CisJCWlmIChpc1NldE5v dE51bGxOZWVkc1RhYmxlU2NhbihyZWwsIChGb3JtX3BnX2F0dHJpYnV0ZSkgR0VUU1RSVUNUKHR1 cGxlKSkpIHsKKwkJCS8qIFRlbGwgUGhhc2UgMyBpdCBuZWVkcyB0byB0ZXN0IHRoZSBjb25zdHJh aW50ICovCisJCQl0YWItPm5ld19ub3RudWxsID0gdHJ1ZTsKKwkJfQogCiAJCU9iamVjdEFkZHJl c3NTdWJTZXQoYWRkcmVzcywgUmVsYXRpb25SZWxhdGlvbklkLAogCQkJCQkJCVJlbGF0aW9uR2V0 UmVsaWQocmVsKSwgYXR0bnVtKTsKQEAgLTU4ODAsNiArNTg4NCw0MiBAQCBBVEV4ZWNTZXROb3RO dWxsKEFsdGVyZWRUYWJsZUluZm8gKnRhYiwgUmVsYXRpb24gcmVsLAogCXJldHVybiBhZGRyZXNz OwogfQogCitzdGF0aWMgYm9vbAoraXNTZXROb3ROdWxsTmVlZHNUYWJsZVNjYW4oUmVsYXRpb24g cmVsLCBGb3JtX3BnX2F0dHJpYnV0ZSBhdHRyKQoreworCUxpc3QgICAgICAgKm5vdE51bGxDb25z dHJhaW50ID0gTklMOworCU51bGxUZXN0ICAgKm5udWxsdGVzdCA9IG1ha2VOb2RlKE51bGxUZXN0 KTsKKworCW5udWxsdGVzdC0+YXJnID0gKEV4cHIgKikgbWFrZVZhcigxLAorCQkJCSAgYXR0ci0+ YXR0bnVtLAorCQkJCSAgYXR0ci0+YXR0dHlwaWQsCisJCQkJICBhdHRyLT5hdHR0eXBtb2QsCisJ CQkJICBhdHRyLT5hdHRjb2xsYXRpb24sCisJCQkJICAwKTsKKwlubnVsbHRlc3QtPm51bGx0ZXN0 dHlwZSA9IElTX05PVF9OVUxMOworCisJLyoKKwkgKiBzYW1lIHRoaW5nIGFzIGluIENvbnN0cmFp bnRJbXBsaWVkQnlSZWxDb25zdHJhaW50CisJICogYXJnaXNyb3c9ZmFsc2UgaXMgY29ycmVjdCBl dmVuIGZvciBhIGNvbXBvc2l0ZSBjb2x1bW4sCisJICogYmVjYXVzZSBhdHRub3RudWxsIGRvZXMg bm90IHJlcHJlc2VudCBhIFNRTC1zcGVjIElTIE5PVAorCSAqIE5VTEwgdGVzdCBpbiBzdWNoIGEg Y2FzZSwganVzdCBJUyBESVNUSU5DVCBGUk9NIE5VTEwuCisJICovCisJbm51bGx0ZXN0LT5hcmdp c3JvdyA9IGZhbHNlOworCW5udWxsdGVzdC0+bG9jYXRpb24gPSAtMTsKKwlub3ROdWxsQ29uc3Ry YWludCA9IGxhcHBlbmQobm90TnVsbENvbnN0cmFpbnQsIG5udWxsdGVzdCk7CisKKwlpZiAoQ29u c3RyYWludEltcGxpZWRCeVJlbENvbnN0cmFpbnQocmVsLCBub3ROdWxsQ29uc3RyYWludCkpCisJ eworCQllcmVwb3J0KERFQlVHMSwKKwkJCShlcnJtc2coInZlcmlmeWluZyB0YWJsZSBcIiVzXCIg Tk9UIE5VTEwgY29uc3RyYWludCAiCisJCQkJIm9uICVzIGF0dHJpYnV0ZSBieSBleGlzdGVkIGNv bnN0cmFpbnRzIiwKKwkJCVJlbGF0aW9uR2V0UmVsYXRpb25OYW1lKHJlbCksIE5hbWVTdHIoYXR0 ci0+YXR0bmFtZSkpKSk7CisJCXJldHVybiBmYWxzZTsKKwl9CisKKwlyZXR1cm4gdHJ1ZTsKK30K KwogLyoKICAqIEFMVEVSIFRBQkxFIEFMVEVSIENPTFVNTiBTRVQvRFJPUCBERUZBVUxUCiAgKgpA QCAtMTM2MjAsMTIgKzEzNjYwLDIzIEBAIENvbXB1dGVQYXJ0aXRpb25BdHRycyhSZWxhdGlvbiBy ZWwsIExpc3QgKnBhcnRQYXJhbXMsIEF0dHJOdW1iZXIgKnBhcnRhdHRycywKIC8qCiAgKiBQYXJ0 Q29uc3RyYWludEltcGxpZWRCeVJlbENvbnN0cmFpbnQKICAqCQlEb2VzIHNjYW5yZWwncyBleGlz dGluZyBjb25zdHJhaW50cyBpbXBseSB0aGUgcGFydGl0aW9uIGNvbnN0cmFpbnQ/CisgKi8KK2Jv b2wKK1BhcnRDb25zdHJhaW50SW1wbGllZEJ5UmVsQ29uc3RyYWludChSZWxhdGlvbiBzY2FucmVs LAorCQkJCQkJCQkJIExpc3QgKnBhcnRDb25zdHJhaW50KQoreworCXJldHVybiBDb25zdHJhaW50 SW1wbGllZEJ5UmVsQ29uc3RyYWludChzY2FucmVsLCBwYXJ0Q29uc3RyYWludCk7Cit9CisKKy8q CisgKiBQYXJ0Q29uc3RyYWludEltcGxpZWRCeVJlbENvbnN0cmFpbnQKKyAqCQlEb2VzIHNjYW5y ZWwncyBleGlzdGluZyBjb25zdHJhaW50cyBpbXBseSBnaXZlbiBjb25zdHJhaW50CiAgKgogICog RXhpc3RpbmcgY29uc3RyYWludHMgaW5jbHVkZXMgaXRzIGNoZWNrIGNvbnN0cmFpbnRzIGFuZCBj b2x1bW4tbGV2ZWwKICAqIE5PVCBOVUxMIGNvbnN0cmFpbnRzIGFuZCBwYXJ0Q29uc3RyYWludCBk ZXNjcmliZXMgdGhlIHBhcnRpdGlvbiBjb25zdHJhaW50LgogICovCi1ib29sCi1QYXJ0Q29uc3Ry YWludEltcGxpZWRCeVJlbENvbnN0cmFpbnQoUmVsYXRpb24gc2NhbnJlbCwKK3N0YXRpYyBib29s CitDb25zdHJhaW50SW1wbGllZEJ5UmVsQ29uc3RyYWludChSZWxhdGlvbiBzY2FucmVsLAogCQkJ CQkJCQkJIExpc3QgKnBhcnRDb25zdHJhaW50KQogewogCUxpc3QJICAgKmV4aXN0Q29uc3RyYWlu dCA9IE5JTDsK ------==--bound.49167.web27j.yandex.ru--