From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: amaury@castordoc.com,
        PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #17271: Updating enum columns type fails when constraints
 exist
In-reply-to: 
 <CAKFQuwaY4CjKb=yHDMhSfER+y4P9p=vnC0PJ3Gdve5tewA15eA@mail.gmail.com>
References: <17271-8b4317357c4991e2@postgresql.org>
 <CAKFQuwaY4CjKb=yHDMhSfER+y4P9p=vnC0PJ3Gdve5tewA15eA@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Thu, 04 Nov 2021 06:58:04 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <321162.1636035777.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 04 Nov 2021 10:22:57 -0400
Message-ID: <321163.1636035777@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/321163.1636035777%40sss.pgh.pa.us>
Precedence: bulk

"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 =3D direction_state_ol=
d

> 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 =3D 'DOWN'::direction_state_ol=
d 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