Обсуждение: 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

"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
>