Re: [GENERAL] Tsearch2 / Create rule on select
On Fri, 9 Mar 2007, Jean-Michel Pour? wrote: Le vendredi 09 mars 2007 ЪЪ 10:58 +0100, Magnus Hagander a ЪЪcrit : No idea. Assuming you want to do it beforehand. otherwise, just create the index and see how large it got? Thank you for your comments. I will add TSeach2 support to phpBB 3.x soon. I had incredible response time on simple queries on more than plain-text 200.000 row. About 1 millisecond! Incredible but true. explain analyse verbose SELECT * FROM phpbb_posts_text WHERE idxfti @@ 'jmp'::tsquery limit 100; Limit (cost=0.00..444.47 rows=100 width=934) (actual time=0.046..0.824 rows=100 loops=1) - Index Scan using idxfti_idx on phpbb_posts_text (cost=0.00..1053.38 rows=237 width=934) (actual time=0.040..0.418 rows=100 loops=1) Index Cond: (idxfti @@ '''jmp'''::tsquery) [color=red]Total runtime: 1.068 ms TSearch2 will be used by wikimedia shortly (it is supported in beta version). I've already seen tsearch2 in 1.9.X release. IMHO, it would be better if PostgreSQL parser was able to find Tseach indexes alone, rewritting the query automatically. Maybe it is on the radar list of hackers. btw, we're working on new FTS feature of PostgreSQL, it's basically tsearch integrated into the pg core. But it has some new features and new SQL commands for configuring of FTS, so if the matter is not pressing I'd recommend to check http://mira.sai.msu.su/~megera/pgsql/ftsdoc Kind regards, Jean-Michel ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] GIST index on geometry+integer
Try install contrib/btree_gist On Thu, 8 Mar 2007, Arnaud Lesauvage wrote: Hi list ! I would like to test clustering on a multicolumn GIST index. The first column is a PostGIS-geometry field, the second column is a smallint field. When I try to create the index, I have an error about GIST not being available for smallint datatype. How can I create this operator class ? Thanks for your help ! Regards -- Arnaud ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Slow running to_tsvector (tsearch2 in PG 8.2.3)
On Wed, 21 Feb 2007, Henrik Zagerholm wrote: Hello list, I've been testing tsearch2 for a while and I recently noticed some really slow queries. This is a quite big document so the times are maybe accurate. The document has about 194 000 words. I put all the data in tbl_fulltext.fulltext_text and then did a update tbl_fulltext set vectors = to_tsvector(fulltext_text); Which takes about 80 seconds to complete. The hardware is a Pentium 4 2.8GHz with 1GB HyperX memory. Is this normal? What can I tweak in postgresql.conf to speed up big to_tsvector()? What is your configuration ? to_tsvector does a lot of work. Regards, Henrik ---(end of broadcast)--- TIP 6: explain analyze is your friend Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] tsearch2 parser configuration
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/HOWTO-parser-tsearch2.html On Tue, 6 Feb 2007, Worky Workerson wrote: Is it possible to configure the tsearch2 parser? I'd like a very dumb parser that splits on everything that is not [a-zA-Z0-9.]. The default parser seems to work well on my dataset except for the '/' character ... it doesn't split mike/john into two lexemes. And ideas? Thanks! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] simple dict with stop words in tsearch2
On Mon, 29 Jan 2007, Pierre Thibaudeau wrote:
In tsearch2, I would like to use the simple dictionary along with my
own list of stopwords.
In other words, once the text is parsed into tokens, no stemming
whatsoever, but stopwords are removed.
Is there an easy way to produce that result, using the standard
simple dictionary?
sure, just specify dict_initoption. For example,
test=# update pg_ts_dict set dict_initoption='contrib/english.stop' where dict_name='simple';
UPDATE 1
test=# select lexize('simple', 'the');
lexize
{}
(1 row)
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Avoiding empty queries in tsearch
Doug,
numnode exists for sure ! It's plainto_tsquery function which
Teodor used in example, appeared in 8.2
Oleg
On Mon, 15 Jan 2007, Doug Cole wrote:
That sounds perfect, but it doesn't seem to exist on either of the
postgresql installations I have access to (8.1 on ubuntu and fedora
core). Is it new to 8.2? Is there a similar function under 8.1, or
at least a decent work-around? Thanks for the help,
Doug
On 1/15/07, Teodor Sigaev [EMAIL PROTECTED] wrote:
contrib_regression=# select numnode( plainto_tsquery('the any') );
NOTICE: query contains only stopword(s) or doesn't contain
lexeme(s), ignored
numnode
-
0
(1 row)
contrib_regression=# select numnode( plainto_tsquery('the table') );
numnode
-
1
(1 row)
contrib_regression=# select numnode( plainto_tsquery('long table') );
numnode
-
3
(1 row)
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW:
http://www.sigaev.ru/
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.2.1 TSearch changes?
On Tue, 9 Jan 2007, Hannes Dorbath wrote: Several /contrib/tsearch2 fixes (Teodor) Is there a more detailed list available? fixes thesaurus dictionary and rank function. Does it obsolete tsearch_snowball_82.gz? No, it's still needed. This is incompatible change, so we can't apply it for minor version. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Does pgsql's regex processor optimize Common-Prefix?
Kurapica, I'd use contrib/pg_trgm for your application. Олег On Tue, 26 Dec 2006, Alvaro Herrera wrote: Kurapica wrote: I am developing an application which searches for city names in a column. There is a lot of cities and I have to 'like' every name which is not effective enough. So I want to know whether pgsql's regex processor can optimize regexes such as: Nebraska|Nevada|North Carolina to N(e(braska|vada)|orth Carolina) If the processor can do that like a Dictionary-Tree, it may be affordable to me or else I have to write a matcher myself. Any suggestion is appreciated. Thank you and appologize for my poor English. Compared to the use of indexes to skip whole table scanning, this optimization is going to have very little impact. So don't worry about it. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Let's play bash the search engine
On Tue, 19 Dec 2006, Magnus Hagander wrote:
On Tue, Dec 19, 2006 at 01:48:22PM +0530, Gurjeet Singh wrote:
On 12/19/06, Henrik Zagerholm [EMAIL PROTECTED] wrote:
Hello,
Searching after tsearch
5. PostgreSQL: Documentation: Manuals: PostgreSQL 7.4: Examples [0.1]
...tsearch and tsearch2Full text
indexingPrevHomeNextLimitationsUpPage Files User Comments No comments
could be found for this...
http://www.postgresql.org/docs/7.4/interactive/examples.html
Searching after tsearch2
An error occured while searching.
Searching after tsearch2full
An error occured while searching.
This error can be generalized to the reg-ex [::alpha::]+[::digit::]+
Examples:
A1
A2 etc...
Why is it so? =)
Seems to_tsvecto() returns NULL for tsearch2 or for, as you say,
anything that ends in a digit.
Oleg, can you comment on why this is happening? What can we do to fix
that?
Most probably, token type 'word' just doesn't indexed. If you
didnt' correct this from pgweb configuration:
-- we won't index/search some tokens
update pg_ts_cfgmap set dict_name = NULL
where tok_alias in ('email', 'url', 'sfloat', 'uri', 'float','word')
and ts_name = 'pg';
//Magnus
---(end of broadcast)---
TIP 6: explain analyze is your friend
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Let's play bash the search engine
On Tue, 19 Dec 2006, Alvaro Herrera wrote: Matthew O'Connor wrote: Magnus Hagander wrote: But seriously, I'm definitly interested in ways it can be improved - and that's true of the whole web team, I'm sure. It was just my way of saying it will take a while, but I'll file it away as a good thing to do when there is a moment of spare time. I like the way the php.net homepage has a search box on the homepage with a dropdown next to it to specify what to search. Yeah, that would be very appropriate, allowing you to search specific version of the docs. Heck, if it allowed searching of specific mail lists, that would rock. It should be pretty easy once documents has apropriate metadata. Also, displaying current section in search box would be informative, so when you're in current documentation, pull-down menu should display 8.2 Documentation. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Let's play bash the search engine
On Tue, 19 Dec 2006, Gurjeet Singh wrote: On 12/19/06, Matthew O'Connor matthew@zeut.net wrote: Magnus Hagander wrote: But seriously, I'm definitly interested in ways it can be improved - and that's true of the whole web team, I'm sure. It was just my way of saying it will take a while, but I'll file it away as a good thing to do when there is a moment of spare time. I like the way the php.net homepage has a search box on the homepage with a dropdown next to it to specify what to search. I would recommend a set of check-boxes, so that user can select multiple places to search. Eg. search in 8.2 release, 8.0 release, ans as just suggested by Alvaro, pgsql-hackers mailing list also. Too many check-boxes :) Better to have pull-down menu with multiple selections. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Let's play bash the search engine
On Tue, 19 Dec 2006, Filip Rembiakowski wrote: 2006/12/19, Joshua D. Drake [EMAIL PROTECTED]: Take a look at let us know what you think and how it performs for you. http://search.postgresql.org/search?q=HAVING says An error occured while searching. I bet HAVING is a stop-word, so actual message is 'NOTICE: query contains only stopword(s) or doesn't contain lexeme(s), ignored' I think we should add to pg_dict dictionary line having having This will prevent 'having' be recognized as a stop-word by other dictionaries, which follow pg_dict dictionary. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Let's play bash the search engine
On Tue, 19 Dec 2006, Thomas H. wrote: http://search.postgresql.org/search?q=HAVING says An error occured while searching. I bet HAVING is a stop-word, so actual message is 'NOTICE: query contains only stopword(s) or doesn't contain lexeme(s), ignored' I think we should add to pg_dict dictionary line having having just a though... wouldn't it make sense for a documentation search index to *not* have stop words at all? potentially every word that is being searched for could be contained in a query example, code piece etc and thus seems important to me... for example keywords like AND, OR etc. Ah, I forgot about them. Now, with GiN we could definitely try stop-words free search ! - thomas Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Tsearch2 / PG 8.2 Which stemmer files?
Hannes, please download patch tsearch_snowball_82.gz http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ which updates API to snowball. Oleg On Thu, 7 Dec 2006, Hannes Dorbath wrote: Which stemmer files is one supposed to use with 8.2 Tsearch2? Trying to compile the output from Gendict with: stem_UTF_8_german.c stem_UTF_8_german.h from: http://snowball.tartarus.org/dist/libstemmer_c.tgz gives: http://hannes.imos.net/make.txt Thanks! Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] tsearch2: pg8.1 to pg8.2
Rick,
did you load tsearch2 itself into your database ?
Oleg
On Thu, 7 Dec 2006, Rick Schumeyer wrote:
I am trying to copy a database that uses tsearch2 from pg8.1 to pg8.2.
All I did to install tsearch2 was cd to the contrib/tsearch2 directory, then
make, make install.
I then dumped the database from pg8.1 and used psql -f filename db to load in
into pg8.2.
Attempting a query gives an error:
lib2=# select * from item where idxTitle @@ to_tsquery('default', 'money');
ERROR: No dictionary with name 'en_stem'
Is there a document that describes the necessary steps to convert to the
upgraded tsearch2?
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] unaccent as stored procedure?
On Mon, 4 Dec 2006, Stephen Woodbridge wrote:
Hi all,
I was wondering if anyone has unac.c which is the lib used in Text::Unaccent
built and wrap as a plpgsql stored procedure not using plperl. Or maybe there
is another general solution that I am no aware of.
I have one, don't remember whet did I get it.
Thanks,
-Steve
---(end of broadcast)---
TIP 6: explain analyze is your friend
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83/*
* pg_unac.c
*
* Author: Nhan NGO DINH
* Version: 1.1
* Description: PostgreSQL external function
* unaccent the given string
*
*/
#include stdio.h
#include postgres.h
#include string.h
#include fmgr.h
#include unac.h
PG_FUNCTION_INFO_V1(unac);
Datum unac(PG_FUNCTION_ARGS) {
text *str = PG_GETARG_TEXT_P(0);
text *result;
int tlen, nlen;
char *tstr, *nstr;
tlen = VARSIZE(str) - VARHDRSZ;
tstr = (char *) palloc(tlen + 1);
memcpy(tstr, VARDATA(str), tlen);
tstr[tlen] = '\0';
nstr = NULL; nlen = 0;
unac_string(UTF-8, tstr, strlen(tstr), nstr, nlen);
/* It may happen that unac_string returns NULL, because iconv
* can't translate the input string. In this case we output
* the string as it is. */
if (nstr == NULL) nstr = tstr;
result = (text *) palloc(strlen(nstr) + VARHDRSZ);
memcpy(VARDATA(result), nstr, strlen(nstr));
VARATT_SIZEP(result) = strlen(nstr) + VARHDRSZ;
PG_RETURN_TEXT_P(result);
}
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Fix for 8.2 release. Was: [GENERAL] Problems to create the portuguese dictionary
Hello, we just released fix for 8.2 release, which updates Snowball API. Patch is available from http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/tsearch_snowball_82.gz It's too late to apply fix to 8.2 release, sorry. Oleg On Fri, 1 Dec 2006, Luiz Claudio da Silva Le?o wrote: Hi, I am trying to create de protuguese dictionary in order to use tserch2. The steps I followed are described below: 1) Login system as user1 2) cd 3) tar -xvzf postgresql-8.1.5.tar.gz 4) cd postgresql-8.1.5 5) ./configure 6) gmake 7) su 8) gmake install 9) adduser postgres 10) mkdir /usr/local/pgsql/data 11) chown postgres /usr/local/pgsql/data 12) su - postgres 13) /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data --locale=pt_BR.utf8 14) /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data logfile 21 15) /usr/local/pgsql/bin/createdb test 16) /usr/local/pgsql/bin/psql test Postgresql working. 17) \q (inside psql) 18) exit 19) cd /home/user1/postgresql-8.1.5/contrib/tsearch2 20) gmake 21) gmake install Tsearch OK. 22) cd ./gendict 23) wget http://snowball.tartarus.org/algorithms/portuguese/stem.c 24) wget http://snowball.tartarus.org/algorithms/portuguese/stem.h 25) ./config -n pt -s -p portuguese_ISO_8859_1 -v -C'Snowball stemmer for Portuguese' 26) cd ../../dict_pt 27) make Now I receive a lot of erros... I think there are differences on interfaces involving snowball and tsearch2. Does anybody know how to create a new dictionary or have old versions of stem.c and stem.h for portuguese? Thanks, Luiz Claudio Leao ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] GiN for 8.1 patch updated
Hello, we just updated patch for 8.1 release, which introduced GiN (Generalized Inverted Index) with tsearch2 support and full multibyte support (UTF-8 as well). It contains VACUUM fix in GiN code. Patch is available from Tsearch2 page http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Problems to create the portuguese dictionary
Luiz, I posted answer in openfts mailing list. Short version: get old .c,.h files from openfts cvs repository. Oleg On Fri, 1 Dec 2006, Luiz Claudio da Silva Le?o wrote: Hi, I am trying to create de protuguese dictionary in order to use tserch2. The steps I followed are described below: 1) Login system as user1 2) cd 3) tar -xvzf postgresql-8.1.5.tar.gz 4) cd postgresql-8.1.5 5) ./configure 6) gmake 7) su 8) gmake install 9) adduser postgres 10) mkdir /usr/local/pgsql/data 11) chown postgres /usr/local/pgsql/data 12) su - postgres 13) /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data --locale=pt_BR.utf8 14) /usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data logfile 21 15) /usr/local/pgsql/bin/createdb test 16) /usr/local/pgsql/bin/psql test Postgresql working. 17) \q (inside psql) 18) exit 19) cd /home/user1/postgresql-8.1.5/contrib/tsearch2 20) gmake 21) gmake install Tsearch OK. 22) cd ./gendict 23) wget http://snowball.tartarus.org/algorithms/portuguese/stem.c 24) wget http://snowball.tartarus.org/algorithms/portuguese/stem.h 25) ./config -n pt -s -p portuguese_ISO_8859_1 -v -C'Snowball stemmer for Portuguese' 26) cd ../../dict_pt 27) make Now I receive a lot of erros... I think there are differences on interfaces involving snowball and tsearch2. Does anybody know how to create a new dictionary or have old versions of stem.c and stem.h for portuguese? Thanks, Luiz Claudio Leao ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Using GIN indexes on 8.2
On Sat, 11 Nov 2006, Robert Treat wrote: On Friday 10 November 2006 14:41, Jeff Davis wrote: On Fri, 2006-11-10 at 20:39 +0300, Teodor Sigaev wrote: Use @, @ operations instead of @ and ~ Look for discussions in -hackers for reasons of changing names Ah, many thanks. How about updating those web pages? :) Now they are in core: http://developer.postgresql.org/pgdocs/postgres/functions-array.html Pls, why don't you use tsearch2 with GIN? Perhaps -- is there any documentation on this? Nothing special, just use GIN index instead of GiST. I think a section showing when to use GiST versus GIN would be very helpful (assuming there are still good reasons to use GiST). I would certainly read it. I was wondering this same thing, but it appears the trade-off is disk space/update speed (GiST) vs. query times (Gin). Magnus had a couple of nice posts in his blog you might want to check out: http://people.planetpostgresql.org/mha/ This is written in tsearch2 documentation http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-ref.html#indexes Also, see our slides http://www.sai.msu.su/~megera/wiki/tsearch2slides The best practice is to use GIN for archive search and GiST for online one. Table inheritabce with CE would help maintaining of good search performance and fast update. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [GENERAL] Index greater than 8k
Gregory, All you described you do with fti is possible with tsearch2. Just need some think, of course. If you don't need stemming, just don't use it, if you need something like %txt%, just write simple dictionary, which produce any substrings from input word. Oleg On Tue, 31 Oct 2006, Gregory S. Williamson wrote: I hesitate to mention it, since it's retrograde, uses OIDS, may not handle your locale/encoding correctly, may not scale well for what you need etc., etc. But we've used fti (in the contrib package) to do fast searches for any bit of text in people's names ... we didn't go with tesearch2 because we were a bit worried about the need to search for fragments of names, and that names don't follow stemming rules and the like very well. Still it might be a way of handling some of the uglier data. It was a bit of a pain to set up but seems to work well. Of course, users can ask for something commonplace and get back gazillions of rows, but apparently that's ok for the application this is part of. Caveat: only about 32 million rows in this dataset, partitioned into unequal grouings (about 90 total). HTH (but doubt it for reasons that undoubtedly be made clear ;-) Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of Joshua D. Drake Sent: Tue 10/31/2006 7:46 PM To: Teodor Sigaev Cc: Darcy Buskermolen; PgSQL General; PostgreSQL-development Subject:Re: [HACKERS] [GENERAL] Index greater than 8k Teodor Sigaev wrote: The problem as I remember it is pg_tgrm not tsearch2 directly, I've sent a self contained test case directly to Teodor which shows the error. 'ERROR: index row requires 8792 bytes, maximum size is 8191' Uh, I see. But I'm really surprised why do you use pg_trgm on big text? pg_trgm is designed to find similar words and use technique known as trigrams. This will work good on small pieces of text such as words or set expression. But all big texts (on the same language) will be similar :(. So, I didn't take care about guarantee that index tuple's size limitation. In principle, it's possible to modify pg_trgm to have such guarantee, but index becomes lossy - all tuples gotten from index should be checked by table's tuple evaluation. We are trying to get something faster than ~ '%foo%'; Which Tsearch2 does not give us :) Joshua D. Drake If you want to search similar documents I can recommend to have a look to fingerprint technique (http://webglimpse.net/pubs/TR93-33.pdf). It's pretty close to trigrams and metrics of similarity is the same, but uses another signature calculations. And, there are some tips and trics: removing HTML marking,removing punctuation, lowercasing text and so on - it's interesting and complex task. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [HACKERS] [GENERAL] Index greater than 8k
On Fri, 3 Nov 2006, Joshua D. Drake wrote: Oleg Bartunov wrote: Gregory, All you described you do with fti is possible with tsearch2. Just need some think, of course. If you don't need stemming, just don't use it, if you need something like %txt%, just write simple dictionary, which produce any substrings from input word. Is there any information on writing these dictionaries? Туториал http://www.sai.msu.su/~megera/wiki/Gendict Some examples are on http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ For example, http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/dicts/README.intdict http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/custom-dict.html Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [GENERAL] Index greater than 8k
On Tue, 31 Oct 2006, Joshua D. Drake wrote: Yes we do (and can) expect to find text among the bytes. We have searches running, we are just running into the maximum size issues for certain rows. you can use substr() to be safe, if schema change doesn't available Sincerely, Joshua D. Drake Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] old Pg interface
I added some compatibility functions and it worked with new PostgreSQL. Let me know if you need it Oleg On Mon, 16 Oct 2006, Brandon Metcalf wrote: I'm currently using version 1.9.0 of the old Pg interface with PostgreSQL 8.0.3. Our code needs to be updated to use DBI/DBD::Pg, but we need to upgrade PostgreSQL before this is going to happen. Does anyone know of any issues with continuing to use the old Pg interface with newer versions of PostgreSQL? Thanks. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] old Pg interface
On Mon, 16 Oct 2006, Oleg Bartunov wrote: I added some compatibility functions and it worked with new PostgreSQL. Sorry, I got confused :) I added them to wdb interface Let me know if you need it Oleg On Mon, 16 Oct 2006, Brandon Metcalf wrote: I'm currently using version 1.9.0 of the old Pg interface with PostgreSQL 8.0.3. Our code needs to be updated to use DBI/DBD::Pg, but we need to upgrade PostgreSQL before this is going to happen. Does anyone know of any issues with continuing to use the old Pg interface with newer versions of PostgreSQL? Thanks. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Two billion records ok?
On Tue, 5 Sep 2006, Nick Bower wrote: Thanks - but what do you call big? Several billions of stars. You can try our Cone Search service at http://vo.astronet.ru/cas/conesearch.php Oleg My application is satellite data btw so the reference could be useful. On Tue, 5 Sep 2006 01:40 pm, Oleg Bartunov wrote: Nick, if you need very fast spatial queries (spherical) you may use our Q3C module for POstgreSQL (q3c.sf.net). We use it for providing access to very big astronomical catalogs. Oleg On Tue, 5 Sep 2006, Nick Bower wrote: We're considering using Postgresql for storing gridded metadata - each point of our grids has a variety of metadata attached to it (including lat/lon, measurements, etc) and would constitute a record in Postgresql+Postgis. Size-wise, grids are about 4000x700 and are collected twice daily over say 10 years. As mentioned, each record would have up to 50 metadata attributes (columns) including geom, floats, varchars etc. So given 4000x700x2x365x10 2 billion, is this going to be a problem if we will be wanting to query on datetimes, Postgis lat/lon, and integer-based metadata flags? If however I'm forced to sub-sample the grid, what rule of thumb should I be looking to be constrained by? Thanks for any pointers, Nick PS - Feel free to throw in any other ideas of grid-suitable databases :) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Tsearch2 Hebrew
On Tue, 5 Sep 2006, Yonatan Ben-Nes wrote: Hi all, Well my problem was that I didn't know if Tsearch2 can work on hebrew data without a fitting stemmer, my current solution is to use the 'simple' dictionary so no lexem is returned. I wonder if there is an hebrew stemmer which I can use but I can't seem to find one, so sadly one of the best features of Tsearch2 isn't working for me. Do you use hebrew ispell dictionary ? If I'm wrong please let me know :) Thanks a lot in advance, Yonatan Ben-Nes Oleg Bartunov wrote: You need to provide more details. Oleg On Fri, 1 Sep 2006, Michelle Konzack wrote: Hello Jonatan, Am 2006-08-30 19:09:19, schrieb Yonatan Ben-Nes: I want to use Tsearch2 for a current project I have but I can't seem to find a way to implement it on hebrew content. I have the same problem since I have an UTF-8 Database of arround 380 GByte (growing 100 MByte per day) in over 60 languages and can not search in arabic, farsi and hebrew. It seems, that there is NO solution for those three languages Greetings Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org __ NOD32 1.1739 (20060904) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Tsearch2 Hebrew
On Tue, 5 Sep 2006, Yonatan Ben-Nes wrote: No, I didn't thought that it will be useful if it won't be accompanied by an hebrew stemmer which will work with it... I'm wrong? ispell and stemmer are doing the same job, so you may use ispell,simple configuration instead of ideal one: ispell, stemmer Of course, some words will not recognized and will leave as is. Also, you may write very simple stemmer using collection of very common endings. Oleg Oleg Bartunov wrote: On Tue, 5 Sep 2006, Yonatan Ben-Nes wrote: Hi all, Well my problem was that I didn't know if Tsearch2 can work on hebrew data without a fitting stemmer, my current solution is to use the 'simple' dictionary so no lexem is returned. I wonder if there is an hebrew stemmer which I can use but I can't seem to find one, so sadly one of the best features of Tsearch2 isn't working for me. Do you use hebrew ispell dictionary ? If I'm wrong please let me know :) Thanks a lot in advance, Yonatan Ben-Nes Oleg Bartunov wrote: You need to provide more details. Oleg On Fri, 1 Sep 2006, Michelle Konzack wrote: Hello Jonatan, Am 2006-08-30 19:09:19, schrieb Yonatan Ben-Nes: I want to use Tsearch2 for a current project I have but I can't seem to find a way to implement it on hebrew content. I have the same problem since I have an UTF-8 Database of arround 380 GByte (growing 100 MByte per day) in over 60 languages and can not search in arabic, farsi and hebrew. It seems, that there is NO solution for those three languages Greetings Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org __ NOD32 1.1739 (20060904) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 6: explain analyze is your friend __ NOD32 1.1739 (20060904) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Tsearch2 Hebrew
You need to provide more details. Oleg On Fri, 1 Sep 2006, Michelle Konzack wrote: Hello Jonatan, Am 2006-08-30 19:09:19, schrieb Yonatan Ben-Nes: I want to use Tsearch2 for a current project I have but I can't seem to find a way to implement it on hebrew content. I have the same problem since I have an UTF-8 Database of arround 380 GByte (growing 100 MByte per day) in over 60 languages and can not search in arabic, farsi and hebrew. It seems, that there is NO solution for those three languages Greetings Michelle Konzack Systemadministrator Tamay Dogan Network Debian GNU/Linux Consultant Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Two billion records ok?
Nick, if you need very fast spatial queries (spherical) you may use our Q3C module for POstgreSQL (q3c.sf.net). We use it for providing access to very big astronomical catalogs. Oleg On Tue, 5 Sep 2006, Nick Bower wrote: We're considering using Postgresql for storing gridded metadata - each point of our grids has a variety of metadata attached to it (including lat/lon, measurements, etc) and would constitute a record in Postgresql+Postgis. Size-wise, grids are about 4000x700 and are collected twice daily over say 10 years. As mentioned, each record would have up to 50 metadata attributes (columns) including geom, floats, varchars etc. So given 4000x700x2x365x10 2 billion, is this going to be a problem if we will be wanting to query on datetimes, Postgis lat/lon, and integer-based metadata flags? If however I'm forced to sub-sample the grid, what rule of thumb should I be looking to be constrained by? Thanks for any pointers, Nick PS - Feel free to throw in any other ideas of grid-suitable databases :) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] TSearch: Need debug help
Hannes,
I don't know german, sorry, but does 'dursten' is a some form of 'durst' ?
Probably, here we have false hit from compound word support. I'd suggest
to use exclusion dictionary (on the base of synonym dictionary)
before ispell. It could be very simple:
durst : durst
Oleg
On Thu, 3 Aug 2006, Hannes Dorbath wrote:
SELECT ts_debug('durst');
(default_german,lword,Latin word,durst,{de_ispell,de},'dur' 'sen')
SELECT ts_debug('h?chsten');
(default_german,word,Word,h?chsten,{de_ispell,de},'sen' 'h?ch' 'h?chst'
'h?chsten')
For some reason both produce the lexem 'sen'. That leads to strange results.
Search for `durst' will highlight `h?chsten' with headline().
Server is PG 8.0.4,
german snowball stemmer,
dictionary used is http://hannes.imos.net/german_iso.med
(From OpenOffice)
What causes some words to result in `sen', though they don't contain that
lexem?
Thanks!
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] TSearch vs. Homebrew
On Tue, 27 Jun 2006, Hannes Dorbath wrote: http://www.symfony-project.com/askeet/21 How does this dead simple approach compare to TSearch performance / scaling wise? You miss the main point in tsearch2 - full integration with database, i.e., full access to metadata, ACID. Lucene has no of these features, so it could use some well known optimization and, and so, scales better. If you don't need ACID, metadata access, why do you need database at all ? Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] TSearch vs. Homebrew
On Tue, 27 Jun 2006, Hannes Dorbath wrote: On 27.06.2006 13:31, Oleg Bartunov wrote: On Tue, 27 Jun 2006, Hannes Dorbath wrote: http://www.symfony-project.com/askeet/21 How does this dead simple approach compare to TSearch performance / scaling wise? You miss the main point in tsearch2 - full integration with database, i.e., full access to metadata, ACID. Lucene has no of these features, so it could use some well known optimization and, and so, scales better. If you don't need ACID, metadata access, why do you need database at all ? Yes, I know the benefits of using TSearch :) (I'm using it on many projects) I just found that article and wondered how well this simple approach might scale. Sorry for wasting your time ;) Sorry, I was a bit off-topic. Lucene scales as any inverted index based engine. In 8.2 tsearch2 also has inverted index support, but we obey relational approach and couldn't provide a whole set of optimization, which file based engines could provide. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Computing transitive closure of a table
Chris, have you seen contrib/ltree ? Oleg On Mon, 19 Jun 2006, Chris Smith wrote: I am doing some preliminary work on the next major release of a piece of software that uses PostgreSQL. As odd as this sounds, it seems that a huge percentage of the new features that have been requested involve computing the transitive closure of a binary relation that's expressed in a database table. For example: - Given a list of relationships of the form X is a direct subgroup of Y, determine the full list of groups of which some group is a (not necessarily direct) subgroup. - Given a list of statements of the form X must happen before Y, determine everything that needs to happen for some objective to be achieved. And the list goes on and on... I'm aware that it's not possible to solve the transitive closure problem using a simple SQL query. Anyone have any recommendations? Are there any thoughts on implementing efficient transitive closures within PostgreSQL? If I wanted to do it, are there preferences on syntax or other such things? My thoughts on an ideal feature would involve being able to create a sort of transitive closure index which could be kept up to date automatically by the database back end. Or should I just punt and let the queries be slow (not a good option, since the group thing is necessary for permission checking, which may happen up to a half-dozen times per HTTP request). Thanks, Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] minimizing downtime when upgrading
On Thu, 15 Jun 2006, snacktime wrote: Anyone have any tips for minimizing downtime when upgrading? So far we have done upgrades during scheduled downtimes. Now we are getting to the point where the time required for a standard dump/restore is just too long. What have others done when downtime is critical? The only solution we have been able to come up with is to migrate the data on a per user basis to a new database server. Each user is a merchant, and the data in the database is order data. Migrating one merchant at a time will keep the downtime per merchant limited to just the time it takes to migrate the data for that merchant, which is acceptable. Any other ideas? we use replication package slony for upgrading. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Compound words giving undesirable results with tsearch2
On Tue, 30 May 2006, Lars Haugseth wrote:
I've setup a database using tsearch2, configured with support for compound
words according to the excellent guide found here:
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_compound_words
This works fine. There is however one drawback that I'd like to know
whether can be remedied. Let's say I want to search for records containing
the word 'fritekst', which is a compound Norwegian word meaning
'free text'.
testdb=# select to_tsquery('default_norwegian', 'fritekst');
to_tsquery
--
'fritekst' | 'fri' 'tekst'
(1 row)
Now, this will indeed match those records, but it will also match any
records containing both of the words 'fri' and 'tekst', without regard
to whether they are next to each other or in completely different parts
of the text being indexed. In many situations, this will lead to a lot
of 'false' matches, seen from a user perspective.
Ideas on how to handle this problem will be much appreciated.
this is where order by relevance should helps.
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Use of ISpell dictionaries with tsearch2 - what is
).
It's possible, but it may produce unpredictable results for searching,
example from head (sorry, russian):
horosho - good ('sh' in russian is one character)
herovo - bad ( slang )
horovo - where is mistype? second character or 5-th? If we correct this
to one or both variants, user will get 'bad' for searching query 'good'...
2.1 Are there any canned synonym dictionaries available the deal with
misspellings in English and/or French? 2.2 Are there any clever
linguistic algorithms that can partly solve the same problem?
Ask linguists :).
---(end of broadcast)---
TIP 6: explain analyze is your friend
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Please comment on the following OpenFTS/tsearch2
On Thu, 27 Apr 2006, Vivek Khera wrote: On Apr 26, 2006, at 3:17 AM, Teodor Sigaev wrote: We knows installation of tsearch2 working with 4 millions docs. What are the design goals for the size of the source tables? My engineers are telling me of things their friends have tried and have hit limits of tsearch2. One was importing a large message board (millions of rows, a few sentences of text per row) and ran into problems (which were not detailed). Our interest is in using it for indexing mailing lists we host. We're looking at about 100 or so messages per day right now, with potential growth. Short of actually implementing it and loading up sample data, what guidelines can you provide as to the limits of tsearch2 source data size? I can imagine having 10+ million rows of 4k-byte to 10k-byte long messages within a couple of years. It should be no problem with inverted index we just posted. Search itself is very fast ! The problem is intrinsic for relational database - read data from disk. If you find 100,000 results and you want to rank them, you have to read them from hd, which is slow. That's why we use cacheing search daemon and on 5 mln blog and we could get 1mln search/day on 8Gb RAM server. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] full text indexing
Chris, try REL8_1_STABLE branch, because you save a lot of time of indexing Oleg On Fri, 17 Mar 2006, Chris wrote: Oleg Bartunov wrote: On Wed, 15 Mar 2006, chris smith wrote: Hi all, Just wondering which full text module is better what the differences are between tsearch and fti ? if you need online indexing and linguistic support (dictionaries, stop words, ranking) tsearch2 is fine. If your data are static and you need only strict search, fti could works for you. We hope to develop inverted index support for tsearch2 this year, though. The table in question has roughly 80,000 rows. how many unique words and how long are documents ? I think I'll stick to tsearch, it seems to work pretty well. I created the words list from one table (2 columns) for fti. 2.6G file and almost 200 million records, and took up almost 8.5G of space in the database. I was going to see if I could do a speed comparison between the two but ran out of space before I could create the indexes on this table. Hmm. Might try it on a different table and see what happens, I'm interested to see the differences (if any) between the results :) Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] full text indexing
On Wed, 15 Mar 2006, chris smith wrote: Hi all, Just wondering which full text module is better what the differences are between tsearch and fti ? if you need online indexing and linguistic support (dictionaries, stop words, ranking) tsearch2 is fine. If your data are static and you need only strict search, fti could works for you. We hope to develop inverted index support for tsearch2 this year, though. The table in question has roughly 80,000 rows. how many unique words and how long are documents ? Thanks! -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Indexes on array columns
contrib/intarray might help you
Oleg
On Wed, 15 Mar 2006, [EMAIL PROTECTED] wrote:
On Wed, Mar 15, 2006 at 09:36:04AM -0700, Brendan Duddridge wrote:
Hi,
Is it possible to put an index on an array column?
Apparently yes (I just did it as a test). However, consider the
following from the manual.
Tip: Arrays are not sets; searching for specific array elements
may be a sign of database misdesign. Consider using a separate
table with a row for each item that would be an array element. This
will be easier to search, and is likely to scale up better to large
numbers of elements.[1]
Arrays are attractive, but it seems they most often aren't the best
solution. FOr instance, I beleive I read somewhere that the index
will be on the whole array, and the individual elements are not
indexed, so
WHERE ary = {foo,bar}
might benefit from your index, but
WHERE 'foo' = ANY(ary)
probably wouldn't. At least that's the impression I got from
reading the archives of this list. I haven't done any testing of
it.
-karl
1. http://www.postgresql.org/docs/8.0/interactive/arrays.html#AEN5491
Thanks,
Brendan Duddridge | CTO | 403-277-5591 x24 | [EMAIL PROTECTED]
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9
http://www.clickspace.com
---(end of broadcast)---
TIP 6: explain analyze is your friend
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Tsearch2 ranking
On Mon, 13 Mar 2006, Hannes Dorbath wrote: 2 rows of tsvector: 'bar':2 'baz':3 'foo':1 'bar':2 'baz':1 'foo':3 so source text was: foo bar baz baz bar foo ts_query now is 'foobazbaz', so both matched. How can I honor the correct order of the first row and rank it higher? The position information is there, why doesn't rank() / rank_cd() not use it? There is a hint to do this. See Phrase search in http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes Is there any way to make a difference between those to rows? Any? Even if it's a bad hack? I really need it :/ Thanks in advance Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Operator for int8 array
On Mon, 27 Feb 2006, S.Thanga Prakash wrote: Dear sir, We are already in the process of migrating toward 8.1 . For existing support, we like to support with 7.1.3 . Along with 7.1.3 release, contrib/array has been given for array iterator support for int4 type array. It is working fine. We changed it similarly, for int8 type array. It is not working and postmaster receives signal 11. We like to know whether is it feasible or not? why not ? thanks and regards, stp. On Fri, 24 Feb 2006, Tom Lane wrote: K.Deepa [EMAIL PROTECTED] writes: I need operator for int8 array. I tried changing the code in contrib/ and compiled. When I tried executing the query, it is dumping. Kindly tell me if there is alternative to overcome the problem. I am using postgresql 7.1.3 version. 7.1.3? Egad. Get yourself onto some remotely modern version of PG. 7.1 is nearly five years old and has many known serious bugs, of both data-loss and security flavors. You didn't say exactly what you needed to do with an int8 array, but you may well find that 8.1 can do it already. The array support is far superior now to what it was in 7.1. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] TSearch2 / German compound words / UTF-8
On Fri, 27 Jan 2006, Harald Armin Massa wrote: Teodor, To all: May be, we should put all snowball's stemmers (for all available languages and encodings) to tsearch2 directory? Yes, that would be VERY helpfull. Up to now I do not dare to use tsearch2 because get stemmer here, get dictionary there... Hmm, we could provide snowball stemmers tsearch2-ready (about 700kb), but ispell dictionaries could be very large. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstra?e 202b 70197 Stuttgart 0173/9409607 Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] TSearch2 / German compound words / UTF-8
Alexander,
could you try tsearch2 from CVS HEAD ?
tsearch2 in 8.1.X doesn't supports UTF-8 and works for someone
only by accident :)
Oleg
On Fri, 27 Jan 2006, Alexander Presber wrote:
Tsearch/isepll is not able to break this word into parts, because of the
s in Produktion/s/intervall. Misspelling the word as
Produktionintervall fixes it:
It should be affixes marked as 'affix in middle of compound word',
Flag is '~', example look in norsk dictionary:
flag ~\\:
[^S] S #~ advarsel advarsels-
BTW, we develop and debug compound word support on norsk (norwegian)
dictionary, so look for example there. But we don't know Norwegian,
norwegians helped us :)
Hello everyone!
I cannot get this to work. Neither in a german version, nor with the
norwegian example supplied on the tsearch website.
That means, just like Hannes I can get compound word support without inserted
's' in german and norwegian:
Vertragstrafe works, but not Vertragsstrafe, which is the right Form.
So I tried it the other way around: My dictionary consists of two words:
---
vertrag/zs
strafe/z
---
My affixes file just switches on compounds and allows for s-insertion as
described in the norwegian tutorial:
---
compoundwords controlled z
suffixes
flag s:
[^S] S # endet nicht auf s: s anfuegen und in
compound-check (Recht Rechts-)
---
ts_debug yields:
tstest=# SELECT tsearch2.ts_debug('vertragstrafe strafevertrag
vertragsstrafe');
ts_debug
-
(german,lword,Latin word,vertragstrafe,{ispell_de,simple},'strafe'
'vertrag')
(german,lword,Latin word,strafevertrag,{ispell_de,simple},'strafe'
'vertrag')
(german,lword,Latin
word,vertragsstrafe,{ispell_de,simple},'vertragsstrafe')
(3 Zeilen)
I would say, the ispell compound support does not honor the s-Flag in
compounds.
Could it be, that this feature got lost in a regression? It must have worked
for norwegian once. (Take the overtrekksgrilldresser example from the
tsearch2:compounds tutorial, that I cannot reproduce).
Any hints?
Alexander
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] tsearchd (tsearch2 daemon) is available for playing
Hi there, we did a 8.1 compatible version of tsearchd, available from http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch2 tsearchd is our old experiment with inverted index. It's fully compatible with tsearch2, actually it's tsearch2+several functions and daemon. Very brief documentation is available http://www.sai.msu.su/~megera/oddmuse/index.cgi/tsearchd Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] full text search
You may try tsearchd http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch2 Oleg On Tue, 17 Jan 2006, Martin Krallinger wrote: Hi all, I am not a Postgres expert, and thus would like to ask you a question related to full text search (and indexing). I actually would like to search a table of over 10gb of free text. I am not quite sure which would be the best (fastest) way to do it. I was trying out tsearch-v2 but it seemed to be very slow. any suggestions? thanks, Martin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] TSearch2 / Get all unique lexems
On Thu, 8 Dec 2005, Hannes Dorbath wrote: On 07.12.2005 16:13, Oleg Bartunov wrote: hmm, you could dump tsvector column and use awk+sort+uniq Thanks. I hoped for something possible inside a pl/pgsql proc. I'm trying to integrate pg_trgm with Tsearch2. I'm still on my UTF-8 database. Yes I know, there is _NO_ UTF-8 support of any kind in Tsearch2 yet, but I got it working to a degree that is OK for my application (Created my own stemmer variant, ispell dict, affix file etc). The last missing bit is to get a source for pg_trgm. I cannot use the the stat() function, because it breaks as soon it sees an UTF-8 char. unless there is some way to ignore errors in utf8 convertation to text this is a dead-end. stat() function uses text representation. You have to wait new release with full UTF8 support or go 'lazy' way, i.e. use any tools to get a list of unique words and create pg_trgm index. There are several questions: * Do you actually need to be synchronized with tsvector ? * Do you need to recognize all words ? I supposed no. In real life you should have a dictionary which you certainly need to recognize. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] is it possible to delete the psql log while psql is
I'd do echo pgql.log Oleg On Thu, 8 Dec 2005, gabor wrote: hi, i'd like to delete the postgresql log file (resides in /var/log/pgsql/postgres), because it has become too big. can i simply delete the file while postgresql is running? or do i have to stop postgresql first, and only delete the logfile after that? thanks, gabor Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] tables with lots of columns - what alternative from
contrib/hstore will save you. See http://www.sai.msu.su/~megera/postgres/gist/hstore/README.hstore for details. Oleg On Wed, 7 Dec 2005, hubert depesz lubaczewski wrote: hi jus recently there were some thread on postgresql list with people asying : i have 700 columns, i have 1000 columns and so on. some people, imediatelly responded: change your schema. this is what forced to me ask: i have a situation where i ahve to store a number of objects in database. all objects have 3 specific attributes (which go into objects table), and may have a lot of custom fields. basically - lsit of accessible custom fields for object depends on which object-category this object belongs to. now. i know, i could have written it in this way: create table object_custom_fields (id serial primary key, object_id int8, field_id int8, field_value text); but: this approach has two very big drawbacks (for me): 1. the table cannot differentiate between custom fields of type date, number and so on. - everything is stored as text. 2. it is rather slow. i have to do a non-unique index scan over object_custom_fields, get all records, and pivot it (on the client side of curse) to make it usable. i did it differently, definitelly not nicely, but i dont see any other way to get this performance with unknown list of custom fields: 1. create table cf_types (id serial, codename text, representation text); 2. create table cf_definitions (id serial, category_id int8, type_id int8, field-number int4); 3. create table cf_values (id serial, object_id int8 (unique), ...); where cf_types store information like this: id | codename | representation ++ 1 | bool | boolean 2 | integer| integer 3 | number | number 4 | text | text 5 | note | text 6 | date | date ... basically - there might be many types with the same representation. then cf_values have a lot of (128 at the moment) fields for all possible representations. basically it looks like: id, object_id, boolean_1 ... boolean_128, integer_1..integer_128, ... the datatypes of this fields relate to their content (integer_* fields have datatype int8, and so on). now. in cf_definitions i specify, category, field_type_id, and a field-number - which relates to _NUMBER in fields in cf_values. what i did achive is *very* fast retrieval of data for any given object. the schema of cf_values table is absolutelly awful, and i will never say differently. my point is - if somebody (tom lane for example) says - redesign your schema - whenever he reads about table with 700 column (i have more :) - then i must have missed something absolutelyl simple, fast and elegant. what is this? depesz Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] TSearch2 / Get all unique lexems
On Wed, 7 Dec 2005, Hannes Dorbath wrote: Is there a way to get all unique lexems from a table with a tsvector column? The stat() function does this (and more), but I cannot use it.. hmm, you could dump tsvector column and use awk+sort+uniq Thanks Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] fts, compond words?
As Teodor already pointed there is no non-ambiguous solution, or at least, we don't know it. Oleg On Wed, 7 Dec 2005, Andrew J. Kopciuch wrote: A (B | (New OperatorTheNextWordMustFollow York)) I had thought about this before myself. Alas I have never had the time to properly investigate implementing such a feature. :( A (B | (New + York)) Something like that? Actually, I love that idea. Oleg, would it be possible to create a tsquery operator that understands proximity? Or, how allowing a predicate to the current '' op, as in '[dist=1]' meaning next token follows with a max distance of 1. I imagine that it would only be useful on unstripped tsvectors, but if the lexem position is already stored ... Would the proximity go in both directions? Or just forward? What about tokens that come before? Just a thought. Andy ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] ltree patch is available
Hi there, Patch for ltree (all releases since 7.3) is available http://www.sai.msu.su/~megera/postgres/gist/patches/_ltree.patch and from CVS. It fixes problem with unoptimal tree construction and big size of index. Thanks Lexa Tutubalin for test suite and persistency. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] fts, compond words?
On Mon, 5 Dec 2005, Marcus Engene wrote:
Hi,
I use the tsearch full text search with pg 8.0.3. It works great, but I
wonder if it's possible to search for compound words?
Ie if I search for New York i want to get a match on
New York has traffic problems.
but not on
New axe murderer incident in brittish York.
Is this possible?
I don't use any wrapper, just
select
...
from
...
where
idxfti @@ to_tsquery('default', 'searchstring')
ranking function is what you need. Read documentation.
Thanks,
Marcus
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] fts, compond words?
On Mon, 5 Dec 2005, Marcus Engene wrote:
I realized from the documentation that I'm not looking for
compound words after all, I meant exact phrase.
I can't see how to make rank tell me which results has an
exact phrase? Like there must be a occurence of 'new' before
'york' (stemmed not really exact phrase)?
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes
Phrase search
This tip is by Mike Rylander
To do phrase searching just add an additional WHERE clause to your query:
SELECT id FROM tab WHERE ts_idx_col @@ to_tsquery('historylesson')
AND text_col ~* '.*history\\s+lesson.*';
The full-text index will still be used, and the regex will be used to
prune the results afterwards.
Is there something new in rank for pg 8.1?
it has some improving, but not for your case.
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] intarray index
On Mon, 28 Nov 2005, Marek Lewczuk wrote: Hello, I have a question about IntArray contrib package. The docs says: ...current implementation provides index support for one-dimensional array of int4's - gist__int_ops, suitable for small and medium size of arrays (used on default), and gist__intbig_ops for indexing large arrays (we use superimposed signature with length of 4096 bits to represent sets)... How many elements within single array is suggested within small/medium/large array ? I have arrays with up to 200 elements - which index I should use ? gist__intbig_ops Thanks. ML ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] tool for DB design
On Fri, 25 Nov 2005, Nikolay Samokhvalov wrote: dbvis isn't the thing what I need. Or I'm blind and cannot find where I can draw ER/UML diagram then transform it to physical, then obtain SQL code. Or, just physical-SQL. Please correct me if I'm wrong. Nikolaym, it's worth to look on Enterprise Architect http://www.sparxsystems.com/products/ea_purchase.html It's not free, but not so pricy. On 25/11/05, Tino Wildenhain [EMAIL PROTECTED] wrote: Am Freitag, den 25.11.2005, 02:25 +0300 schrieb Nikolay Samokhvalov: Please, suggest any free/opensource tool for DB design under Linux. I need following: ER (or UML)-diagram - physical diagram - SQL code (I don't even dream about reverse transformation...) Quite good example of such tool is Sybase PowerDesigner (which supports Postgres), but AFAIK it runs only under win and costs $... dia can do this and I guess a lot of other tools. For the reverse check out db visualizer (dbvis) http://www.minq.se/products/dbvis/ Which has a very nice ER-view. (With autorouting, which many tools, even the $$$-ones miss) ++Tino -- Best regards, Nikolay ---(end of broadcast)--- TIP 6: explain analyze is your friend Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] tsearch2: more than one index per table?
On Tue, 22 Nov 2005, Andrew J. Kopciuch wrote: This is not a problem with the index creation ... your tsearch2 installation is not configured for the locale your server is running. http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html See the section TSEARCH2 CONFIGURATION. It explains, and has examples on how to set this up. I added paragraph about this problem, check my notes http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_Notes Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] TSearch2 / German compound words / UTF-8
On Wed, 23 Nov 2005, Hannes Dorbath wrote:
Hi,
I'm on PG 8.0.4, initDB and locale set to de_DE.UTF-8, FreeBSD.
My TSearch config is based on Tsearch2 and Unicode/UTF-8 by Markus Wollny
(http://tinyurl.com/a6po4).
The following files are used:
http://hannes.imos.net/german.med [UTF-8]
http://hannes.imos.net/german.aff [ANSI]
http://hannes.imos.net/german.stop [UTF-8]
http://hannes.imos.net/german.stop.ispell [UTF-8]
german.med is from ispell-german-compound.tar.gz, available on the TSearch2
site, recoded to UTF-8.
The first problem is with german compound words and does not have to do
anything with UTF-8:
In german often an s is used to link two words into an compound word.
This is true for many german compound words. TSearch/ispell is not able to
break those words up, only exact matches work.
An example with Produktionsintervall (production interval):
fts=# SELECT ts_debug('Produktionsintervall');
ts_debug
--
(default_german,lword,Latin
word,Produktionsintervall,{de_ispell,de},'produktionsintervall')
Tsearch/isepll is not able to break this word into parts, because of the s
in Produktion/s/intervall. Misspelling the word as Produktionintervall
fixes it:
fts=# SELECT ts_debug('Produktionintervall');
ts_debug
-
(default_german,lword,Latin
word,Produktionintervall,{de_ispell,de},'ion' 'produkt' 'intervall'
'produktion')
How can I fix this / get TSearch to remove/stem the last s on a word before
(re-)searching the dict? Can I modify my dict or hack something else? This is
a bit of a show stopper :/
I think the right way is to fix affix file, i.e. add appropriate rule,
but this is out of our skill :) You, probable, should send your
complains/suggestions to erstellt von transam email: [EMAIL PROTECTED]
(see german.aff)
The second thing is with UTF-8:
I know there is no, or no full support yet, but I need to get it as good as
it's possible /now/. Is there anything in CVS that I might be able to
backport to my version or other tips? My setup works, as for the dict and the
stop word files, but I fear the stemming and mapping of umlauts and other
special chars does not as it should. I tried recoding the german.aff to UTF-8
as well, but that breaks it with an regex error sometimes:
fts=# SELECT ts_debug('dass');
ERROR: Regex error in '[^s??]$': brackets [] not balanced
CONTEXT: SQL function ts_debug statement 1
This seems while it tries to map ss to ?, but anyway, I fear, I didn't
anything good with that.
Similar problem was discussed
http://sourceforge.net/mailarchive/forum.php?thread_id=6271285forum_id=7671
As suggested in the Tsearch2 and Unicode/UTF-8 article I have a second
snowball dict. The first lines of the stem.h I used start with:
extern struct SN_env * german_ISO_8859_1_create_env(void);
So I guess this will not work exactly well with UTF-8 ;p Is there any other
stem.h I could use? Google hasn't returned much for me :/
As we mentioned several times, tsearch2 doesn't supports UTF-8 and
is working only by accident :) We've got working parser with full UTF-8
support, but we need to rewrite interfaces to dictionaries, so there is nothing
useful to the moment. All changes are available in CVS HEAD (8.2dev).
Backpatch for 8.1 will be available from our site as soon as we complete
UTF-8 support for CVS HEAD. We have no deadlines yet, but we have discussed
support of this project with OpenACS community (grant from University of
Mannheim), so it's possible that we could complete it really soon
(we have no answer yet).
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] tsearch2: more than one index per table?
On Wed, 23 Nov 2005, Rick Schumeyer wrote:
I apologize if I'm being dense, but I'm not completely following the
explanation. It is true that my pg_ts_cfg.locale is set to en_US.UTF-8.
It was my understanding that specifying default as in
UPDATE t SET idxB=to_tsvector('default', b);
should give tsearch enough information.
It is not clear to me why the first time works, but not the second time
with almost identical statements.
I thought that I only had to follow the procedure in the docs if I want
to do the following:
UPDATE t SET idxB=to_tsvector(b); -- no 'default'
Perhaps I am wrong about this?
nothing wrong except trigger you defined 'BEFORE UPDATE OR INSERT' fired
and it has no knowledge which configuration to use.
You may put trigger statement after update, but you'll get the same
error when inserting something to table.
Don't resist and configure tsearch2 to match server's locale.
It's most painless way.
-Original Message-
From: [EMAIL PROTECTED] [mailto:pgsql-general-
[EMAIL PROTECTED] On Behalf Of Andrew J. Kopciuch
Sent: Wednesday, November 23, 2005 12:08 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] tsearch2: more than one index per table?
On Tuesday 22 November 2005 21:45, Rick Schumeyer wrote:
Is there something in tsearch2 that prevents more than one index per
table?
I would like an index on field A, and a separate index on field B.
The index builds fine for A, but gives an error for B. The error text
is
ERROR: could not find tsearch config by locale
This is not a problem with the index creation ... your tsearch2
installation
is not configured for the locale your server is running.
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-
intro.html
See the section TSEARCH2 CONFIGURATION. It explains, and has examples
on
how to set this up.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] tsearch2: more than one index per table?
On Wed, 23 Nov 2005, Teodor Sigaev wrote:
ERROR: could not find tsearch config by locale
UPDATE t SET idxA=to_tsvector('default', a);
Is it working
select to_tsvector('foo bar')?
I suppose, no. In that case tsearch can't find configuration for current
database locale, update pg_ts_cfg.locale in wished row to correct value.
Actually, it's described in tsearch2 introduction.
Make sure pg_ts_cfg.locale matched server's locale (see 'show all')
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] TSearch2 Questions
On Mon, 21 Nov 2005, Hannes Dorbath wrote: A few stupid questions: Where to get the latest version? Is http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ a dead site and the latest versions are always silently distributed with PG inside the contrib dir? You should always use tsearch2 distributed with postgresql. We keep our version for testing purposes. Sometimes we publish backpatches (from CVS HEAD) for stable releases. How can I find out what version of TSearch2 I'm running? Is there active development? It's actively developed, see CVS HEAD commits. Main problem attacked is fully UTF-8 support. Also, we plan some other improvements. See http://www.sai.msu.su/~megera/oddmuse/index.cgi/todo Are the patches provided on the site above for backup still needed, or are they already included in the versions that ship with 8.0.x? If not, why not? =) All patches already applied . Or the better question, are any of those patches listed under Development included in the version that ships with recent PG versions? right now, there is no patches you should be aware of. We plan to release UTF-8 support patch for 8.1 release. I'm playing a bit with it ATM. Indexing one Gigabyte of plain text worked well, with 10 GB I yet have some performance problems. I read the TSearch Tuning Guide and will start optimizing some things, but is it a realistic goal to index ~90GB plain text and get sub-second response times on hardware that ~4000 EUR can buy? What's ATM ? As for the sub-second response times it'd very depend on your data and queries. It'd be certainly possible with our tsearch daemon which we postponed, because we inclined to implement inverted indices first and then build fts index on top of inverted index. But this is long-term plan. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Custom GIST getting ERROR: too many LWLocks taken
On Sun, 20 Nov 2005, Ben Martin wrote: Hi, Perhaps this post is better suited to another PG mailing list? If this is the cast then apologies up front :/ pgsql-hackers is more appropriate list I've been playing around with improving some of the GiST code in PG 8.1 to better take advantage of domain knowledge where I am applying it. I am now getting ERROR: too many LWLocks taken when I create index on a largish table (about 50Mb index should be generated). I only get this when shuffling around some index tuples inside picksplit(). The pointers and left/right splits all seem to be in order when I return and the smaller indexes I've created using this code are all fine. My GiST is based on an existing implementation from PG 8.1 so the allocation of the pages etc should all be fine. Any hints as to what could be eating up LWLocks in this case? looks like you're leaking locks. 100 locks is a maximum number of simultaneous locks (backend/storage/lwlock.c). Thanks. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Tsearch2: casting text to tsquery
On Wed, 16 Nov 2005, Alexander Presber wrote:
Hello,
I have a question that arose while using tsearch on a large database of book
information.
In order to inject unstemmed + stemmed versions of searchstrings into a query
we want to cast an expression of type text to tsquery.
Unfortunately
SELECT 'word'::tsquery
(where the literal 'word' is of type 'unknown' seemingly) works just fine
while e.g.
SELECT ('word' || 'fullword')::tsquery
or, simpler
SELECT 'wordfullword'::text::tsquery
gives
ERROR: cannot cast type text to tsearch2.tsquery
How can we work around that? Thanks for any help
no workaround needed. to_tsquery provides everything you need. If you
want fancy-shmancy solution you could always write wrapper around tsquery, but
I doubt you enter queries by hand, so why do you bothering ?
Sincerely yours
Alexander Presber
---(end of broadcast)---
TIP 6: explain analyze is your friend
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Tsearch2: casting text to tsquery
On Wed, 16 Nov 2005, Alexander Presber wrote:
Am 16.11.2005 um 13:52 schrieb Oleg Bartunov:
On Wed, 16 Nov 2005, Alexander Presber wrote:
Hello,
I have a question that arose while using tsearch on a large database of
book information.
In order to inject unstemmed + stemmed versions of searchstrings into a
query we want to cast an expression of type text to tsquery.
Unfortunately
SELECT 'word'::tsquery
(where the literal 'word' is of type 'unknown' seemingly) works just fine
while e.g.
SELECT ('word' || 'fullword')::tsquery
or, simpler
SELECT 'wordfullword'::text::tsquery
gives
ERROR: cannot cast type text to tsearch2.tsquery
How can we work around that? Thanks for any help
no workaround needed. to_tsquery provides everything you need. If you want
fancy-shmancy solution you could always write wrapper around tsquery, but
I doubt you enter queries by hand, so why do you bothering ?
No. No fancy-shmancy solution needed, just one that works.
I do not see a way to include both a stemmed and unstemmed version of a
string into a tsquery.
Can you give an example, please?
This is quite different question and Teodor has already answered you -
use different configurations. But I'm wondering why do you need that.
Thanks
Alex
P.S. I have read the documentation (twice.)
Sincerely yours
Alexander Presber
---(end of broadcast)---
TIP 6: explain analyze is your friend
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] using new bitmap scans to index bit columns?
On Wed, 9 Nov 2005, TJ O'Donnell wrote: I like the new bitmap scans and I'm wondering if there is any way I can utilize them for my bitmask column, defined as bit(1024). I use this column as a sort of fingerprint to quickly scan my tables. But it is a scan, not an index. I have not figured out a way to index the bitmask column. Is there some way it can be used as an index now that there are bitmap scans in 8.1? Currently I do this: Select * from mytable where contains(bitmask, fingerprint(user_data)) and matches(datacolumn, user_data); user_data is a string, like a regexp but with different semantics for chemical data. bitmask is precomputed/stored as bit(1024) = fingerprint(datacolumn) contains(a,b) returns bool as 'select b=(ab);' This works well because matches() is an expensive functions. But it would work better if bitmask could be indexed, no? You can use GiST to do that. TJ O'Donnell ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] which charset use for cyrilic?
Zet, there is pgsql-ru-general list (russian), btw. see http://www.postgresql.org/community/lists/subscribe for subscription info. You did't get us enough info and examples (cut'n paste form psql would be nice). Oleg On Sat, 29 Oct 2005, Zet wrote: Hi Which charset is need to be set in database for cyrilic? I've used till now WIN, but today I found a problem for example: SELECT * FROM table WHERE a = 'слово' returns me a record, where a = 'фраза' after I tried UNICODE but for most of cyrilic words PG gives error like invalid byte sequence for encoding UNICODE:... Regards, Zet ---(end of broadcast)--- TIP 6: explain analyze is your friend Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] tsearch2 setweight
On Fri, 28 Oct 2005, David Gama Rodr??guez wrote: Hello List I' ve installed tsearch2 and is working perfectly but I get this thing, I want to index more than 4 fields in one tsvector so I think to use the setweight but it can only accept ABCD y need to have 5 letters or more Is there any way to change that? or changing the source code, but I want to know if this affects tsearch or postgres currently, no. Do you really need more than 4 classes, not attributes (different attributes could belong to the same lexem class) ? In principle, it's possible to have more than 4 classes, but then you'll increase storage for tsvector. tnks!!! ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Tsearch functional indexes
Ron,
tsearch index is lossy, so search results needs to be verified.
If you have separate tsvector column you could use it and get all benefit
of to_tsvector already process documents (parsing, dictionaries lookup,...),
instead of reading entire document from disk and process it again.
Read tsearch2 internals for more info.
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_internals
Oleg
On Sun, 9 Oct 2005, Ron Mayer wrote:
In the tsearch2 documentation I see a lot of examples where you
add a column of type tsvector to your table and then indexing
that column.
Instead of adding the extra column, would it be possible to
just make a functional index something like this:
create index foo__tsearch on foo using gist (to_tsvector('simple'::text,
text))
A brief informal experiment I tried suggests that
the create index command works; but that queries
using this approach were slower than having the
column around. I would have expected it to be faster,
since the disk space used should have been smaller.
Perhaps something about table statistics that I'm
not considering?
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] How to inject knowledge into a Postgres database
You could increase statistics or try contrib/tsearch2 Oleg On Fri, 7 Oct 2005, [EMAIL PROTECTED] wrote: Hello, We have a table of people with a date-of-birth and a surname, both indexed. We have queries like this: select report from table where dateofbirth = '1966-12-12' and surname like 'boer%' The planner estimates that in a 1.5M record table 'boer%' will have one record and 1966-12-12 about 40. Unfortunately, names are not evenly distributed, and in some combinations it will have to process many thousands of records. On some older and slower systems, this hurts. Is there a way to tell the planner to always prefer the dateofbirth index ? Alternatively, to inform it about the wildly uneven distribution of surnames (this must be even worse in China). A third possibility would be to rewrite such a query as a nested query: is there a rewrite query - hook (like apaches mod_rewrite) available ? Or is there a somewhere a proxy-server that could do this ? Cheers, Han Holl ---(end of broadcast)--- TIP 6: explain analyze is your friend Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Slow search.. quite clueless
On Mon, 26 Sep 2005, Yonatan Ben-Nes wrote: Hi again everyone, Oleg I tried tsearch2 and happily it does work wonderfully for me returning results extremly fast and actually its working even better then I wanted with all of those neat features like: lexem, weight stop words. I got only one problem which is when I want the results to be ordered by a diffrent field (like print INT field) it takes quite alot of time for it to do it if the query can return lots of results (for example search for the word computer) and thats even if I limit the results. The best way to improve its speed for such quereies (that I've found...) is to create an index on the field which I want to order by and using it CLUSTER the table, after the clustering I drop the the index so it won't be used when I run queries with ORDER BY on that field, that seem to improve the speed, if anyone got a better idea ill be glad to hear it. what's your actual query ? have you tried multicolumn index ? Anyway thanks alot everyone! Ben-Nes Yonatan Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] tsearch2 for alphabetic character strings codes
Ron,
probably you need to write custom parser. tsearch2 supports
different parsers.
Oleg
On Fri, 23 Sep 2005, Ron Mayer wrote:
I'm looking for a way search for substrings strings within
documents in a way very similar to tsearch2, but my strings
are not alphabetical codes so I'm having a tough time
trying to use the current tsearch2 configurations with them.
For example, using tsearch to search for codes like
'31.03(e)(2)(A)'
in a set of documents is tricky because tsearch seems
to treat most of the punctuation as word separators.
fli=# select
fli-# to_tsvector('default','31.03(e)(2)(A)'),
fli-# to_tsvector('simple','31.03(e)(2)(A)');
to_tsvector | to_tsvector
---+-
'2':3 'e':2 '31.03':1 | '2':3 'a':4 'e':2 '31.03':1
(1 row)
I see that tsearch2 allows different configurations
that appaently differ in how they parse strings.
I guess what I'm looking for is a configuration
that's even simpler-than-simple, and only breaks
up strings on whitespace and doesn't use any natural
language dictionaries. I was hoping I could download
or define such a configuration; but didn't see any
obvious documentation on how to set up my own
configuration.
Does this sound like a good approach (and if so, could
someone please point me in the right direction), or
are there other things I should be looking to.
Ron
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Slow search.. quite clueless
. Thanks alot in advance, Yonatan Ben-Nes ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Slow search.. quite clueless
with a specific letter, it did improve the speeds but not in a real significant way.. tried clusters,indexes,SET STATISTICS,WITHOUT OIDS on the keyword table and what not.. im quite clueless... Actually I even started to look on other solutions and maybe you can say something about them also.. maybe they can help me: 1. Omega (From the Xapian project) - http://www.xapian.org/ 2. mnoGoSearch - http://www.mnogosearch.org/doc.html 3. Swish-e - http://swish-e.org/index.html To add on everything I want at the end to be able to ORDER BY the results like order the product by price, but im less concerned about that cause I saw that with cluster I can do it without any extra overhead. Thanks alot in advance, Yonatan Ben-Nes ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] tsearch2 unicode
Michal,
tsearch2 doesn't supports UTF-8. It might works though.
full support of UTF-8 and other features are planned
http://www.sai.msu.su/~megera/oddmuse/index.cgi/todo
Oleg
On Tue, 6 Sep 2005, Michal Hlavac wrote:
hello,
I have suse 9.3 with installed postgresql 8.0.3 from rpm.
All my databases are unicode with locale sk_SK.UTF-8.
I installed slovak ispell dictionary and tsearch2 is working very well.
m13=# SELECT lexize('sk_ispell','Ivana');
lexize
--
{ivana,ivan}
(1 row)
On the other side I have FreeBSD also with postgresql 8.0.3 and with
unicode database. I also installed slovak ispell dictionary and tsearch2
is working very well.
but result:
hlk=# SELECT lexize('sk_ispell','Ivana');
ERROR: Regex error in 'ia[tЪЪina$': brackets [] not balanced
where is the problem??? thanks, miso
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] PostgreSQL and XML support
I have XML support in PostgreSQL in my Todo http://www.sai.msu.su/~megera/oddmuse/index.cgi/todo Hopefully, we'll have something for 8.2 Oleg On Tue, 6 Sep 2005, Doug Bloebaum wrote: On 9/6/05, Andrey Fomichev [EMAIL PROTECTED] wrote: - Are there any of you who need to store and query XML data? - Do you already use PostgreSQL to store XML data or you are just thinking about it? The project I'm currently working on uses XML for both data extraction from Postgres and as a means to transmit data to Postgres for storage. - If you store XML data in PostgreSQL, how do you do it? What tool do you use? We're using Oracle's XSQL servlet ( http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10794/adx09xsq.htm) with Apache tomcat (http://jakarta.apache.org/tomcat/) as its servlet container. The only change we've made to XSQL is the addition of a custom tag (xsql:pg-func) in order to make use of Postgres functions returning REF CURSOR. Aside from that, we're using XSQL out-of-the-box. Granted, we're not really manipulating XML within the database, rather we're using XML as a sort of database-neutral interface. - Do you need some advanced query capabilities? Like XQuery, XPath 2.0 No. - Do you need some advanced update capabilities? Like node-level updates No. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Out Of Memory Error on GiST Index
On Thu, 1 Sep 2005, James Cradock wrote: Hello. I'm getting an Out Of Memory error when I try to create a GiST index on a geometry column (PostGIS) with approximately 33,000,000 rows. I can truncate the data in the table, create the GiST index on the empty table, but when I try to reload the data via pg_restore I get the error. I get the error also if I try creating the GiST index on the table with the 33,000,000 rows. Are there limitations with the GiST index? Has anyone seen this? AFAIK, no. Perhaps, it's PostGIS problem ? It'd be nice to build rtree index using contrib/rtree_gist to see if problem in GiST. Thanks. Jim - James Cradock, [EMAIL PROTECTED] - James Cradock, [EMAIL PROTECTED] me3 Technology Consultants, LLC 24 Preble Street, 2nd Floor Portland, ME 04101 207-772-3217 (office) 207-838-8678 (mobile) www.me3.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Searching for LTree dmoz-testdata
Peter, we have problem with mb on our server (it's Supermicro for dual ppro :), but you use mirror at my desktop machine http://mira.sai.msu.su/~megera/postgres Oleg On Wed, 31 Aug 2005, Peter Arwanitis wrote: Hi there, I've choosen pgsql-general, cause I'm new to postgres community and I'm looking for a actually (4 days ago?) vanished website from Teodor Sigaev and Oleg Bartunov (ex: http://www.sai.msu.su/~megera/postgres) Bside tsearch2 they have worked on LTree (googlecache: http://64.233.183.104/search?q=cache:AynIHdkXWGMJ:www.sai.msu.su/~megera/postgres/gist/ltree/+gist+site:sai.msu.suhl=en) and I'm very interested in the test-data package (dmozltree-eng.sql.gz). It could shorten my evaluation time and I'm interested in dmoz anyway. Did anyone out here downloaded this data??? I cannot find a second occurence online. mail to spex66 _at_ gmail would be very appreciated thanks for hints greetings from Munich, Germany Peter (=PA=) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Adjacency List total item counts
use contrib/ltree Oleg On Tue, 9 Aug 2005, Ben wrote: Hi This question is not specific to PostgreSQL but I would like to know what is the best way to count the number of items in each node from the leaf to the root? Something like this: Computers (100) /\ / \ CPU (15) Memory (85) I have the following SQL schema: Tree ( treeId int, parentId int, name varchar(250), ) Item ( itemId int, treeId int, expiryDate date ) Note that the count for the total number of items in each node depends on the item expiry date, i.e. ignore the item if the expiry date is older than now(). I have come up with the following solutions but not happy with any one of them: 1) Do a batch count, i.e. count the number of items every 30 minutes. Using this method defeats the purpose of having the count next to each node since the number might not be the same as the actual count. 2) Use trigger but this can be slow since it has to recurse the tree and do the sum every time new item is added. Thanks Ben ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] tsearch2: very slow queries
Hi there, tsearch2 is our next problem we plan to attack after we have done with GiST core. Actually, we did some experiments in background and we're sure we could very greatly improve tsearch2 performance and add a lot of nice features. Most probably, we'll call for fund raising for this project as soon as we find out how to reliably transfer money to us. Oleg On Sun, 7 Aug 2005, Marc G. Fournier wrote: On Sun, 7 Aug 2005, Joshua D. Drake wrote: Marc G. Fournier wrote: 'k, I'm obviously doing something wrong, since my experiences with sites like fts.postgresql.org indicate things should be *alot* faster then I'm getting ... Well the first thing I would ask is are you running 8.0? My testing shows that Tsearch is pretty abysmal if you are not running 8.0. At least with very large tables. This is one thing I was fearing, especially with the work that Teodor and gang have been putting into it for 8.1 :( Unfortunately, we're currently stuck with 7.4.6 for this, so that is one thing I'm going to have to take into consideration ... Considering the number of rows I am not that surprised but I would be curious to know what type of HD you have? Also correct me if I am wrong but gist indexes are typically very large. Do you have enough work_mem/sort_mem to keep them from going to disk? I'm currently playing in a non-production environment (ie. my desktop machine) just to get a feel for things ... our main server for this is a proper 4G of RAM, sort_mem bump'd up quite nicely, and file system spread over multiple spindles ... Right now, I'm just playing with / learning the tsearch stuff, so am more looking at a 'this is the worst case scenario on my box', and this improves things ... not perfect, but anything I can improve here, I know will be easier to improve on the production server :) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] tsearch2 frequent updates
On Sat, 23 Jul 2005, Steve Atkins wrote: On Sat, Jul 23, 2005 at 02:37:42PM -0400, Matthew Terenzio wrote: If I'm using Tsearch2 to search a public online forum, do you think it's okay to update on every forum entry? Put another way: is to_tsvector() resource intensive when used in this way? Depends on the update rate, I suspect. I found it far too slow for 5+ new messages a second back when it was new. But you're probably handling less than that, and it's improved since then. You should probably benchmark a reaonable test case and see. in 8.1 performance should be certainly improved because we added concurrency recovery suppot to GiST. Currently, whole table should be exclusively locked for every update. Cheers, Steve ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] fts error
On Wed, 13 Jul 2005, marcelo Cortez wrote:
hi folks
the follow script fail
select to_tsquery('hello world ');
- ERROR: syntax error
how to catch this error, any clue?
by definition :)
read http://www.sai.msu.su/~megera/oddmuse/index.cgi/tsearch-v2-intro
Lets attempt to use the function with a string of multiple words:
SELECT to_tsquery('default', 'this is many words');
ERROR: Syntax error
The function can not accept a space separated string. The intention of the
to_tsquery function is to return a type of tsquery used for searching a tsvector field. What we need to do is search for one to many words with some kind of logic (for now simple boolean).
best regards
mdc
__
Correo Yahoo!
Espacio para todos tus mensajes, antivirus y antispam ?gratis!
?Abr? tu cuenta ya! - http://correo.yahoo.com.ar
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Transparent i18n?
Hi there, sorry if just misunderstanding but we have contrib/hstore available from http://www.sai.msu.su/~megera/postgres/gist/ which could be used for storing as many languages as you need. It's sort of perl hash. Oleg On Mon, 4 Jul 2005, David Pratt wrote: Hi Greg. Not sure about this one since I have never made my own type. Do you mean like an ip to country type of situation to guess locale? If so, I am using a ip to country table to lookup ip from request and get the country so language can be passed automatically to display proper language (but I need some translation work done first before I can activate this). I will also use this for black listing purposes and other things so multi purpose. I have got a good part of what I wanted working so far. I am just working on language update delete trigger since there does not appear to be a direct way of surgically removing a specific element from an array in postgres unless I have missed something. For example if I knew spanish was 3rd array in my multi-dimensional array of say 10 lang/translation arrays in the array containing all translations - to remove just this one without having rewrite the array and update the field (which is what I am hoping to complete today). So my language update delete trigger needs to scan the array for lang/translation for deletion, update language key for each language from a reference field (other than for the language being deleted), rewrite the array without the lang/translation that was deleted, and then update the field with rewritten array. Sounds worse that it really is since the multidimensional array containing each lang/translation array is same length and you are performing this by iterating with a loop through records in multi_language table. Further, each translation can be compared by key (for me this is the iso language code). Also, realistically how many times do you need to add and drop languages. And number of languages in use for me will likely never exceed say 20. So this process, even with large numbers of multi-language fields should not be that problematic even if you had say a few thousand text fields fields you wanted translations available for. I think you would still be looking at milliseconds to perform this. This will be an after type trigger (after deletion). I guess I will see what performance is like when I am finished - so far it is pretty fast for adding. You also have a sensible structure for multi_language fields where each one is referenced to multi_language table by id (normalized) with referential integrity (something I was seeking). The only thing not normalized are translations which is okay to me since array structure is dynamic yet keys give you exactly what you want. I am also going to look at Karsten's material shortly to see how his system works but I am interested in following through with what I started first with arrays approach since I am happy with what I am seeing. Regards, David On Monday, July 4, 2005, at 12:06 PM, Greg Stark wrote: I wonder if you could make an SQL type that used text[] as its storage format but had an output function that displayed the correct text for the current locale. Where current locale could be something you set by calling a function at the beginning of the transaction. Do pg_dump and all the important things use the send/receive functions not the input/output functions? so even though this output function loses information it wouldn't cause serious problems? You would still need a way to retrieve all the languages for the cases like administrative interfaces for updating the information. I'm not entirely convinced this would be any better than the alternative of retrieving all of them by default and having a function to retrieve only the correct language. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] tsearch2 and case
On Mon, 4 Jul 2005, Uwe C. Schroeder wrote:
First of all: Happy Independence Day.
I've got a quick question for those with tsearch2 experience.
I set tsearch2 up and it works great (although I'd like to search for phrases
too, but I guess that's not supported at this time).
Anyways, I noted that the search seems to be case sensitive for some search
terms, particularly abbreviations. So if I'm searching with
to_tsquery('ABCD') - where ABCD is a standard abbreviation, I get a lot of
hits, but I get none with to_tsquery('abcd') because the abbreviation is
always upper case in my text.
I'd like the search to be completely case insensitive. Can anyone point me in
the right direction?
use your own dictionary ! Read gendict tutorial for details.
http://www.sai.msu.su/~megera/oddmuse/index.cgi/Gendict
I would create simple dictionary abbrev which recognize your abbreviations
and use it first before any other dicts. btw, if you did it general enough
please share with us, so other people could use it.
Thanks for any help.
UC
--
Open Source Solutions 4U, LLC 2570 Fleetwood Drive
Phone: +1 650 872 2425 San Bruno, CA 94066
Cell: +1 650 302 2405 United States
Fax:+1 650 872 2417
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] tsearch2 errors after db move
On Sun, 3 Jul 2005, Matthew Terenzio wrote: I recently moved a database to a new machine: PostgreSQL 7.4.7 on i386-portbld-freebsd5.3, compiled by GCC cc (GCC) 3.4.2 [FreeBSD] 20040728 any queries related to tsearch2 give me this error: ERROR: cache lookup failed for function 17188 I am running the same versions of Postgres, Freebsd, etc I guess my next move might be to run untsearch2.sql on both machines, do a new dump , and try to rebuild tsearch2 from scratch on on both machines Unless someone knows better? yes, search archives and read about regprocedure_7.4.patch.gz (http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/) ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Finding points within 50 miles
How big is your data ? There are rather sophisticated and very effective methods in astronomy. For example, http://www.sai.msu.su/~megera/oddmuse/index.cgi/SkyPixelization, http://www.sai.msu.su/~megera/oddmuse/index.cgi/pg_sphere Oleg On Mon, 27 Jun 2005, Janning Vygen wrote: Am Montag, 27. Juni 2005 01:40 schrieb CSN: If I have a table of items with latitude and longitude coordinates, is it possible to find all other items that are within, say, 50 miles of an item, using the geometric functions (http://www.postgresql.org/docs/8.0/interactive/functions-geometry.html)? If so, how? I did it without some special features and datatypes some time ago. feel free to modify and use for your own. It should give you an idea how to do it. SELECT c1.zip, c2.zip, 6378.388 * acos( sin(radians(c1.latitude)) * sin(radians(c2.latitude)) + cos(radians(c1.latitude)) * cos(radians(c2.latitude)) * cos(radians(c1.longitude - c2.longitude)) ) AS distance FROM coordinates AS c1 CROSS JOIN coordinates AS c2 I had some problems with the calculation inside acos() sometimes being greater than 1, which should not occur. Please use a CASE WHEN sin(...) 1 THEN 1 ELSE sin(...) END if you have the same problem. kind regards, janning ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] [SQL] Set Membership operator -- test group membership
Look contrib/intarray
On Tue, 14 Jun 2005, Sophie Yang wrote:
Say I have a table tbl1 with two columns:
tbl1(a integer, b integer, c integer)
I want to select the rows in which a and b are members
of a list of integer pairs. The SQL in my mind is
something like:
select * from tbl1 where (a, b) in ((1, 20), (2,
30), (3, 50));
I know the SQL above does not work in PostgreSQL. I
wonder what is the proper way to use in PostgreSQL. I
tried select * from tbl1 where (a, b) in ('{{1, 20},
{2, 30}, {3, 50}}'), and it doesn't work either.
Thanks!
Sophie
__
Yahoo! Mail Mobile
Take Yahoo! Mail with you! Check email on your mobile phone.
http://mobile.yahoo.com/learn/mail
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Regards,
Oleg
_
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Limits of SQL
I'm not sure if it's relevant to your question http://www-2.cs.cmu.edu/~cache/pg_graph/ pg_graph provides a way of handling graph-based data structures within the relational database PostgreSQL. In particular, it provides a convenient means of inserting graphs as BLOB-like objects in the RDBMS. Primarily, however, it provides a mechanism for indexing the graphs to provide efficient means to perform nearest-neighbor queries over collections of graphs. On Thu, 2 Jun 2005, Joachim Zobel wrote: Hi. I am looking for a way to write a SELECT that finds connectivity components of a graph or at least for one that given two nodes determines if there is a path between them. It seems that this is not possible, no matter what graph representation I choose. Which constructs from set theory are missing in SQL? Set of all subsets is one I am missing, or can it be done somehow? Is anybody else thinking about the limits of SQL? As often I am probably not the first to ask these questions. Any pointers? Sincerely, Joachim ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to add an INHERITS to an already populated table
look on pg_inherits table and pg_class.relhassubclass. More info http://www.pgsql.ru/db/mw/msg.html?mid=2044343 On Tue, 31 May 2005, Richard Huxton wrote: David Pradier wrote: Hi everybody, is it possible to add some inheritance lively, without doing a dump/restore ? Not AFAIK. Easiest solution is probably to script some ALTER TABLE ADD COLUMN commands. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to add an INHERITS to an already populated table
On Tue, 31 May 2005, Oleg Bartunov wrote: look on pg_inherits table and pg_class.relhassubclass. More info http://www.pgsql.ru/db/mw/msg.html?mid=2044343 example: create table t (i int4); create table t1 (i int4); create table t2 (i int4); -- mark 't' has children tables update pg_class set relhassubclass='t' where relname='t'; -- get oid of child table 't1' select relfilenode from pg_class where relname='t1'; -- get oid of parent table select relfilenode from pg_class where relname='t'; -- add inheritance t-t1 insert into pg_inherits values(15769046,15769044,1); -- get oid of child table 't2' select relfilenode from pg_class where relname='t2'; -- add inheritance t-t2 insert into pg_inherits values(15769048,15769044,1); --test test=# explain analyze select * from t; QUERY PLAN -- Result (cost=0.00..88.20 rows=5820 width=8) (actual time=0.057..0.057 rows=0 loops=1) - Append (cost=0.00..88.20 rows=5820 width=8) (actual time=0.044..0.044 rows=0 loops=1) - Seq Scan on t (cost=0.00..29.40 rows=1940 width=8) (actual time=0.008..0.008 rows=0 loops=1) - Seq Scan on t1 t (cost=0.00..29.40 rows=1940 width=8) (actual time=0.007..0.007 rows=0 loops=1) - Seq Scan on t2 t (cost=0.00..29.40 rows=1940 width=8) (actual time=0.006..0.006 rows=0 loops=1) Total runtime: 0.171 ms (6 rows) -- check if alter table works alter table t add column x real; test=# \d t Table public.t Column | Type | Modifiers +-+--- i | integer | x | real| test=# \d t1 Table public.t1 Column | Type | Modifiers +-+--- i | integer | x | real| Inherits: t test=# \d t2 Table public.t2 Column | Type | Modifiers +-+--- i | integer | x | real| Inherits: t On Tue, 31 May 2005, Richard Huxton wrote: David Pradier wrote: Hi everybody, is it possible to add some inheritance lively, without doing a dump/restore ? Not AFAIK. Easiest solution is probably to script some ALTER TABLE ADD COLUMN commands. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Tsearch2 lexeme position
On Thu, 12 May 2005, Peter Schmelzer wrote: Hi, this is an answer of thread http://archives.postgresql.org/pgsql-general/2003-08/msg00694.php You say it exist a funktion to fin positions an frequency of lexems for own calculate of an ranking since this time. But where is the description of this? May Im to blind but I spend a lot of time to search in internet for this. http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-guide.html http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-ref.html has to less examples to see it. May it is'nt in?.. I need it to serch terms with more than one lexem and later for an ranking for nearst lexems. ranking functions is already aware of this. See tsearch2.sql in contrib/tsearch2/sql for examples. We don't have API to specify your own ranking function. Do you really need it ? greeds Peter Schmelzer ---(end of broadcast)--- TIP 8: explain analyze is your friend Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Interesting article on transactional algorithms includes
On Tue, 3 May 2005, Dann Corbit wrote: http://www.cs.cmu.edu/~harchol/Papers/actual-icde-submission.pdf thanks, interesting analysis. I've seen many papers where PostgreSQL was used as a research database and wondering why we have no code submission from their projects. For example, materialized views in PostgreSQL 7.3.4 http://research.csc.ncsu.edu/selftune/, code is available, implementing staircase join in PostgreSQL 7.3.3 http://www.inf.uni-konstanz.de/~mayers/, code is available abstract in http://www.ub.uni-konstanz.de/kops/volltexte/2004/1166/ ---(end of broadcast)--- TIP 8: explain analyze is your friend Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Problem with GIST-index and timestamps
On Thu, 28 Apr 2005, [ISO-8859-1] Sebastian B?ck wrote: Hello, i wanted to define and GIST-index on a table with a timestamp-column containing 'infinity' values, and it throws the following error: ERROR: cannot subtract infinite timestamps Is this a known limitation? I don't know. How can i avoid or circumvent this? partial index should works Thanks in advance Sebastian ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] List of Functions
On Thu, 21 Apr 2005, Dinesh Pandey wrote: \df \df functionname also, \df function* Thanks Dinesh Pandey -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Rob Stone Sent: Thursday, April 21, 2005 9:48 AM To: 'pgsql-general@postgresql.org' Subject: [GENERAL] List of Functions I have no idea if this is the correct list to use. How do you obtain a list of the functions and their argument lists that have been created on a Postgres database? I have tried searching various lists and archives plus the documentation. All I want to find is a list of:- Function name return valuearguments. After you run the create or replace script, this data has to be saved somewhere. Just the name of the table would do. Thanks, Robert Stone CONFIDENTIALITY NOTICE AND DISCLAIMER - The information in this transmission may be confidential and/or protected by legal privilege, and is intended only for the person or persons to whom it is addressed. If you are NOT such a person, you are warned that any disclosure, copying or dissemination of the information is unauthorised. If you have received this transmission in error, please advise Park Lane Information Technology -- +61 (03) 9813 1000 -- and permanently delete all copies of this transmission from your mail server(s), mail client(s) and from any physical records. Park Lane Information Technology has no liability (including liability in negligence) for any unauthorised use of the information contained in this transmission. ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Foreign Keys Question
Will inherits helps you ? create table SITE_forum.t1 () inherits (SITE.t); Oleg On Wed, 13 Apr 2005, Matthias Loitsch wrote: First of all, hi. I'm new to this mailing list. I searched this on the net, but I didn't get any usable answers... So here's my problem: I have 2 schemas. One is called SITE, one SITE_forum. What I wanted, is to separate the forum from the whole Site db, so I can put them on different servers if I encounter performance problems or anything. So I started the SITE_forum schema, made all my tables, and realized, that I don't want to create the table users in the schema SITE_forum, because I want to use the exact same users than in the schema SITE. One possibility would be, to create 2 identic tables on both schemas, but that really really is not what I'd like to do. So I thought I could make a foreign key on a different Schema (db), and use the same table And well, thats where I started to search if this is possible ... and, in fact my main question is: Is this a good idea? I have no idea if this will be fast enough, or if I will have lots of problems afterward Could anyone help me with this ? Thanks in advance, Matthias Loitsch Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] Log File Maintainance
On Tue, 12 Apr 2005, Richard Huxton wrote: Inpreet Singh wrote: Hello, I am working on live server where we have installed postgres database as our back end. But now the problem is due to continues work on postgres, size of log files has become problem for us. And the partition where our postgres exists is full. Postmaster is not working now. I tried to rotate log file by adding lines in postgresql.conf syslog = 2 # range 0-2 syslog_facility = 'LOCAL0' syslog_ident = 'postgres' You also need to update your syslog.conf and restart syslogd Add a line like: local0.* /var/log/pgsql probably, better -/var/log/pgsql Read the man page(s) for details. Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] create user with database and contrib
On Sat, 9 Apr 2005, Michal Hlavac wrote: hello, is there some simple way, how to do this??? I need to create user 'diplo' with no database create and no add users privileges... createuser -A -D -P -E -U root diplo after that I create database 'diplo'; I need to import ltree into database 'diplo' with user diplo, but I can't add grant access to language 'c', because only superusers can do that... so, what's the problem ? psql diplo ltree.sql thanks, miso ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] create user with database and contrib
On Sat, 9 Apr 2005, Michal Hlavac wrote: Oleg Bartunov wrote: so, what's the problem ? psql diplo ltree.sql ok, I can add ltree via superuser, but I must grant access for user diplo to every function... it is possible to do that easier? Did you try what I recommend you ? It should works ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] 8.0.1 in a non-standard location and tsearch2
Did you resolve your problem ? On Tue, 29 Mar 2005, Ben wrote: Yes, I did. On Fri, 25 Mar 2005, Oleg Bartunov wrote: Did you try 'make clean' first ? On Thu, 24 Mar 2005, Ben wrote: I'm trying to install tsearch2 into an empty database on a new 8.0.1 postgres install. The machine already has an older 7.4 install of postgres on it, so I gave configure a --prefix=/usr/local/pg801 option. Postgres installed and started fine (after changing the port), and I was able to create my new empty database without issues. Now comes the part where I fail to install tsearch2. I go to the contrib/tsearch2 directory, run make and make install without issues. make installcheck tries to connect to the older postgres install (I don't see an option to set the port it attempts to use), so I try to pipe tsearch2.sql into the new database. It starts working fine, and then says: ERROR: could not find function tsvector_cmp in file /usr/local/pgsql/lib/tsearch2.so which is interesting, because it's not trying to use /usr/local/pg801/ like it's supposed to. Thoughts? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] 8.0.1 in a non-standard location and tsearch2
On Tue, 29 Mar 2005, Ben wrote: Yes by reinstalling to the default location. :) When I get a chance I'll try this again, but I've been too swamped to give it a go. I just tried myself to install pgsql into non-standard location and got no problem. On Tue, 29 Mar 2005, Oleg Bartunov wrote: Did you resolve your problem ? On Tue, 29 Mar 2005, Ben wrote: Yes, I did. On Fri, 25 Mar 2005, Oleg Bartunov wrote: Did you try 'make clean' first ? On Thu, 24 Mar 2005, Ben wrote: I'm trying to install tsearch2 into an empty database on a new 8.0.1 postgres install. The machine already has an older 7.4 install of postgres on it, so I gave configure a --prefix=/usr/local/pg801 option. Postgres installed and started fine (after changing the port), and I was able to create my new empty database without issues. Now comes the part where I fail to install tsearch2. I go to the contrib/tsearch2 directory, run make and make install without issues. make installcheck tries to connect to the older postgres install (I don't see an option to set the port it attempts to use), so I try to pipe tsearch2.sql into the new database. It starts working fine, and then says: ERROR: could not find function tsvector_cmp in file /usr/local/pgsql/lib/tsearch2.so which is interesting, because it's not trying to use /usr/local/pg801/ like it's supposed to. Thoughts? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
