Обсуждение: text searching in postgres

Hello folx!

I was in the process of building a an app that would use lucene to query
a pg database when a colleague told me about a couple of projects that
allegedly add the functionality I was intending to implement directly to
pg, tsearch/OpenFTS and fti.

I'm hoping some of you might have used these modules and could offer
your opinions and experiences.

thanks

-bill

On 30 Jan 2003, Bill wrote:

[...]
> allegedly add the functionality I was intending to implement directly to
> pg, tsearch/OpenFTS and fti.
[...]

I use tsearch alone without relevants ranking offered by OpenFTS, and it's
blazzingly fast and simple. My project involved a yellow page directory of
around 250,000 entries.

Bill wrote:
> Hello folx!
>
> I was in the process of building a an app that would use lucene to query
> a pg database when a colleague told me about a couple of projects that
> allegedly add the functionality I was intending to implement directly to
> pg, tsearch/OpenFTS and fti.
>
> I'm hoping some of you might have used these modules and could offer
> your opinions and experiences.

Hi Bill,

It's probably a good idea to join the OpenFTS (openfts.sf.net) mailing
list and ask there too:

http://lists.sourceforge.net/lists/listinfo/openfts-general

Haven't personally use OpenFTS, though at least two of the main
developers of OpenFTS, Oleg Bartunov and Teodor Sigaev, have been active
contributors to PostgreSQL over the years so it probably integrates
pretty well.

Hope this helps.

:-)

Regards and best wishes,

Justin Clift


> thanks
>
> -bill


--
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
- Indira Gandhi

zzzz=# select version();
                                             version
------------------------------------------------------------------------
------------------------
  PostgreSQL 7.3 on powerpc-apple-darwin6.3, compiled by GCC gcc (GCC)
3.1 20020420 (prerelease)
(1 row)

Being too lazy to RTFM, I decided to discover on my own if postgres
supported 2D array types:

zzzz=# create table foo (bar int8[][]);
CREATE TABLE

(Oh cool!  it does!)

zzzz=# \d foo
       Table "public.foo"
  Column |   Type   | Modifiers
--------+----------+-----------
  bar    | bigint[] |

(hmm, that doesn't look right.  <pause>  *snicker*  Hmm, I wonder...)

zzzz=# create table foo2 (bar int8[][][][][][][][]);
CREATE TABLE
zzzz=# \d foo2
       Table "public.foo2"
  Column |   Type   | Modifiers
--------+----------+-----------
  bar    | bigint[] |

So now my question is, since postgres doesn't support 2D (or N-D)
arrays, shouldn't the above produce some kind of syntax error?

eric

"Eric B.Ridge" <ebr@tcdi.com> writes:
> So now my question is, since postgres doesn't support 2D (or N-D)
> arrays, shouldn't the above produce some kind of syntax error?

Postgres' view of arrays is inherited from APL, I think: all arrays
of the same element type are the same datatype regardless of
dimensionality.  We do have N-dimensional arrays, for N up to whatever
the arbitrary MAXDIM constant is.  See the examples in the User's Guide.

            regards, tom lane

While testing this out I discovered somthing interesting.

Here was my test :

create table foo (bar int8[]);
insert into foo values('{12423}');
insert into foo values('{{1223,4563},{3623}}');
insert into foo values('{{123},{3423}}');
insert into foo values('{{12323,45363},{5443,23}}');
insert into foo values('{{123,456,768},{543,323,235},{234,456,654}}');
select array_dims(bar),bar from foo ;

The result was :

  array_dims |                     bar
------------+---------------------------------------------
  [1:1]      | {12423}
  [1:2][1:1] | {{1223},{3623}}
  [1:2][1:1] | {{123},{3423}}
  [1:2][1:2] | {{12323,45363},{5443,23}}
  [1:3][1:3] | {{123,456,768},{543,323,235},{234,456,654}}
(5 rows)

What happened to the '4563' on the second insert statement?

If you have less dimensions filled out as an array of arrays does postgres
default to the minimum dimension of the array data rather than put a NULL
entry to fill in additional entries?

When I tried to force the data I recieved somewhat expected results :

test=$ insert into foo values('{{1223,4563},{3623,}}');
ERROR:  Bad int8 external representation ""

test=$ insert into foo values('{{1223,4563},{3623,NULL}}');
ERROR:  Bad int8 external representation "NULL"

test=$ insert into foo values('{{1223,4563},{3623,0}}');
INSERT 65261 1


Guy

Eric B.Ridge wrote:
> zzzz=# select version();
>                                             version
> ------------------------------------------------------------------------
> ------------------------
>  PostgreSQL 7.3 on powerpc-apple-darwin6.3, compiled by GCC gcc (GCC)
> 3.1 20020420 (prerelease)
> (1 row)
>
> Being too lazy to RTFM, I decided to discover on my own if postgres
> supported 2D array types:
>
> zzzz=# create table foo (bar int8[][]);
> CREATE TABLE
>
> (Oh cool!  it does!)
>
> zzzz=# \d foo
>       Table "public.foo"
>  Column |   Type   | Modifiers
> --------+----------+-----------
>  bar    | bigint[] |
>
> (hmm, that doesn't look right.  <pause>  *snicker*  Hmm, I wonder...)
>
> zzzz=# create table foo2 (bar int8[][][][][][][][]);
> CREATE TABLE
> zzzz=# \d foo2
>       Table "public.foo2"
>  Column |   Type   | Modifiers
> --------+----------+-----------
>  bar    | bigint[] |
>
> So now my question is, since postgres doesn't support 2D (or N-D)
> arrays, shouldn't the above produce some kind of syntax error?
>
> eric
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>