Thread: BUG #17271: Updating enum columns type fails when constraints exist

BUG #17271: Updating enum columns type fails when constraints exist

From
PG Bug reporting form
Date:
The following bug has been logged on the website:

Bug reference:      17271
Logged by:          Amaury Dumoulin
Email address:      amaury@castordoc.com
PostgreSQL version: 12.8
Operating system:   alpine 3.14 on docker (host macOs 11.6)
Description:

Error message upon failure

The following fails

CREATE TYPE direction_state AS ENUM('UP', 'DOWN');
CREATE TABLE example (id SERIAL PRIMARY KEY, direction direction_state NOT
NULL, below BOOLEAN NOT NULL);
ALTER TABLE example ADD CONSTRAINT ck_example_direction CHECK ((direction =
'DOWN' AND below) OR (NOT below));
ALTER TYPE direction_state RENAME TO direction_state_old;
CREATE TYPE direction_state AS ENUM('UP', 'DOWN', 'UNKNOWN');
ALTER TABLE example ALTER COLUMN direction TYPE direction_state USING
direction::text::direction_state;
DROP TYPE direction_state_old

With a sibyllin error message
ERROR:  operator does not exist: direction_state = direction_state_old
HINT:  No operator matches the given name and argument types. You might need
to add explicit type casts.

If we drop and recreate the constraint it works

CREATE TYPE direction_state AS ENUM('UP', 'DOWN');
CREATE TABLE example (id SERIAL PRIMARY KEY, direction direction_state NOT
NULL, below BOOLEAN NOT NULL);
ALTER TABLE example ADD CONSTRAINT ck_example_direction CHECK ((direction =
'DOWN' AND below) OR (NOT below));
ALTER TYPE direction_state RENAME TO direction_state_old;
CREATE TYPE direction_state AS ENUM('UP', 'DOWN', 'UNKNOWN');
ALTER TABLE example DROP CONSTRAINT ck_example_direction;
ALTER TABLE example ALTER COLUMN direction TYPE direction_state USING
direction::text::direction_state;
DROP TYPE direction_state_old
ALTER TABLE example ADD CONSTRAINT ck_example_direction CHECK ((direction =
'DOWN' AND below) OR (NOT below));


Re: BUG #17271: Updating enum columns type fails when constraints exist

From
"David G. Johnston"
Date:
On Thu, Nov 4, 2021 at 6:54 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      17271
Logged by:          Amaury Dumoulin
Email address:      amaury@castordoc.com
PostgreSQL version: 12.8
Operating system:   alpine 3.14 on docker (host macOs 11.6)
Description:       

Error message upon failure

With a sibyllin error message
ERROR:  operator does not exist: direction_state = direction_state_old
HINT:  No operator matches the given name and argument types. You might need
to add explicit type casts.

If we drop and recreate the constraint it works


This does not surprise me - why do you believe it is a bug?  What should happen?

David J.

Re: BUG #17271: Updating enum columns type fails when constraints exist

From
"David G. Johnston"
Date:
On Thu, Nov 4, 2021 at 6:54 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      17271
Logged by:          Amaury Dumoulin
Email address:      amaury@castordoc.com
PostgreSQL version: 12.8
Operating system:   alpine 3.14 on docker (host macOs 11.6)
Description:       


Any particular reason you didn't just use:

ALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } neighbor_enum_value ]

David J.

Re: BUG #17271: Updating enum columns type fails when constraints exist

From
Amaury Dumoulin
Date:
Hi David,

Thanks for your quick response.
I believe it should fail.

However the error message should state that the constraint is blocking the operation.
The current error message makes it hard to make a connection to the culprit.

The reason we did not go for ADD VALUE is that we use the same approach to change all values at once by redefining the enum. 
First ORM reasons which evolved to an internal standard (which we should change based on your suggestion).

What do you think?

Le jeu. 4 nov. 2021 à 15:00, David G. Johnston <david.g.johnston@gmail.com> a écrit :
On Thu, Nov 4, 2021 at 6:54 AM PG Bug reporting form <noreply@postgresql.org> wrote:
The following bug has been logged on the website:

Bug reference:      17271
Logged by:          Amaury Dumoulin
Email address:      amaury@castordoc.com
PostgreSQL version: 12.8
Operating system:   alpine 3.14 on docker (host macOs 11.6)
Description:       


Any particular reason you didn't just use:

ALTER TYPE name ADD VALUE [ IF NOT EXISTS ] new_enum_value [ { BEFORE | AFTER } neighbor_enum_value ]

David J.

Re: BUG #17271: Updating enum columns type fails when constraints exist

From
Tom Lane
Date:
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thu, Nov 4, 2021 at 6:54 AM PG Bug reporting form <noreply@postgresql.org>
> wrote:
>> ERROR:  operator does not exist: direction_state = direction_state_old

> This does not surprise me - why do you believe it is a bug?  What should
> happen?

Yeah.  Concretely, what you have in the constraint (after the RENAME
TYPE) is

# \d example
...
Check constraints:
    "ck_example_direction" CHECK (direction = 'DOWN'::direction_state_old AND below OR NOT below)

Simply updating "direction" to some other type doesn't provide any
guidance about changing the type of the constant, so re-parsing the
constraint fails.  It's hard to see what we could do here that
wouldn't involve a lot of guesswork and chances of getting things
wrong.

I concur with David's advice that ALTER TYPE ADD VALUE would
be a better answer, at least for the specific use-case you're
showing here.

            regards, tom lane



Re: BUG #17271: Updating enum columns type fails when constraints exist

From
Tom Lane
Date:
Amaury Dumoulin <amaury@castordoc.com> writes:
> I believe it should fail.
> However the error message should state that the constraint is blocking the
> operation.
> The current error message makes it hard to make a connection to the culprit.

Hmm, that's a reasonable concern, though you didn't make it clear that
that was what you were on about.  We could possibly provide an addendum
like "CONTEXT: while processing constraint foo" in this situation.

            regards, tom lane