[GENERAL] parser: tokens in tsearch2

m number of characters to avoid _code get split in more than one lexeme. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] tsearch2: setting weights on tsquery

talog.english', coalesce(input1,'')), 'A') || ' ' || setweight(to_tsvector('pg_catalog.english', coalesce(input2,'')), 'B') ) and I won't be able rank on all fields at a time. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

exposing more parse was: Re: [GENERAL] tsearch2: setting weights on tsquery

On Tue, 21 Oct 2008 10:29:52 +0200 Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: I came across this: http://grokbase.com/topic/2007/08/07/general-tsearch2-plainto-tsquery-with-or/r92nI5l_k9S4iKcWdCxKs05yFQk And I find it is strictly related to my needs. Working around ts_parse I could

Re: exposing more parse was: Re: [GENERAL] tsearch2: setting weights on tsquery

On Tue, 21 Oct 2008 13:20:12 +0200 Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > On Tue, 21 Oct 2008 10:29:52 +0200 > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > > I came across this: > http://grokbase.com/topic/2007/08/07/general-tsear

Re: exposing more parse was: Re: [GENERAL] tsearch2: setting weights on tsquery

On Tue, 21 Oct 2008 10:36:20 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > > It would still be nice to be able to directly work with tsvector > > and tsquery so people could exploit the parser, lexer etc... and > &g

[GENERAL] tsearch2: setting weights on tsquery plainto_tsquery with | or and weight

On Tue, 21 Oct 2008 13:40:33 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > > I missed it. Thanks. Nearly perfect. Now I've to understand what > > a {} is. > > An array with a null element? an empty array

Re: [GENERAL] Shopping cart

ld be publicly published. The ones I'm aware of are Zen Cart, OSCommerce, Ubercart and Ecommerce (last 2 for drupal). I think they *may* work with postgresql as well. I think that anyway most of the popular prepackaged solutions don't support transactions in the DB. -- Ivan Sergio Borgono

[GENERAL] where column in ARRAY

Without building up a dynamic query is it possible to: create or replace function t1(a int[]) as $$ ... select * from t1 where c in a; // eg in spite of in (1,2,3); or just obtain a similar effect? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list

Re: [GENERAL] where column in ARRAY

On Wed, 22 Oct 2008 14:46:35 +0200 Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: > Without building up a dynamic query is it possible to: > > create or replace function t1(a int[]) as > $$ > ... > select * from t1 where c in a; // eg in spite of in (1,2,3); >

Re: [GENERAL] Shopping cart

QL and the changes you'll have to do to make it works may be minimal... but somehow core is more DB friendly than most modules so you may have some surprises. Still no popular Free cart I know rely on DB coherency features especially the ones written in PHP. -- Ivan Sergio Borgonovo http://www.

[GENERAL] gin index and same query misteriously slowing down on a nearly-readonly DB

c. And then it goes no further. I've been able to vacuum full dropping the gin index and then vacuum and vacuum full... but it is still very very slow. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

Re: [GENERAL] Database design for separate tsearch table

h magic would > require me to store the full name right there in the table, or am > I mistaken? You'll have to build up a tsvector for each language, so yeah it may be useful to store the tsvector together with the language with witch it was obtained. If you don't tsearch won't wor

Re: [GENERAL] Database design for separate tsearch table

ify that? I know the general differences between > gist and gin, but not how it affects weighted searches... http://www.postgresql.org/docs/8.3/static/textsearch-indexes.html search for @@@ -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] overhead of plpgsql functions over simple select

pared to a simple select? I'm not that worried of old query plans. thx -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] overhead of plpgsql functions over simple select

e simple tests and it looks as being roughly 3 time faster. With higher numbers the difference seems to get smaller, maybe because of the higher cost of allocating memory caused by generate_series(?). So I know that immutable simple(?) functions are much faster in sql... anything else to avoid

Re: [GENERAL] tsearch2 problem

ere is some superposition with English. Till now it looks as an acceptable compromise but I wouldn't like to have surprises before I find the resources to actually do what should be done (fully support the 2 languages). -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pg

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

a stored in a format that doesn't require a long queue of tools to be read. I do really hate dependencies that translates in hours of *boring* work if something turn bad. BTW I gave a glance to MonetDB papers posted earlier and it seems that compression algorithms are strongly r

Re: [GENERAL] Are there plans to add data compression feature to postgresql?

