DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=mime-version:in-reply-to:references:date:message-id:subject:from:to
	:cc:content-type;
	b=JhgF3u0leBKrtzTjw7fX8iH3roNDQDn1esTiLN3OHjzrIawvQKCAcADLC6wJHW/gTV
	5BHLrBeI0cWzoe+0/w/cs/wHWm5zeOstrRyTGRTyQtm7VWvHH4DTgPZRIjMCef3CamYL
	/R55lWr7EzxlVgqW3MxkNO03VVsnhvnCCzE0w=
MIME-Version: 1.0
In-Reply-To: <BANLkTi=4EaMpXu7orXiAHOiA2AS0w9k_Yg@mail.gmail.com>
References: <BANLkTi=hAv876hXEru6FO8PNGWFNYB21ZA@mail.gmail.com>
	<4DF7275C020000250003E59F@gw.wicourts.gov>
	<BANLkTikz+MY1DoO5UcxzJbG+VNgGKn0HFg@mail.gmail.com>
	<4DF7399B020000250003E5AB@gw.wicourts.gov>
	<BANLkTi=4EaMpXu7orXiAHOiA2AS0w9k_Yg@mail.gmail.com>
Date: Tue, 14 Jun 2011 16:42:49 -0400
Message-ID: <BANLkTiksUZuiKrtbsDfUkfYUU=zMEgK2cg@mail.gmail.com>
Subject: Re: tsvector limitations
From: Tim <elatllat@gmail.com>
To: pgsql-admin@postgresql.org
Cc: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Content-Type: multipart/alternative; boundary=bcaec521555343b97304a5b2148c

--bcaec521555343b97304a5b2148c
Content-Type: text/plain; charset=ISO-8859-1

 So I ran this test:
unzip -p text.docx word/document.xml | perl -p -e
's/<.+?>/\n/g;s/[^a-z0-9\n]/\n/ig;'|grep ".." > text.txt
ls -hal ./text.*
#-rwxrwxrwx 1 postgres postgres 15M 2011-06-14 15:12 ./text.docx
#-rwxrwxrwx 1 postgres postgres 29M 2011-06-14 15:17 ./text.txt
mv /tmp/text.* /var/lib/postgresql/9.0/main/
cd ~/;psql -d postgres
#psql (9.0.4)
CREATE DATABASE test;
\q
cd ~/;psql -d test
CREATE TABLE test(title VARCHAR(256), data OID, words TSVECTOR);
INSERT INTO test VALUES (  'text.docx',  LO_IMPORT('text.docx'),
TO_TSVECTOR(pg_read_file('text.txt' ,0, 100000000))  );

and I got this:
 #ERROR:  string is too long for tsvector (30990860 bytes, max 1048575
bytes)

doing the math
echo "scale=3;29/(30990860/1048575)"|bc #==0.981

Indicates a (worst case) limit of searching only the first 1MB of a text
file before you need to start building your own search maybe on top of
tsvector.
The year is 2011 I don't think searching a 2MB text file is to much to
expect.
So how am I to use the PGSQL FTS as a "full text search" when the above
example can only handle a "small or partial text search"?
If I'm not missing anything maybe the documentation should be adjusted
accordingly.

Any thoughts or alternatives are most welcome.

--bcaec521555343b97304a5b2148c
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable


So I ran this test:<br><div style=3D"margin-left: 40px;">unzip -p text.docx=
 word/document.xml | perl -p -e &#39;s/&lt;.+?&gt;/\n/g;s/[^a-z0-9\n]/\n/ig=
;&#39;|grep &quot;..&quot; &gt; text.txt<br>
ls -hal ./text.*<br>#-rwxrwxrwx 1 postgres postgres 15M 2011-06-14 15:12 ./=
text.docx<br>#-rwxrwxrwx 1 postgres postgres 29M 2011-06-14 15:17 ./text.tx=
t<br>mv /tmp/text.* /var/lib/postgresql/9.0/main/<br>cd ~/;psql -d postgres=
<br>
#psql (9.0.4)<br>CREATE DATABASE test;<br>\q<br>cd ~/;psql -d test<br>CREAT=
E TABLE test(title VARCHAR(256), data OID, words TSVECTOR);<br>INSERT INTO =
test VALUES (=A0 &#39;text.docx&#39;,=A0 LO_IMPORT(&#39;text.docx&#39;),=A0=
 TO_TSVECTOR(pg_read_file(&#39;text.txt&#39; ,0, 100000000))=A0 );<br>
<br></div>and I got this:<br>
<div style=3D"margin-left: 40px;">

#ERROR:=A0 string is too long for tsvector (30990860 bytes, max 1048575 byt=
es)<br><br></div>doing the math <br><div style=3D"margin-left: 40px;">echo =
&quot;scale=3D3;29/(30990860/1048575)&quot;|bc #=3D=3D0.981<br></div><br>In=
dicates a (worst case) limit of searching only the first 1MB of a text file=
 before you need to start building your own search maybe on top of tsvector=
.<br>
The year is 2011 I don&#39;t think searching a 2MB text file is to much to =
expect.<br>So how am I to use the PGSQL FTS as a &quot;full text search&quo=
t; when the above example can only handle a &quot;small or partial text sea=
rch&quot;?<br>
If I&#39;m not missing anything maybe the documentation should be adjusted =
accordingly.<br><br>Any thoughts or alternatives are most welcome. <br>

--bcaec521555343b97304a5b2148c--