From: Vik Fearing <vik@postgresfriends.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
To: Andrew Dunstan <andrew@dunslane.net>
Cc: Данил Столповских <danil.stolpovskikh@gmail.com>
Cc: pgsql-hackers@postgresql.org
Cc: o.tselebrovskiy@postgrespro.ru
Cc: d.frolov@postgrespro.ru
Subject: Re: Allow deleting enumerated values from an existing enumerated data type
Date: Fri, 29 Sep 2023 02:36:53 +0200
Message-ID: <9fffd149-da0f-0c9c-6745-731fb688642a@postgresfriends.org> (raw)
In-Reply-To: <1081742.1695926782@sss.pgh.pa.us>
References: <CAG7W4rpQn5vwKJ99sbXkdhgahPSCu=hOZhRqAq8i=ScAH8HRUg@mail.gmail.com>
<953107.1695911339@sss.pgh.pa.us>
<4755493c-a25c-1332-d2c1-900bd02fb125@dunslane.net>
<1081742.1695926782@sss.pgh.pa.us>
On 9/28/23 20:46, Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> 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
Attachments:dropenum.diff (text/x-patch, 2.2K) downloaddiff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.yindex 7d2032885e..f8d70cdaa0 100644--- a/src/backend/parser/gram.y+++ b/src/backend/parser/gram.y@@ -6404,6 +6404,29 @@ AlterEnumStmt:
n->skipIfNewValExists = false;
$$ = (Node *) n;
}
+ | ALTER TYPE_P any_name DROP VALUE_P Sconst+ {+ /*+ * The following problems must be solved before this can be+ * implemented:+ *+ * - There can be no data of this type using this value in+ * any table+ *+ * - The value may not appear in any non-leaf page of a+ * btree (and similar issues with other index types)+ *+ * - Concurrent sessions must not be able to insert this+ * value while the preceding conditions are being checked+ *+ * - Possibly more...+ */++ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("dropping an enum value is not yet implemented"),+ parser_errposition(@4)));+ }
;
opt_if_not_exists: IF_P NOT EXISTS { $$ = true; }
diff --git a/src/test/regress/expected/enum.out b/src/test/regress/expected/enum.outindex 01159688e5..105ae7a2f9 100644--- a/src/test/regress/expected/enum.out+++ b/src/test/regress/expected/enum.out@@ -605,6 +605,11 @@ ERROR: "red" is not an existing enum label
-- check that renaming to an existent value fails
ALTER TYPE rainbow RENAME VALUE 'blue' TO 'green';
ERROR: enum label "green" already exists
+-- We still can't drop an enum value+ALTER TYPE rainbow DROP VALUE 'green';+ERROR: dropping an enum value is not yet implemented+LINE 1: ALTER TYPE rainbow DROP VALUE 'green';+ ^
--
-- check transactional behaviour of ALTER TYPE ... ADD VALUE
--
diff --git a/src/test/regress/sql/enum.sql b/src/test/regress/sql/enum.sqlindex 93171379f2..a98df40ed8 100644--- a/src/test/regress/sql/enum.sql+++ b/src/test/regress/sql/enum.sql@@ -276,6 +276,9 @@ ALTER TYPE rainbow RENAME VALUE 'red' TO 'crimson';
-- check that renaming to an existent value fails
ALTER TYPE rainbow RENAME VALUE 'blue' TO 'green';
+-- We still can't drop an enum value+ALTER TYPE rainbow DROP VALUE 'green';+
--
-- check transactional behaviour of ALTER TYPE ... ADD VALUE
--
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-hackers@postgresql.org
Cc: vik@postgresfriends.org, tgl@sss.pgh.pa.us, andrew@dunslane.net, danil.stolpovskikh@gmail.com, o.tselebrovskiy@postgrespro.ru, d.frolov@postgrespro.ru
Subject: Re: Allow deleting enumerated values from an existing enumerated data type
In-Reply-To: <9fffd149-da0f-0c9c-6745-731fb688642a@postgresfriends.org>
References: <CAG7W4rpQn5vwKJ99sbXkdhgahPSCu=hOZhRqAq8i=ScAH8HRUg@mail.gmail.com> <953107.1695911339@sss.pgh.pa.us> <4755493c-a25c-1332-d2c1-900bd02fb125@dunslane.net> <1081742.1695926782@sss.pgh.pa.us> <9fffd149-da0f-0c9c-6745-731fb688642a@postgresfriends.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox