I just noticed something about how this is behaving. When I run the script
on my machines, it's putting the index data tb_c_ingredient into
tb_c_step_fti. If I exit psql, go back in and manually insert, it goes to
the correct table tb_c_ingredient_fti.
I wrote a much simpler script (see attachment: just 3 tables being indexed,
3 triggers, and 3 index-data tables) and am having the same problem - only
all the data is going into the first table's index until I logout and go
back in.
It may be worth noting the following error if I run the script multiple
times in the same psql session:
psql:bug.sql:66: ERROR: pg_class_aclcheck: relation 125816 not found
psql:bug.sql:67: ERROR: pg_class_aclcheck: relation 125816 not found
psql:bug.sql:68: ERROR: pg_class_aclcheck: relation 125816 not found
That's happening on the INSERT INTO table1 ... table2 ... and table3
statements.
Eric
----- Original Message -----
From: "Tom Lane" <[EMAIL PROTECTED]>
To: "Eric Johnson" <[EMAIL PROTECTED]>
Cc: "Pgsql-Bugs" <[EMAIL PROTECTED]>
Sent: Sunday, August 03, 2003 8:29 PM
Subject: Re: [BUGS] Using contrib/fulltext on multiple tables.
> "Eric Johnson" <[EMAIL PROTECTED]> writes:
> > Later in the script when inserting into these tables via stored
procedures,
> > the full text indexes get created for a and b but not c. It's just
empty.
>
> I can't reproduce this in either 7.3 or CVS tip. The index tables seem
> to have reasonable numbers of entries in them after running your script:
>
> recipe=# select count(*) from tb_c_step_fti;
> count
> -------
> 41
> (1 row)
>
> recipe=# select count(*) from tb_c_recipe_fti;
> count
> -------
> 207
> (1 row)
>
> recipe=# select count(*) from tb_c_ingredient_fti;
> count
> -------
> 103
> (1 row)
>
> If that wasn't what you meant, you'll need to be more specific.
>
> regards, tom lane
>
DROP TRIGGER one_fti_trigger on table1;
DROP TRIGGER two_fti_trigger on table2;
DROP TRIGGER three_fti_trigger on table3;
DROP FUNCTION fti() CASCADE;
--DROP INDEX one_fti_string_idx;
--DROP INDEX one_fti_id_idx;
--DROP INDEX one_fti_oid_idx;
--DROP INDEX two_fti_string_idx;
--DROP INDEX two_fti_id_idx;
--DROP INDEX two_fti_oid_idx;
--DROP INDEX three_fti_string_idx;
--DROP INDEX three_fti_id_idx;
--DROP INDEX three_fti_oid_idx;
DROP TABLE table1;
DROP TABLE table2;
DROP TABLE table3;
DROP TABLE table1_fti;
DROP TABLE table2_fti;
DROP TABLE table3_fti;
CREATE FUNCTION fti() RETURNS OPAQUE AS '/usr/lib/postgresql/lib/fti.so' LANGUAGE 'C';
-- Create tables
CREATE TABLE table1(id int4, label VARCHAR(64));
CREATE TABLE table2(id int4, label VARCHAR(64));
CREATE TABLE table3(id int4, label VARCHAR(64));
CREATE TABLE table1_fti(string VARCHAR(5120), id oid);
CREATE TABLE table2_fti(string VARCHAR(4351), id oid);
CREATE TABLE table3_fti(string VARCHAR(1000), id oid);
-- Triggers for FTI
CREATE TRIGGER "one_fti_trigger" AFTER UPDATE OR INSERT OR DELETE
ON table1 FOR EACH ROW
EXECUTE PROCEDURE fti(table1_fti, label);
CREATE TRIGGER "two_fti_trigger" AFTER UPDATE OR INSERT OR DELETE
ON table2 FOR EACH ROW
EXECUTE PROCEDURE fti(table2_fti, label);
CREATE TRIGGER "three_fti_trigger" AFTER UPDATE OR INSERT OR DELETE
ON table3 FOR EACH ROW
EXECUTE PROCEDURE fti(table3_fti, label);
-- Indexes for FTI
--CREATE INDEX one_fti_string_idx ON table1_fti(string);
--CREATE INDEX one_fti_id_idx ON table1_fti(id);
--CREATE INDEX one_fti_oid_idx ON table1_fti(oid);
--CREATE INDEX two_fti_string_idx ON table2_fti(string);
--CREATE INDEX two_fti_id_idx ON table2_fti(id);
--CREATE INDEX two_fti_oid_idx ON table2_fti(oid);
--CREATE INDEX three_fti_string_idx ON table3_fti(string);
--CREATE INDEX three_fti_id_idx ON table3_fti(id);
--CREATE INDEX three_fti_oid_idx ON table3_fti(oid);
INSERT INTO table1 values (1, 'test');
INSERT INTO table2 values (1, 'test');
INSERT INTO table3 values (1, 'test');
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