than just TOAST. eg. if there are several columns that are frequently updated together... I'd say that compression could be one more tool for managing data integrity not that it will inevitably have a negative impact on it (nor a positive one if not correctly managed). What am I still missing? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] raise notice, psql and having some feedback from scripts

I just have some batch work in scripts that I pass through: psql < script.sql &>script.log & or may be run by cron. In sql raise notice is not available. Is there any other way to send messages to the logs without polluting them too much with -a? -- Ivan Sergio

[GENERAL] gin index creation performance problems

an 1h. maintenance_work_mem is still untouched. What would be a good value to start from? Anything else to do to improve performances? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] gin index creation performance problems

On Mon, 3 Nov 2008 16:45:35 +0100 Ivan Sergio Borgonovo <[EMAIL PROTECTED]> wrote: Forgot to add that top say postgresql is using 100% CPU and 15% memory. > I'm looking for a bit more guidance on gin index creation. > > The process: > - vaccum analyze. > - start a tran

Re: [GENERAL] gin index creation performance problems

On Mon, 03 Nov 2008 11:04:45 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > > maintenance_work_mem is still untouched. What would be a good > > value to start from? > GIN index build time is *very* sensitive to mainte

[GENERAL] gin creation and previous history of server

tion succede it is definitively slower than 3x gist/gin index creation looks more cpu bounded than memory bounded. I'm checking if I made some mistake in other cfg parameters that may have some impact on index creation... Any further clue? -- Ivan Sergio Borgonovo http://www.webthatwor

Re: [GENERAL] COPY TO duplicates "\" signs

; D:\\Program Files\\BMC Software\\CONTROL-D\\wa/reports > == ==== == try to play with the ESCAPE AS and set it as '' if you really want to skip \. http://www.postgresql.org/docs/8.1/static/sql-copy.html -- Ivan Sergio Borgonovo http://www.webthatwork

Re: [GENERAL] gin creation and previous history of server

On Tue, 04 Nov 2008 10:33:26 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > > It seems that gin creation is triggering something nasty in the > > server that depends on previous history of the server. > > Can you p

Re: [GENERAL] gin creation and previous history of server

On Wed, 05 Nov 2008 10:53:38 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > > Tom Lane <[EMAIL PROTECTED]> wrote: > >> Can you put together a self-contained test case that illustrates > >> this? &g

Re: [GENERAL] Specifying text to substitute for NULLs in selects

drop cascades to table test.test DROP SCHEMA everything clearly explained in the COPY manual: http://www.postgresql.org/docs/8.1/static/sql-copy.html -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Specifying text to substitute for NULLs in selects

d input syntax for integer: "NA" furthermore... even if c1 was text you may end up in output like: 'NA' that will be hard to be discerned from a "normal" string. BTW I just discovered that COPY doesn't work on view. -- Ivan Sergio Borgonovo http://www.webthatwork

Re: [GENERAL] Specifying text to substitute for NULLs in selects

;', and all NULLs will from then on be output as NA. > > The COPY option is closest to a generic setting, but doesn't work > with a select query, just a table dump. \copy (select ) to ... works. As written in my 2nd post. -- Ivan Sergio Borgonovo http://www.webthatworks.it

[GENERAL] options for launching sql script asynchronously from web app

I'd like to launch some sql script asynchronously from a web app and have some kind of feedback later. Some form of authentication would be a plus. Is there anything ready? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-ge

Re: [GENERAL] options for launching sql script asynchronously from web app

s. I don't like it... but I've done it before and I'm still alive. I just have to trigger an event that run asynchronously, avoid other events of the same kind are triggered while one is running and report back event status. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.i

Re: [GENERAL] Fulltext index

deas? Multicolumn indexes should get into 8.4. You may add a column tsvector and compute it with a trigger that chose the correct language when generating the tsvector. Then you'll have to pick up the correct language when you generate the tsquery in your search. http://www.sigaev.ru/gin/fastin

[GENERAL] psql exit code

I'm running: ON_ERROR_STOP="on" PGPASSFILE="/somewhere" psql dbname username -f script.sql (or alternatively http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] psql exit code

On Mon, 10 Nov 2008 10:56:57 + Richard Huxton <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo wrote: > > I'm running: > > > > ON_ERROR_STOP="on" PGPASSFILE="/somewhere" psql dbname username > > -f script.sql > > (or alt

