On Thu, Nov 18, 2010 at 6:28 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Allan Kamau <kamauallan@gmail.com> writes:
>> CREATE TABLE farm.produce
>> (id INTEGER NOT NULL DEFAULT NEXTVAL('farm.produce_seq')
>> ,process___id TEXT NOT NULL
>> ,item_names tsvector NULL
>> ,product__ids__tsquery tsquery NULL
>> ,product__ids__tsvector tsvector NULL
>> ,population_time TIMESTAMP NOT NULL DEFAULT clock_timestamp()
>> ,PRIMARY KEY(id)
>> )
>
>> It seems the tsvector field did not contribute to this error as the
>> error occurred when I attempted to populate the tsquery field with
>> rather long tsquery data. Without populating the tsvector field but
>> got the same error with the same size message indicating that the data
>> in the tsvector fields do not lead to this problem.
>
> Yeah, on poking into the system catalogs I see that tsquery is declared
> as not supporting toasting (it has typstorage = 'p'lain). I don't
> offhand know the implementation reason for that or whether it would be
> a good idea to change it. But obviously Teodor's expectation was that
> nobody would ever want to store large tsqueries on disk. I guess it
> would be worth asking what's your use-case for storing tsquery, as
> opposed to tsvector?
>
> regards, tom lane
>
I do have two tables in a many to many relationship, let's call these
tables 'A' and 'B'. I am supposed to obtain all possible interactions
between tuples in 'A' (cross join), where such interaction is via
common or shared tuple(s) of 'B'. It somehow ends up been a recursive
interaction detection exercise because the actual requirement is
finding for any group of tuples in 'A' all the tuples in 'B' that they
share.
Being a non trivial exercise I was hoping to also test performance
without making use of a joining table and instead store the ids of all
tuples of 'B' related to a given tuple 'A' in the specific tuple of
'A' where such ids values of 'B' will be stored as ored as tsquery.
Then introduce another field in 'B' of tsquery type having the single
value of the records id field. Then for each crossjoin of tuples in
'A' I would use the tsquery from either side of this join to query on
the tsvector of table 'A'.
Similar implementation using a joining table is currently under way.
Regards,
Allan.
