Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
Hi,
From: Zoltan Boszormenyi [EMAIL PROTECTED]
Subject: Re: [PATCHES] WITH RECURSIVE patch V0.1
Date: Sun, 18 May 2008 23:22:02 +0200
But I have a little problem with the output.
If it's not obvious, here is the query tweaked a little below.
...
Can we get the rows in tree order, please? I.e. something like this:
?column?
--
38
15
10
5
2
3
17
9
8
6
26
13
1
12
18
11
7
(17 rows)
No, you can't. However, you can obtain recursive path by using ARRAY
type, as another way. Here is a sample SQL.
WITH RECURSIVE x(level, parent, child, path) AS
(SELECT 1::integer, * , array[child] FROM test_connect_by
WHERE parent IS NULL
UNION ALL
SELECT x.level + 1, base.*, array_append(path, base.child)
FROM test_connect_by AS base, x WHERE base.parent = x.child
)
SELECT path, array_to_string(path, '-') FROM x
WHERE NOT EXISTS (SELECT 1 FROM test_connect_by WHERE parent =
x.child);
path | array_to_string
-+-
{18,11} | 18-11
{18,7} | 18-7
{26,13} | 26-13
{26,1} | 26-1
{26,12} | 26-12
{38,6} | 38-6
{38,17,9} | 38-17-9
{38,17,8} | 38-17-8
{38,15,10} | 38-15-10
{38,15,5,2} | 38-15-5-2
{38,15,5,3} | 38-15-5-3
(11 rows)
Regards,
--
Yoshiyuki Asaba
[EMAIL PROTECTED]
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Updated patch (Re: [PATCHES] WITH RECURSIVE patch V0.1)
On Sun, May 18, 2008 at 08:51:29PM +0900, Tatsuo Ishii wrote: WITH RECURSIVE patch V0.1 Please find updated patch with bug fixes from Yoshiyuki Asaba and Michael Meskes. Any mistakes in it are mine. :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate recursive_query-2.patch.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] \df displaying volatility
On May 23, 2008, at 8:57 PM, Joshua D. Drake wrote: Alvaro Herrera wrote: Would anyone object to \df displaying a function's volatility? Maybe limit it to \df+? Ideally we would have a short header for the column so that it doesn't take too much space, and specify the setting with a single letter. The meaning of each letter we could display at the bottom of the table as a footer (something we were going to do for \z too I think?) Thoughts? I think it would be about time :) +1 I'd like to see the function comment as well in \df+. (And probably for most database objects that don't already show the comment.) Michael Glaesemann [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] May Commitfest is done!
Am Mittwoch, 21. Mai 2008 schrieb Richard Huxton: Is there a tag in the CVS to mark this point, or better still a tarball that people like me can check out and play with over the next month or two? There is not. But the density of commits is not so high, so you should be able to target this state by timestamp. Especially as there will be less (invasive) commits in theory during the noncommitfest times, any repository state between now and July is good for testing. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] TODO item: Have psql show current values for a sequence
On Sat, May 24, 2008 at 2:25 AM, daveg [EMAIL PROTECTED] wrote: On Sat, May 24, 2008 at 12:27:16AM -0300, Dickson S. Guedes wrote: Hi all, These patch implements the TODO item: Have psql show current values for a sequence. Comments are welcome. Sequence public.foo_bar_seq +---+-+-+ |Column | Type |Value| +---+-+-+ | sequence_name | name| foo_bar_seq | | last_value| bigint | 11 | | start_value | bigint | 1 | | increment_by | bigint | 1 | | max_value | bigint | 9223372036854775807 | | min_value | bigint | 1 | | cache_value | bigint | 1 | | log_cnt | bigint | 31 | | is_cycled | boolean | f | | is_called | boolean | t | +---+-+-+ Is it now the style to draw a complete box around /d* displays? Or can we dispense with the top and bottom rows of dashes? Hi Dave, This box around the display is because I'm using \pset border 2. -- []s Dickson S. Guedes - Projeto Colmeia - Curitiba - PR +55 (41) 3254-7130 ramal: 27 http://makeall.wordpress.com/ http://pgcon.postgresql.org.br/ http://planeta.postgresql.org.br/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PERFORM] Posible planner improvement?
Moving to -hackers... On May 21, 2008, at 9:09 AM, Richard Huxton wrote: Luke Lonergan wrote: The problem is that the implied join predicate is not being propagated. This is definitely a planner deficiency. IIRC only equality conditions are propagated and gt, lt, between aren't. I seem to remember that the argument given was that the cost of checking for the ability to propagate was too high for the frequency when it ocurred. Of course, what was true for code and machines of 5 years ago might not be so today. Definitely... How hard would it be to propagate all conditions (except maybe functions, though perhaps the new function cost estimates make that more practical) in cases of equality? For reference, the original query as posted to -performance: select * from t1, t2 where t1.id 158507 and t1.id = t2.id; That took 84 minutes (the query was a bit longer but this is the part that made the difference) after a little change the query took ~1 second: select * from t1, t2 where t1.id 158507 and t2.id 158507 and t1.id = t2.id; -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] [GENERAL] Fragments in tsearch2 headline
Now I understand the code much better. A few more questions on headline
generation that I was not able to get from the code:
1. Why is hlparsetext used to parse the document rather than the
parsetext function? Since words to be included in the headline will be
marked afterwords, it seems more reasonable to just use the parsetext
function.
The main difference I see is the use of hlfinditem and marking whether
some word is repeated.
The reason this is important is that hlparsetext does not seem to be
storing word positions which parsetext does. The word positions are
important for generating headline with fragments.
2.
I would prefer the signature ts_headline( [regconfig,] text, tsquery
[,text] )and function should accept 'NumFragments=N' for default
parser. Another parsers may use another options.
Does this mean we want a unified function ts_headline and we trigger the
fragments if NumFragments is specified? It seems that introducing a new
function which can take configuration OID, or name is complex as there
are so many functions handling these issues in wparser.c.
If this is true then we need to just add marking of headline words in
prsd_headline. Otherwise we will need another prsd_headline_with_covers
function.
3. In many cases people may already have TSVector for a given document
(for search operation). Would it be faster to pass TSVector to headline
function when compared to computing TSVector each time? If that is the
case then should we have an option to pass TSVector to headline
function?
-Sushant.
On Sat, 2008-05-24 at 07:57 +0400, Teodor Sigaev wrote:
[moved to -hackers, because talk is about implementation details]
I've ported the patch of Sushant Sinha for fragmented headlines to pg8.3.1
(http://archives.postgresql.org/pgsql-general/2007-11/msg00508.php)
Thank you.
1 diff -Nrub postgresql-8.3.1-orig/contrib/tsearch2/tsearch2.c
now contrib/tsearch2 is compatibility layer for old applications - they don't
know about new features. So, this part isn't needed.
2 solution to compile function (ts_headline_with_fragments) into core, but
using it only from contrib module looks very odd. So, new feature can be used
only with compatibility layer for old release :)
3 headline_with_fragments() is hardcoded to use default parser, but what will
be
in case when configuration uses another parser? For example, for japanese
language.
4 I would prefer the signature ts_headline( [regconfig,] text, tsquery
[,text] )
and function should accept 'NumFragments=N' for default parser. Another
parsers
may use another options.
5 it just doesn't work correctly, because new code doesn't care of parser
specific type of lexemes.
contrib_regression=# select headline_with_fragments('english', 'wow asd-wow
wow', 'asd', '');
headline_with_fragments
--
...wow asd-wowbasd/b-wow wow
(1 row)
So, I incline to use existing framework/infrastructure although it may be a
subject to change.
Some description:
1 ts_headline defines a correct parser to use
2 it calls hlparsetext to split text into structure suitable for both goals:
find the best fragment(s) and concatenate that fragment(s) back to the text
representation
3 it calls parser specific method prsheadline which works with preparsed
text
(parse was done in hlparsetext). Method should mark a needed
words/parts/lexemes etc.
4 ts_headline glues fragments into text and returns that.
We need a parser's headline method because only parser knows all about its
lexemes.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Execution-time-sensitive timestamp regression tests
Magnus Hagander wrote: For the record, what we were talking about was snapshotting the time at backend start and then use QueryPerformanceCounter() to see what happened and do some calculation. Although this might not be such a big issue for the regression tests: Be aware that the reliability of QueryPerformanceCounter() depends on the hardware: http://support.microsoft.com/kb/274323/en-us I used it in Munnin, but there were too many customer machines affected by this bug so that I had to abandom it. This was a few years ago, but I'm not sure whether the function can be trusted today (see for example this performance problem http://support.microsoft.com/kb/895980/en-us). Rainer -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Fragments in tsearch2 headline
On Sat, May 24, 2008 at 11:18 PM, Sushant Sinha [EMAIL PROTECTED] wrote: Does this mean we want a unified function ts_headline and we trigger the fragments if NumFragments is specified? It seems that introducing a new function which can take configuration OID, or name is complex as there are so many functions handling these issues in wparser.c. If this is true then we need to just add marking of headline words in prsd_headline. Otherwise we will need another prsd_headline_with_covers function. I think that we could merge down the two functions using a default cover of the whole text when NumCovers if missing. I started writing the function. The only missing information is, as you stated, the missing posititions in HeadlineParsedText. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] \df displaying volatility
Alvaro Herrera [EMAIL PROTECTED] writes: Would anyone object to \df displaying a function's volatility? Maybe limit it to \df+? Huh? \df+ has displayed volatility for a long time, and I don't recall any great demand to move it into \df. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Execution-time-sensitive timestamp regression tests
Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: Objections, better ideas? Alternatively we could do a pg_sleep(.1) to sleep for 100ms. It sounds like the ideal would be something like: insert 'now' pg_sleep(.1) begin insert 'now' select * from table -- expect 1 (not 0 or 2) ... the other tests you mention which get bitten by midnight end Roger, will do. There actually still is a small risk in the sequence: if the BEGIN block starts *exactly* at midnight, to within the resolution of gettimeofday(), then 'now' and 'today' will yield the same value so the expected row counts will not be matched. This seems like an acceptably small probability to me. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Does 'ALTER INDEX' take exclusive lock?
Hi All, Does the command ALTER INDEX take exclusive lock on the objects involved? Specifically I am looking at ALTER INDEX ... SET TABLESPACE. The docs do not mention anything about this. I assume it would, and can do a few tests (or look at code :) ), but asking here wouldn't hurt! Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
