Re: [BUGS] tsvector_update_trigger() fails in 9.6 on character(2) column

Matt Magoffin <postgresql.org@msqr.us> writes:
> I am migrating a 9.3 database to 9.6, and came across what looks like a possible regression somewhere in
tsvector_update_trigger()when passed a column of type character. The following works in 9.3: 

> CREATE TABLE public.foo (
>   foo_id integer NOT NULL,
>   country character(2) NOT NULL,
>    fts tsvector,
>   CONSTRAINT foo_pk PRIMARY KEY (foo_id)
> );
> CREATE TRIGGER maintain_fts
>   BEFORE INSERT OR UPDATE
>   ON public.foo
>   FOR EACH ROW
>   EXECUTE PROCEDURE tsvector_update_trigger('fts', 'pg_catalog.english', 'country');

> INSERT INTO public.foo (foo_id, country) VALUES (1, 'US');

> But the same thing in 9.6 results in :

> ERROR: column "country" is not of a character type

This is a consequence of commit 9acb9007de30b3daaa9efc16763c3bc6e3e0a92d,
which replaced a handwritten type-compatibility check with an
IsBinaryCoercible() test.  Since char(n) is in fact not binary-coercible
to text, IsBinaryCoercible() rejects this case.  (A char(n)-to-text cast
normally involves a function that strips trailing blanks.)

If we're willing to assume that tsvector never cares about trailing blanks
in its input string, then we could just allow the case, along the lines of

-       if (!IsBinaryCoercible(SPI_gettypeid(rel->rd_att, numattr), TEXTOID))
+       if (!IsBinaryCoercible(SPI_gettypeid(rel->rd_att, numattr), TEXTOID) &&
+           !IsBinaryCoercible(SPI_gettypeid(rel->rd_att, numattr), BPCHAROID))

Not entirely sure that it's worth the trouble, as this seems like a rather
strange use-case to me.

            regards, tom lane


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs