[GENERAL] status of inheritance
I designed a schema which would benefit from inheritance (in particular, an index over more than one table). However, I read that inheritance is not really implemented yet. So I simulated it with extra indexes and extra joins. Is there any plan / timetable for implementing inheritance (with proper index support)? signature.asc Description: This is a digitally signed message part
[GENERAL] with or without timezone?
I am designing a new schema from scratch. Which type is more natural to work with, a timestamp with a timezone or without a timezone? I read the 7.4 documentation on timezones two or three times but I'm still not really sure. The fact that the default is without time zone suggests that without is more natural. But I have a vague preference for with time zone. Can someone push me in the right direction? -- If you are an American then support http://fairtax.org (Permanently replace 50,000+ pages of tax law with about 200 pages.) signature.asc Description: This is a digitally signed message part
[GENERAL] tsearch2 questions
1. What is the advantage of the tsearch2() trigger? Why can't I write my own trigger which does approximately: UPDATE manuscript set manuscript_vector = setweight(to_tsvector(manuscript_genre), 'A') || setweight(to_tsvector(manuscript_title), 'B') || to_tsvector(manuscript_abstract); 2. Is there a way to know in advance the maximum return value of the rank function? I have lots of other information to include in the goodness-of-match score besides the fulltext match rank so I would prefer a tsearch2 rank score between 0 and 1. Do I need to write my own rank function? -- Make April 15 just another day, visit http://fairtax.org ---(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 Wed, Jul 04, 2007 at 10:59:46AM +0400, Oleg Bartunov wrote: > On Wed, 4 Jul 2007, Joshua N Pritikin wrote: > >1. What is the advantage of the tsearch2() trigger? Why can't I write my > >own trigger which does approximately: > > no advantage, it's just an example. Please mention that in the documentation: tsearch2() trigger used to automatically update vector_column_name, my_filter_name is the function name to preprocess text_column_name. There are can be many functions and text columns specified in tsearch2() trigger. The following rule used: function applied to all subsequent text columns until next function occurs. Example, function dropatsymbol replaces all entries of @ sign by space. tsearch2() is an example. You are welcome to write your own trigger. > >2. Is there a way to know in advance the maximum return value of the > >rank function? I have lots of other information to include in the > >goodness-of-match score besides the fulltext match rank so I would > >prefer a tsearch2 rank score between 0 and 1. Do I need to write my own > >rank function? > > what's about simple normalization formulae, like rank/(rank+1) ? I think you are suggesting that I use the best rank as the denominator for the rank column. Yes, I suppose that will work. Thanks. -- Make April 15 just another day, visit http://fairtax.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] tsearch2 questions
On Wed, Jul 04, 2007 at 10:40:11AM +0200, hubert depesz lubaczewski wrote: > On 7/4/07, Joshua N Pritikin <[EMAIL PROTECTED]> wrote: > >Please mention that in the documentation: > > dont you think this is perfeclty clear? > > "If you want to do something specific with columns, you may write your very > own trigger function using plpgsql or other procedural languages (but not > SQL, unfortunately) and use it instead of tsearch2 trigger." >From where are you quoting? I was quoting from: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-ref.html > >what's about simple normalization formulae, like rank/(rank+1) ? > >I think you are suggesting that I use the best rank as the denominator > >for the rank column. Yes, I suppose that will work. > > actually oleg supposed not to use best rank, but just use the formula as > given - rank/(rank+1) to get rank in range of 0 to 1. OK, then what does the +1 mean in your formulae? Consider these results from [1]. rank/(rank+1): 0.19/.1 = 1.9, .1/.1 = 1, etc. That doesn't make sense. The reciprocal also doesn't make sense. So what does Oleg mean? I was guessing that Oleg meant to divide the rank column by the first rank, that is, by 0.19 so you would get 1, .52, .52, etc. id | headline| rank +---+-- 3 | crawling over cobbles in a low passage. | 0.19 1 | crawl over cobbles leads inward to the west. | 0.1 4 | passages lead east, north, and south. | 0.1 5 | crawl slants up. | 0.1 7 | passage here is blocked by a recent cave-in. | 0.1 Am I being stupid? [1] http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-guide.html -- Make April 15 just another day, visit http://fairtax.org ---(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 questions
On Wed, Jul 04, 2007 at 11:08:21AM +0200, hubert depesz lubaczewski wrote: > On 7/4/07, Joshua N Pritikin <[EMAIL PROTECTED]> wrote: > >From where are you quoting? I was quoting from: > > > >http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-ref.html > > i was quoting file > http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html So that one is fine. Only the reference could use some clarification. > >actually oleg supposed not to use best rank, but just use the formula as > >> given - rank/(rank+1) to get rank in range of 0 to 1. > >OK, then what does the +1 mean in your formulae? Consider these results > >from [1]. rank/(rank+1): 0.19/.1 = 1.9, .1/.1 = 1, etc. That doesn't > >make sense. The reciprocal also doesn't make sense. So what does Oleg > >mean? I was guessing that Oleg meant to divide the rank column by the > >first rank, that is, by 0.19 so you would get 1, .52, .52, etc. > > +1 means: add one to. > for example: for rank = 0.1 you get: 0.1/(0.1+1) = 0.1/1.1 = 0.0909 > for rank = 0.5 you get: 0.5/(0.5+1) = 0.5/1.5 = 0. D'oh! I see. > i think that notation: rank+1 is pretty readable. > > additionally - sorry but i dont understand your calculations. what is > 0.19/.1 > ? how did you get the .1? I was imagining that "rank+1" was the second row of the rank column. Sorry for the confusion. -- Make April 15 just another day, visit http://fairtax.org ---(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
[GENERAL] tsearch2 in multilingual database?
Sometime in the future, I anticipate storing other languages in addition to English in my database to be indexed with tsearch2. set_curcfg() seems to be per-session. Will I need to call set_curcfg() every time I switch languages? -- Make April 15 just another day, visit http://fairtax.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] how does a temp table work?
I read the docs but I'm still not sure. If I create a temp table with ON COMMIT DROP then is that table private to the transaction? In other words, if the temp table is created in plpgsql stored procedure "foo" and foo is executed from different transactions in parallel then each transaction will see its own private temp table, right? -- Make April 15 just another day, visit http://fairtax.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] order is preserved by outer select?
Here's another easy (stupid?) question: SELECT data.* FROM (SELECT * FROM foo ORDER BY bar) AS data Will the returned rows still be ordered by bar? -- Make April 15 just another day, visit http://fairtax.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] redirecting output of pg_dump
On Wed, Jul 18, 2007 at 03:39:01PM +0530, Ashish Karalkar wrote: > I want to take backup from one server and save it to another machine hard > drive. > The backup will be taken through a shell script attached to a cron job. > > something like: > > pg_dump -d postgres -U postgres -f save the file on that machine > Can you use ssh? For example: ssh -x nirmalvihar.info pg_dump -F c ppx > `date +%Y%m%d` -- Make April 15 just another day, visit http://fairtax.org ---(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