[GENERAL] still gin index creation takes forever

ns mysteriously even when there is no process stealing CPU cycles. Anyway when I'm creating a gin index CPU use is very high staying constantly near 100%. Any suggestion about how to track down the problem? thanks [1] temporary but not strictly temp tables -- Ivan Sergio Borgonovo http:/

Re: [GENERAL] still gin index creation takes forever

On Tue, 11 Nov 2008 22:02:17 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > > Any suggestion about how to track down the problem? > > What you are describing sounds rather like a > use-of-uninitialized-memory problem

[GENERAL] sequence not restarting in a for loop (actually semi-random numbers)

esult I was expecting? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] sequence not restarting in a for loop (actually semi-random numbers)

On Wed, 12 Nov 2008 11:17:03 -0500 Tom Lane <[EMAIL PROTECTED]> wrote: > Ivan Sergio Borgonovo <[EMAIL PROTECTED]> writes: > > insert into mytop (id,n) select id, nextval('tmp_seq') > > from biglist join mylist on biglist.id=mylist > > order b

Re: [GENERAL] still gin index creation takes forever

uot;;3920;4166 "del";3092;3281 "edizioni";2465;2465 "della";2292;2410 "m";2283;2398 "dell";2150;2281 "j";1967;2099 "d";1789;1864 "per";1685;1770 "longman";1671;1746 "le";1656;1736 "press";1687;

Re: [GENERAL] still gin index creation takes forever

Yes... 6min compared to something that span a night and is far more than what I'm willing to wait to give an exact measure since it does look to last more than the box itself. Anyway... I'll try Teodor's trick to see if somehow it can circumvent the real cause and I'll try ev

Re: [GENERAL] still gin index creation takes forever

in on a 2x Xeon HT 3.2GHz, 4Gb RAM and SCSI RAID1. It's far from being a scientific measure. I'll try to do more experiments later to collect more data and see if it didn't happen by chance. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] tracking down a warning

t how can I find the statement? Which is the right log config to tweak to get enough info to be able to use grep on my code base without producing 2Gb logs in 5min? BTW can this log config be tweaked dynamically? by connection etc... or just in postgres.conf? thanks -- Ivan S

Re: [GENERAL] transaction_read_only effects on performance, actual meaning (was: How to reduce impact of a query)

UES ('oats'), ('wheat'), ('beans'); > > SET transaction_read_only = 1; > > SELECT * FROM a; > > COMMIT; > > > > but it does. Interesting. Thank you for pointing it out. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] long vacuum full, gin index and unusually long delete

problems... for all the other activities the box seems to behave happily... including some other heavy weight activities where postgresql is involved. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chan

[GENERAL] case when evaluating else condition anyway?

string to null and everything else to a timestamp. Is there any cleaner functional way that doesn't involve prepared statement etc... since the whole exercise is caused by an null-impaired DB API (aka MySQLish). -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via p

Re: [GENERAL] case when evaluating else condition anyway?

On Wed, 26 Nov 2008 14:18:44 + Sam Mason <[EMAIL PROTECTED]> wrote: > On Wed, Nov 26, 2008 at 02:53:07PM +0100, Ivan Sergio Borgonovo > wrote: > > case when ''=extinput then null else extinput::timestamp end > > I'd tend to use nullif(extinput,'&

Re: [GENERAL] pk vs unique not null differences between 8.3.4 and 8.3.8

On Mon, 02 Nov 2009 09:53:06 -0500 Tom Lane wrote: > Ivan Sergio Borgonovo writes: > > This statement succede in 8.3.8 > > alter table shop_commerce_gift drop constraint > > shop_commerce_gift_pkey; > > but failed in 8.3.4. > > Uh, that doesn't seem to mat

[GENERAL] xml import/export tools and performance

thing that is more like a tagged csv. Currently I'm mostly looking around to see where it would be convenient to move the boundaries of the problem. I'd be curious about what kind of performance impact they may have compared to COPY. thanks -- Ivan Sergio Borgonovo http://www.webthatwo

[GENERAL] knowing which table/schema is going to be chosen

; In this case test.mytable will be chosen. Is there a way to ask postgresql the schema of the table that will be chosen? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] knowing which table/schema is going to be chosen

