From: | Manuel Rigger <rigger(dot)manuel(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | Re: ALTER TABLE fails when changing column type due to index with bit_ops opclass |
Date: | 2019-11-25 20:22:50 |
Message-ID: | CA+u7OA5f6n90p53h5be+VcnsKqs8Jg4bZma_v2rTfEvQvfSswg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
I see, thanks for the explanation!
From my perspective, it is not necessary to document this, as probably
not many people would have such a use case.
Best,
Manuel
On Wed, Nov 20, 2019 at 7:16 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Manuel Rigger <rigger(dot)manuel(at)gmail(dot)com> writes:
> > Consider the following statements:
>
> > CREATE TABLE t0(c0 BIT VARYING(1));
> > CREATE INDEX i0 ON t0(c0 bit_ops);
> > ALTER TABLE t0 ALTER c0 TYPE TEXT; -- ERROR: operator class "bit_ops"
> > does not accept data type text
>
> > Altering the column type fails, which is somewhat unexpected, since it
> > does not seem to cause problems for other opclasses. For example, the
> > following executes without errors:
>
> > CREATE TABLE t0(c0 TEXT);
> > CREATE INDEX i0 ON t0(c0 text_ops);
> > ALTER TABLE t0 ALTER c0 TYPE BIT VARYING(1) USING c0::bit varying(1);
>
> > Is this a bug or expected?
>
> I think this is expected behavior, more or less. The critical difference
> is that you specified a non-default opclass in the first example (the
> default choice for that column datatype is varbit_ops not bit_ops).
> ALTER TABLE figures that it's okay to replace the default opclass for
> the original type with the default opclass for the new type, but it's
> not willing to guess about what you want if the index has a non-default
> opclass. So the conversion only goes through if the specified opclass
> also accepts the new datatype, which typically it wouldn't.
>
> This is probably not documented anyplace. Should it be? If so, what
> should we say and where?
>
> regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | PG Bug reporting form | 2019-11-25 21:00:07 | BUG #16137: pg_upgrade fails with an index over nesting function |
Previous Message | Marco Cuccato | 2019-11-25 15:35:28 | Re: LDAPS trusted ca support |