Thread: Using contrib/fulltext on multiple tables.
I am running into a problem with using contrib/fulltext on several tables. postgres 7.3.2, script attached. Here's the details... I have one sql script which drops everything and recreates the database structure, then populates it with some test data. In this script I create tables a,b and c and setup the triggers for fulltext indexing (as well as associated tables for the index data). 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. If I just have a and c setup in the script, they both work. Same for a and b. It seems to be a problem with b and c. Something else that is odd is I can then call the Insert stored procedure for c and it's index doesn't get created. However, if I exit and reenter the psql client app, THEN run the Insert stored procedure, the index is created just fine. I've tried breaking it into two scripts (one for db structure and one for test data) but it still doesn't do the indexing on c. Any Ideas? I apologize for the length, Eric
Attachment
"Eric Johnson" <ej@ejinnovations.com> 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
That's exactly what I meant. I'm getting 0 rows in tb_c_ingredient_fti. Thank you for looking into this, I'll keep poking around as to what could be the problem on my end (not really sure, it's a vanilla install). Thanks again, Eric ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Eric Johnson" <ej@ejinnovations.com> Cc: "Pgsql-Bugs" <pgsql-bugs@postgresql.org> Sent: Sunday, August 03, 2003 8:29 PM Subject: Re: [BUGS] Using contrib/fulltext on multiple tables. > "Eric Johnson" <ej@ejinnovations.com> 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 >
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" <tgl@sss.pgh.pa.us> To: "Eric Johnson" <ej@ejinnovations.com> Cc: "Pgsql-Bugs" <pgsql-bugs@postgresql.org> Sent: Sunday, August 03, 2003 8:29 PM Subject: Re: [BUGS] Using contrib/fulltext on multiple tables. > "Eric Johnson" <ej@ejinnovations.com> 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 >