On Thu, 12 Nov 2009 10:38:27 + Richard Huxton wrote: > Ivan Sergio Borgonovo wrote: > > I have a search_path that may not just consist of $user, public. > > eg. > > $user, public, test, import > > > > I'd like to know which table is going to be chos

[GENERAL] impersonating a user/ownership problems

ainful. create sequence alter table ... owner to alter sequence ... owned by ... alter table ... set default ... Is there a less painful approach? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to yo

Re: [GENERAL] passing parameters to multiple statements

. That's shooting yourself in the head without even knowing the reason. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] passing parameters to multiple statements

On Wed, 18 Nov 2009 11:38:46 +0100 Pavel Stehule wrote: > 2009/11/18 Ivan Sergio Borgonovo : > > On Tue, 17 Nov 2009 20:16:36 -0800 > > David Fetter wrote: > > > >> On Tue, Nov 17, 2009 at 09:33:05AM -0700, Konstantin Izmailov > >> wrote: > >>

[GENERAL] advocating pgsql was:passing parameters to multiple statements

accent on the "social" part of it and on the fall back on our beloved DB. I think there are far more people knowing just one DB and badly than companies with such strict (insane) policies and... well PostgreSQL is very standard compliant. I'd beat another horse ;) -- Ivan Sergi

[GENERAL] duplicating a schema

roach? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] duplicating a schema

On Tue, 1 Dec 2009 11:39:06 +0900 Schwaighofer Clemens wrote: > On Sun, Nov 29, 2009 at 21:21, Ivan Sergio Borgonovo > wrote: > > I need to create a new schema with all the content in an existing > > one, just with a new name. > > The way I've found is: > >

Re: [GENERAL] duplicating a schema

On Tue, 1 Dec 2009 09:53:12 +0100 Ivan Sergio Borgonovo wrote: > On Tue, 1 Dec 2009 11:39:06 +0900 > Schwaighofer Clemens wrote: > > > On Sun, Nov 29, 2009 at 21:21, Ivan Sergio Borgonovo > > wrote: > > > I need to create a new schema with all the content in an &

Re: [Bacula-users] [GENERAL] Catastrophic changes to PostgreSQL 8.4

system that has a different locale from the one the backup was made on? Can I use regexp? Can accents be ignored during searches? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] flagging first row inserted for each "group" of key

to move to 8.4 shortly, so no windowing functions that will make reporting easier/faster. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] flagging first row inserted for each "group" of key

On Thu, 17 Dec 2009 10:38:32 +0100 "A. Kretschmer" wrote: > In response to Ivan Sergio Borgonovo : > > I've a web application. > > > > I'm logging data related to the landing page and subsequent > > selected hits. > > > > create ta

[GENERAL] pg_dump excluding tables content but not table schema

I've some tables that are just cache. I'd like to just dump the table schema without dumping the table contend. I think I could do it in 2 steps but I'd like to avoid it. Is there a way? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-gener

Re: [GENERAL] pg_dump excluding tables content but not table schema

On Mon, 28 Dec 2009 19:39:36 + Raymond O'Donnell wrote: > On 28/12/2009 18:41, Ivan Sergio Borgonovo wrote: > > > I'd like to just dump the table schema without dumping the table > > contend. > pg_dump -s -t My fault. I was not clear enough. I'd

Re: [GENERAL] pg_dump excluding tables content but not table schema

On Mon, 28 Dec 2009 21:20:17 +0100 Ivan Sergio Borgonovo wrote: > pg_dump -Fc -Z9 -s -t *.cache* -d mydb > schema_only.bak > pg_dump -Fc -Z9 -T *.cache* -d mydb > nearly_full.bak > cat nearly_full.bak schema_only.bak | pg_restore -1 -d mydb > It seems it is working... I

Re: [GENERAL] Optimized Select Statement

Is that what you were looking for? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] how much left for restore?

tweak in pg config to make it faster? For dev only... could I just stop the dev server, copy the *files* on flash and mount them on the notebook? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

[GENERAL] aggregate over tables in different schema

r than building dynamically the statement as a list of union or building up a view is there any other way? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] R: aggregate over tables in different schema

ore the children should have their own pk and not share them. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] how much left for restore?

On Mon, 11 Jan 2010 12:30:45 -0500 Francisco Reyes wrote: > Ivan Sergio Borgonovo writes: > > > Is there a way to know/estimate how much is left to complete a > > restore? > > Not sure on plain ASCII files but if your pg_dump used Fc then at > restore you can pass

