Postgres Pro
Downloads
Home   >   mailing lists

Thread: tsearch consistency trigger on inheritated table

tsearch consistency trigger on inheritated table

From
"Bjoern Metzdorf"
Date:
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

Re: tsearch consistency trigger on inheritated table

From
Tom Lane
Date:
"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

Re: tsearch consistency trigger on inheritated table

From
Björn Metzdorf
Date:
> > 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

Re: tsearch consistency trigger on inheritated table

From
Björn Metzdorf
Date:
> 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

Re: tsearch consistency trigger on inheritated table

From
Björn Metzdorf
Date:
> 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
Attachment

Re: tsearch consistency trigger on inheritated table

From
Tom Lane
Date:
=?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