Обсуждение: [BUGS] tsvector_update_trigger() fails in 9.6 on character(2) column

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

I traced this back to tsvector_update_trigger() because if I disable that trigger the INSERT statement will succeed.

If I change the column to character varying(2), then the INSERT succeeds as well.

Kind regards,
Matt

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

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

> On 9/03/2017, at 4:34 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Not entirely sure that it's worth the trouble, as this seems like a rather
> strange use-case to me.

Thanks for the info, Tom. I can speak to the use-case in my situation, where the char(2) column is but one of many
columnsadded to a tsvector column via tsvector_update_trigger(), so that general user-entered search queries can
execute(quickly) against the tsvector column alone. 

Kind regards,
Matt

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