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 createdHm, 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
Attachments:
tsearch_txtidx.c.patchapplication/octet-stream; name=tsearch_txtidx.c.patchDownload+1-1
=?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