Re: [GENERAL] how much left for restore?

On Mon, 11 Jan 2010 18:36:18 + Sam Mason wrote: > On Fri, Jan 08, 2010 at 11:28:15AM +0100, Ivan Sergio Borgonovo > wrote: > > Is there a way to know/estimate how much is left to complete a > > restore? > maybe something like "pv" would help? > http:

[GENERAL] OR tsquery

7;orange' The only thing I can think of is looping on ts_lexize that looks not very efficient in plpgsql. Am I missing something? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] OR tsquery

other option would be to return the tsvector to the client and then build the tsquery there and send it back to the server. I'm on 8.3 but I don't think it makes any real difference for this. Sorry if I'm still missing the obvious. -- Ivan Sergio Borgonovo http://www.webthatworks.i

[GENERAL] ranking how "similar" are tsvectors was: OR tsquery

cess to elements of a tsvector will completely solve the problem since tsvectors store positions too, but it will be a step forward in making easier to compare documents to find similar ones. An operator that check the intersection of tsvectors would come handy. Adding a ts_rank(tsvector, tsvecto

Re: [GENERAL] ranking how "similar" are tsvectors was: OR tsquery

ic. Unfortunately, we had no sponsor for > full-text search for last year and I see no perspectives this > year, so we postpone our text-search development. Good luck. Do you have anything like http://www.chipin.com/ for small donations? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] C: extending text search: from where to start

I'd like to extend full text search so that I can transform tvectors in tquery and have direct access to a tvector as a record/array. I'm on Debian. This is my first experience with pg source code. I'd appreciate any pointer that will quickly put me on the right track. thanks

Re: [GENERAL] C: extending text search: from where to start

On Tue, 19 Jan 2010 10:12:21 +0100 Dimitri Fontaine wrote: > Ivan Sergio Borgonovo writes: > > I'd appreciate any pointer that will quickly put me on the right > > track. > > I'd guess you begin here: > http://wiki.postgresql.org/wiki/Developer_FAQ With

[GENERAL] more docs on extending postgres in C

onment and best practices on Debian, something that could help me to compile, install, test easily on Debian. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www

Re: [GENERAL] more docs on extending postgres in C

On Wed, 20 Jan 2010 16:56:04 +0100 Dimitri Fontaine wrote: > Ivan Sergio Borgonovo writes: > > I haven't been able to find anything better than the online > > manual and pg source code to learn how to write extensions. > > Maybe this will help: > ht

Re: [GENERAL] more docs on extending postgres in C

e once during the first call of the function. I grep throu contrib and I wasn't able to find anything that really enlighted me about BlessTupleDesc. I'll try to see if tomorrow things will look clearer. thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-gener

Re: [GENERAL] PgSQL problem: How to split strings into rows

; moe http://www.postgresql.org/docs/current/static/functions-array.html string_to_array select (string_to_array('tano pino gino', ' '))[i] from generate_series(1, 3) s(i); You'd get the idea... to get the length of the array you've array_length. -- Ivan Sergio Borgonov

Re: [GENERAL] more docs on extending postgres in C

On Wed, 20 Jan 2010 17:43:27 +0100 Adrian von Bidder wrote: > On Wednesday 20 January 2010 15.42:14 Ivan Sergio Borgonovo wrote: > > I'd also appreciate some suggestion about dev environment and > > best practices on Debian, something that could help me to > > compil

Re: [GENERAL] more docs on extending postgres in C

On Fri, 22 Jan 2010 11:02:46 -0500 Tom Lane wrote: > Ivan Sergio Borgonovo writes: > > If I had to build stuff in the pg source tree I'd just clone a > > contrib directory and change the makefile [1]. What am I > > supposed to do if I'd like to create a contrib

[GENERAL] not officially documented use of setweight??

of this use in official postgres docs. The docs just says: setweight(vector tsvector, weight "char") returns tsvector Am I missing them? Is that use "supported" in future versions? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing l

Re: [GENERAL] not officially documented use of setweight??

On Mon, 25 Jan 2010 12:01:04 +0100 Ivan Sergio Borgonovo wrote: > I think I've learned how to use pg text search from Oleg and Teodor > documentation since I've found on my code this use of setweight: > > query := query && > setweight(configuration, 'ban

Re: [GENERAL] FTS uses "tsquery" directly in the query

7;ll have to use | I expect a lot of returned rows) to make efficient use of the gin index and avoid to compute ts_rank for too many rows. Don't hold your breath waiting... but let me know if you're interested so I don't have to be the only one posting newbies questions on p

Re: [GENERAL] FTS uses "tsquery" directly in the query

t a couple of functions that: - will return a tsvector as a weight int, pos int[], lexeme text record - will turn a tsvector + operator into a tsquery 'orange':A1,2,3 'banana':B4,5 'tomato':C6,7 -> 'orange':A | 'banana':B | 'tomato

[GENERAL] tsvector minimum match using index

the rank for each of them. -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] is this the correct result for ts_rewrite? reducing tsquery to improve performance?

