Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qm1VA-005Jge-Cw for pgsql-hackers@arkaria.postgresql.org; Fri, 29 Sep 2023 00:37:04 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qm1V8-0008zO-TR for pgsql-hackers@arkaria.postgresql.org; Fri, 29 Sep 2023 00:37:02 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qm1V8-0008z5-J9 for pgsql-hackers@lists.postgresql.org; Fri, 29 Sep 2023 00:37:02 +0000 Received: from relay4-d.mail.gandi.net ([217.70.183.196]) by makus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qm1V5-007Cj8-AT for pgsql-hackers@postgresql.org; Fri, 29 Sep 2023 00:37:01 +0000 Received: by mail.gandi.net (Postfix) with ESMTPSA id 00EA0E0004; Fri, 29 Sep 2023 00:36:53 +0000 (UTC) Content-Type: multipart/mixed; boundary="------------qnIQvQ45AY6gjkQvXQI5YLbh" Message-ID: <9fffd149-da0f-0c9c-6745-731fb688642a@postgresfriends.org> Date: Fri, 29 Sep 2023 02:36:53 +0200 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.15.1 Subject: Re: Allow deleting enumerated values from an existing enumerated data type Content-Language: en-US To: Tom Lane , Andrew Dunstan Cc: =?UTF-8?B?0JTQsNC90LjQuyDQodGC0L7Qu9C/0L7QstGB0LrQuNGF?= , pgsql-hackers@postgresql.org, o.tselebrovskiy@postgrespro.ru, d.frolov@postgrespro.ru References: <953107.1695911339@sss.pgh.pa.us> <4755493c-a25c-1332-d2c1-900bd02fb125@dunslane.net> <1081742.1695926782@sss.pgh.pa.us> From: Vik Fearing In-Reply-To: <1081742.1695926782@sss.pgh.pa.us> X-GND-Sasl: vik@postgresfriends.org List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------qnIQvQ45AY6gjkQvXQI5YLbh Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 9/28/23 20:46, Tom Lane wrote: > Andrew Dunstan writes: >> I wonder if we could have a boolean flag in pg_enum, indicating that >> setting an enum to that value was forbidden. > > Yeah, but that still offers no coherent solution to the problem of > what happens if there's a table that already contains such a value. > It doesn't seem terribly useful to forbid new entries if you can't > get rid of old ones. > > Admittedly, a DISABLE flag would at least offer a chance at a > race-condition-free scan to verify that no such values remain > in tables. But as somebody already mentioned upthread, that > wouldn't guarantee that the value doesn't appear in non-leaf > index pages. So basically you could never get rid of the pg_enum > row, short of a full dump and restore. > > We went through all these points years ago when the enum feature > was first developed, as I recall. Nobody thought that the ability > to remove an enum value was worth the amount of complexity it'd > entail. This issue comes up regularly (although far from often). Do we want to put some comments right where would-be implementors would be sure to see it? Attached is an example of what I mean. Documentation is intentionally omitted. -- Vik Fearing --------------qnIQvQ45AY6gjkQvXQI5YLbh Content-Type: text/x-patch; charset=UTF-8; name="dropenum.diff" Content-Disposition: attachment; filename="dropenum.diff" Content-Transfer-Encoding: base64 ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3BhcnNlci9ncmFtLnkgYi9zcmMvYmFja2VuZC9w YXJzZXIvZ3JhbS55CmluZGV4IDdkMjAzMjg4NWUuLmY4ZDcwY2RhYTAgMTAwNjQ0Ci0tLSBh L3NyYy9iYWNrZW5kL3BhcnNlci9ncmFtLnkKKysrIGIvc3JjL2JhY2tlbmQvcGFyc2VyL2dy YW0ueQpAQCAtNjQwNCw2ICs2NDA0LDI5IEBAIEFsdGVyRW51bVN0bXQ6CiAJCQkJbi0+c2tp cElmTmV3VmFsRXhpc3RzID0gZmFsc2U7CiAJCQkJJCQgPSAoTm9kZSAqKSBuOwogCQkJfQor CQkgfCBBTFRFUiBUWVBFX1AgYW55X25hbWUgRFJPUCBWQUxVRV9QIFNjb25zdAorCQkJewor CQkJCS8qCisJCQkJICogVGhlIGZvbGxvd2luZyBwcm9ibGVtcyBtdXN0IGJlIHNvbHZlZCBi ZWZvcmUgdGhpcyBjYW4gYmUKKwkJCQkgKiBpbXBsZW1lbnRlZDoKKwkJCQkgKgorCQkJCSAq IC0gVGhlcmUgY2FuIGJlIG5vIGRhdGEgb2YgdGhpcyB0eXBlIHVzaW5nIHRoaXMgdmFsdWUg aW4KKwkJCQkgKiAgIGFueSB0YWJsZQorCQkJCSAqCisJCQkJICogLSBUaGUgdmFsdWUgbWF5 IG5vdCBhcHBlYXIgaW4gYW55IG5vbi1sZWFmIHBhZ2Ugb2YgYQorCQkJCSAqICAgYnRyZWUg KGFuZCBzaW1pbGFyIGlzc3VlcyB3aXRoIG90aGVyIGluZGV4IHR5cGVzKQorCQkJCSAqCisJ CQkJICogLSBDb25jdXJyZW50IHNlc3Npb25zIG11c3Qgbm90IGJlIGFibGUgdG8gaW5zZXJ0 IHRoaXMKKwkJCQkgKiAgIHZhbHVlIHdoaWxlIHRoZSBwcmVjZWRpbmcgY29uZGl0aW9ucyBh cmUgYmVpbmcgY2hlY2tlZAorCQkJCSAqCisJCQkJICogLSBQb3NzaWJseSBtb3JlLi4uCisJ CQkJICovCisKKwkJCQllcmVwb3J0KEVSUk9SLAorCQkJCQkJKGVycmNvZGUoRVJSQ09ERV9G RUFUVVJFX05PVF9TVVBQT1JURUQpLAorCQkJCQkJIGVycm1zZygiZHJvcHBpbmcgYW4gZW51 bSB2YWx1ZSBpcyBub3QgeWV0IGltcGxlbWVudGVkIiksCisJCQkJCQkgcGFyc2VyX2VycnBv c2l0aW9uKEA0KSkpOworCQkJfQogCQkgOwogCiBvcHRfaWZfbm90X2V4aXN0czogSUZfUCBO T1QgRVhJU1RTICAgICAgICAgICAgICB7ICQkID0gdHJ1ZTsgfQpkaWZmIC0tZ2l0IGEvc3Jj L3Rlc3QvcmVncmVzcy9leHBlY3RlZC9lbnVtLm91dCBiL3NyYy90ZXN0L3JlZ3Jlc3MvZXhw ZWN0ZWQvZW51bS5vdXQKaW5kZXggMDExNTk2ODhlNS4uMTA1YWU3YTJmOSAxMDA2NDQKLS0t IGEvc3JjL3Rlc3QvcmVncmVzcy9leHBlY3RlZC9lbnVtLm91dAorKysgYi9zcmMvdGVzdC9y ZWdyZXNzL2V4cGVjdGVkL2VudW0ub3V0CkBAIC02MDUsNiArNjA1LDExIEBAIEVSUk9SOiAg InJlZCIgaXMgbm90IGFuIGV4aXN0aW5nIGVudW0gbGFiZWwKIC0tIGNoZWNrIHRoYXQgcmVu YW1pbmcgdG8gYW4gZXhpc3RlbnQgdmFsdWUgZmFpbHMKIEFMVEVSIFRZUEUgcmFpbmJvdyBS RU5BTUUgVkFMVUUgJ2JsdWUnIFRPICdncmVlbic7CiBFUlJPUjogIGVudW0gbGFiZWwgImdy ZWVuIiBhbHJlYWR5IGV4aXN0cworLS0gV2Ugc3RpbGwgY2FuJ3QgZHJvcCBhbiBlbnVtIHZh bHVlCitBTFRFUiBUWVBFIHJhaW5ib3cgRFJPUCBWQUxVRSAnZ3JlZW4nOworRVJST1I6ICBk cm9wcGluZyBhbiBlbnVtIHZhbHVlIGlzIG5vdCB5ZXQgaW1wbGVtZW50ZWQKK0xJTkUgMTog QUxURVIgVFlQRSByYWluYm93IERST1AgVkFMVUUgJ2dyZWVuJzsKKyAgICAgICAgICAgICAg ICAgICAgICAgICAgIF4KIC0tCiAtLSBjaGVjayB0cmFuc2FjdGlvbmFsIGJlaGF2aW91ciBv ZiBBTFRFUiBUWVBFIC4uLiBBREQgVkFMVUUKIC0tCmRpZmYgLS1naXQgYS9zcmMvdGVzdC9y ZWdyZXNzL3NxbC9lbnVtLnNxbCBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2VudW0uc3FsCmlu ZGV4IDkzMTcxMzc5ZjIuLmE5OGRmNDBlZDggMTAwNjQ0Ci0tLSBhL3NyYy90ZXN0L3JlZ3Jl c3Mvc3FsL2VudW0uc3FsCisrKyBiL3NyYy90ZXN0L3JlZ3Jlc3Mvc3FsL2VudW0uc3FsCkBA IC0yNzYsNiArMjc2LDkgQEAgQUxURVIgVFlQRSByYWluYm93IFJFTkFNRSBWQUxVRSAncmVk JyBUTyAnY3JpbXNvbic7CiAtLSBjaGVjayB0aGF0IHJlbmFtaW5nIHRvIGFuIGV4aXN0ZW50 IHZhbHVlIGZhaWxzCiBBTFRFUiBUWVBFIHJhaW5ib3cgUkVOQU1FIFZBTFVFICdibHVlJyBU TyAnZ3JlZW4nOwogCistLSBXZSBzdGlsbCBjYW4ndCBkcm9wIGFuIGVudW0gdmFsdWUKK0FM VEVSIFRZUEUgcmFpbmJvdyBEUk9QIFZBTFVFICdncmVlbic7CisKIC0tCiAtLSBjaGVjayB0 cmFuc2FjdGlvbmFsIGJlaGF2aW91ciBvZiBBTFRFUiBUWVBFIC4uLiBBREQgVkFMVUUKIC0t Cg== --------------qnIQvQ45AY6gjkQvXQI5YLbh--