Обсуждение: tsearch consistency trigger on inheritated table

Hi,

The trigger from README:

create trigger txtidxupdate before update or insert on titles
 for each row execute procedure tsearch(titleidx, title);

When using this trigger on an inheritated table (with "title" being an
inheritated column), an insert or update on the table fails stating:

NOTICE:  TSearch: can not find field 'title'
ERROR:  ExecAppend: Fail to add null value in not null attribute created

Is this possible or did I mess everything completely up?

Btw, using txt2txtidx on all rows and selecting with ## and @@ works fine,
it is just the trigger.

Regards,
Bjoern

"Bjoern Metzdorf" <bm@turtle-entertainment.de> writes:
> When using this trigger on an inheritated table (with "title" being an
> inheritated column), an insert or update on the table fails stating:
> NOTICE:  TSearch: can not find field 'title'
> ERROR:  ExecAppend: Fail to add null value in not null attribute created

Hm, I cannot see how it would matter whether the column is inherited or
not.  Could you provide a self-contained example of what you're doing?

            regards, tom lane

> > When using this trigger on an inheritated table (with "title" being an
> > inheritated column), an insert or update on the table fails stating:
> > NOTICE:  TSearch: can not find field 'title'
> > ERROR:  ExecAppend: Fail to add null value in not null attribute created
>
> Hm, I cannot see how it would matter whether the column is inherited or
> not.  Could you provide a self-contained example of what you're doing?

Well, it turned out that column "title" was "character(100)" and not "text".
It seems that function tsearch depends on columns being "text". Is there
some way to circumwent this? txt2txtidx works with "character" too.

Regards,
Bjoern

> Well, it turned out that column "title" was "character(100)" and not
"text".
> It seems that function tsearch depends on columns being "text". Is there
> some way to circumwent this? txt2txtidx works with "character" too.

The corresponding code part in txtidx.c:

if (numattr < 0 || (!(oidtype == TEXTOID || oidtype == VARCHAROID)))
{
   elog(NOTICE, "TSearch: can not find field '%s'", trigger->tgargs[i]);
   continue;
}

Question is, do I break anything if I add "|| oidtype == CHARACTEROID" ?

Regards,
Bjoern

> The corresponding code part in txtidx.c:
>
> if (numattr < 0 || (!(oidtype == TEXTOID || oidtype == VARCHAROID)))
> {
>    elog(NOTICE, "TSearch: can not find field '%s'", trigger->tgargs[i]);
>    continue;
> }
>
> Question is, do I break anything if I add "|| oidtype == CHARACTEROID" ?

Attached a quick hack to txtidx.c (added BPCHAROID).

Hope this does not break things. Comments?

Regards,
Bjoern

=?Windows-1252?Q?Bj=F6rn_Metzdorf?= <bm@turtle-entertainment.de> writes:
> --- txtidx.c-orig       Wed Nov 13 17:15:57 2002
> +++ txtidx.c    Wed Nov 13 17:33:15 2002
> @@ -554,7 +554,7 @@

>                 numattr =3D SPI_fnumber(rel->rd_att, trigger->tgargs[i]);
>                 oidtype =3D SPI_gettypeid(rel->rd_att, numattr);
> -               if (numattr < 0 || (!(oidtype =3D=3D TEXTOID || oidtype =3D=
> =3D VARCHAROID)))
> +               if (numattr < 0 || (!(oidtype =3D=3D TEXTOID || oidtype =3D=
> =3D VARCHAROID || oidtype =3D=3D BPCHAROID)))
>                 {
>                         elog(NOTICE, "TSearch: can not find field '%s'", tr=
> igger->tgargs[i]);
>                         continue;

It would probably be better to have two different error messages for
the "field not found" and "field not right type" cases.

BTW, "character(100)" screams "lots of wasted space" to me.  Why aren't
you using varchar, anyway?

            regards, tom lane