#x27;java':ABC (1 row) test=# select 'java:A | java:B | java:C'::tsquery; tsquery ( 'java':A | 'java':B ) | 'java':C (1 row) I did try to pass them through nodetree... but the result keeps on being different. --

Re: [GENERAL] How to generate unique hash-type id?

th something like: http://www.webthatworks.it/d1/node/page/pseudo_random_sequences_postgresql -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] reducing result set of tsvector @@ tsquery avoiding to use ts_rank

ing on ts_rank... especially suggestions that will exploit the index. So any suggestion that could reduce the result set before filtering on rank is welcome and I'll try to put them in practice in some C functions that taken a tsvector build up a tsquery to be used to find similar do

Re: [GENERAL] Query to find list of dates between two dates

e this select date '2008-05-01' + i from generate_series(0, (date '2009-12-10' - date '2008-05-01')) s(i); -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your sub

[GENERAL] turning a tsvector without position in a weighted tsvector

sitions to tsvectors without positions? Is there any use-case? -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] turning a tsvector without position in a weighted tsvector

Since I'm writing some helper to manipulate tsvectors I was wondering if a) there is any reasonable use case of adding weights to vectors with no position b) I missed any obvious way to add weights to tsvectors that were initially without positions thanks -- Ivan Sergio Borgonovo http://www.w

Re: [GENERAL] How do I delete duplicate rows in a table?

earned it here... -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] border case ::tsvector vs. to_tsvector was turning a tsvector without position in a weighted tsvector

meaningful positions if you'd like to assign a weight to a tsvector with no positions. I still wonder if it would be reasonable to write a function that forcefully assign a position and a weight to vectors to be used with ts_rank. I've some ideas about possible use cases but I'

[GENERAL] LIKE a set of pattern generated from a table

7; || field2 from origin where ... union select 'constB,' || field1 || ',' || field2 from origin where ... ); -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] C function to create tsquery not working

'java:1A,2B tano:3C,4D', '|', 'ABC', 100) -> java:AB | tano:C I've made some improvement compared to previous version I've posted but still it returns an empty tsquery. Things that works: - tsvector_tsquery_size returns reasonable total length of strings

[GENERAL] [SOLVED] C function to create tsquery not working

On Thu, 11 Feb 2010 20:11:54 +0100 Ivan Sergio Borgonovo wrote: > I'm still having trouble making this work: > http://pgsql.privatepaste.com/14a6d3075e Finally I got it working, not the above version anyway... CREATE OR REPLACE FUNCTION tsvector_to_tsquery(IN tsv tsvector, o

[GENERAL] errmsg and multi-byte strings.

How am I supposed to output multibyte strings in an errmsg (and Co.) as in errmsg("operator not permitted '%s'", mbstring) thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes

[GENERAL] C function manipulating tsquery doesn't work with -O2

0x00536458 in ExecProcNode () #6 0x00534337 in ExecutorRun () #7 0x005d6b2b in ?? () #8 0x005d8339 in PortalRun () #9 0x005d2de9 in ?? () #10 0x005d4624 in PostgresMain () #11 0x0000005a6c68 in ?? () #12 0x005a7b30 in PostmasterMain

Re: [GENERAL] C function to create tsquery not working

On Thu, 11 Feb 2010 20:11:54 +0100 Ivan Sergio Borgonovo wrote: > I'm still having trouble making this work: > > http://pgsql.privatepaste.com/14a6d3075e I tried to play with item->operator.left to see if reshuffling the expression could make any difference. item->operat

<    1   2   3   4   5   6   >