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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Matt Magoffin <postgresql(dot)org(at)msqr(dot)us>
Cc: pgsql-bugs(at)postgresql(dot)org, Teodor Sigaev <teodor(at)sigaev(dot)ru>
Subject: Re: tsvector_update_trigger() fails in 9.6 on character(2) column
Date: 2017-03-09 03:34:46
Message-ID: 24975.1489030486@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Matt Magoffin <postgresql(dot)org(at)msqr(dot)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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Matt Magoffin 2017-03-09 07:32:19 Re: tsvector_update_trigger() fails in 9.6 on character(2) column
Previous Message Matt Magoffin 2017-03-09 03:17:50 tsvector_update_trigger() fails in 9.6 on character(2) column