Обсуждение: Error with citext extension when upgrading from 9.0.3 to 9.3.5: 'cannot cast type oid[] to oidvector'

Dear PostgreSQL developers,

We've encountered some errors when upgrading a database using the citext
extension from 9.0.3 to 9.3.5. Specifically, we run pg_upgrade, then
attempt to run 'CREATE EXTENSION citext FROM UNPACKAGED' to resolve
collation errors on our citext columns. This produces the error message
'ERROR:  cannot cast type oid[] to oidvector' when it runs the following
command from contrib/citext/citext--unpackaged--1.0.sql:

UPDATE pg_catalog.pg_index SET indcollation[0] = 100
WHERE indclass[0] IN (
  WITH RECURSIVE typeoids(typoid) AS
    ( SELECT 'citext'::pg_catalog.regtype UNION
      SELECT oid FROM pg_catalog.pg_type, typeoids
        WHERE typelem = typoid OR typbasetype = typoid )
  SELECT oid FROM pg_catalog.pg_opclass, typeoids
  WHERE opcintype = typeoids.typoid
);

We think this may be related to commit 7b63528, which fixes array slicing
of int2vector and oidvector values. The full message on the commit states
that this forbids array-element updates on oidvector columns. Additionally,
we have successfully upgraded to 9.3.1, which is the latest version of 9.3
that does not include this commit.

Please let us know if there's additional information or assistance we can
provide to help resolve this.

Thanks very much,
Eric Malm and Luan Santos, for the Pivotal CF Runtime team

Eric Malm <emalm@pivotal.io> writes:
> We've encountered some errors when upgrading a database using the citext
> extension from 9.0.3 to 9.3.5. Specifically, we run pg_upgrade, then
> attempt to run 'CREATE EXTENSION citext FROM UNPACKAGED' to resolve
> collation errors on our citext columns. This produces the error message
> 'ERROR:  cannot cast type oid[] to oidvector' when it runs the following
> command from contrib/citext/citext--unpackaged--1.0.sql:

Oh, that's annoying.

> We think this may be related to commit 7b63528,

Yeah, no doubt.  I don't think there's anything wrong with that commit,
but we'll need to make the citext upgrade script even more of a kluge
in order to dodge the new restriction.

I'm thinking about regexp_replace on the text form of the oidvector :-(.
Anybody have a better idea?

            regards, tom lane

I wrote:
> Eric Malm <emalm@pivotal.io> writes:
>> We think this may be related to commit 7b63528,

> Yeah, no doubt.  I don't think there's anything wrong with that commit,
> but we'll need to make the citext upgrade script even more of a kluge
> in order to dodge the new restriction.

I've pushed a fix for this, if you need it right away:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=7f7eec89b66947e4098773cf286653b9c4f01c88

That's against HEAD but it's the same in all the back branches.

            regards, tom lane

Dear Tom,

On Thu, Aug 28, 2014 at 3:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> I've pushed a fix for this, if you need it right away:
>
>
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=7f7eec89b66947e4098773cf286653b9c4f01c88
>

Wonderful, thanks so much for the quick fix! It isn't urgent for us to
upgrade immediately, so we'll likely wait for the fix to come out in the
next point release, but it's great to know where it is in case we do need
to apply it manually.

Thanks again,
Eric Malm, for the Pivotal CF Runtime team