[HACKERS] Recursive Queries and tuplestore.c
So to implement recursive queries I think what we need is a memoizing node like Materialize which allows multiple simultaneous readers. Looking into how to implement this I find that the read position of a Materialize node is actually implemented directly in tuplestore.c. That means that tuplestore.c would have to grow a bit to allow for an array of structs that contain current, readpos_file, readpos_offset, markpos_current, markpos_file, and markpos_offset. An alternative I've been thinking about is creating a kind of slave_tuplestore. It would keep its own readpos etc, but pass all actual operations through to another tuplestore. I don't think it would pay for itself though. The complexity involved especially around when arrays are grown and dumped to disk would be greater than the complexity saved in the regular codepath. Secondly, it would be valuable for recursive queries to be able to throw away old tuples when all the marks and read positions have passed them (if they're not random-access of course). This is the same feature that Simon suggested for merge joins. I'm thinking the way to do that is to keep an array of arrays instead of a single array. Whenever (all) the mark and read position(s) advances over the end of an array we can free it. Alternatively whenever it comes time to grow the array we could check whether older tuples aren't needed any more and memmove the later tuples back to the start of the array instead of growing the array. That would be simpler but it would be more expensive to do the memmove. Often there would be very few tuples moving though, and repalloc often does a memcpy anyways, so perhaps it wouldn't really be a problem. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Recursive Queries
The CONNECT BY patch from evgen potemkin has been ported to pg 8.2... and it's now in BSD License... I will test it on our test environement Le jeudi 25 janvier 2007 à 11:08 +, Gregory Stark a écrit : > Hm, having skimmed through the Evgen Potemkin's recursive queries patch I find > it quite different from what I was expecting. My own thinking was headed off > in a different direction. > > Basically the existing patch reimplements a new kind of join which implements > a kind of nested loop join (with newer versions adding a kind of hash join) > which feeds a new kind of tuplestore called a tupleconn. > > I was thinking to have a new node above a standard join. The new node would > repeatedly feed back down to the join the results of the previous iteration > and reexecute the join to get the next generation. > > I think my approach is more in line with the DB2/ANSI "WITH" style query which > is expected to do a breadth-first search. The Oracle CONNECT BY syntax is > expected to do a depth first search. > > I have two major issues with the repeated-join model though. > > a) Ideally we would want to switch between nested loop, merge join, and hash > join depending on the size of the previous generation. That means the join > node wouldn't be the same type of join for all the iterations. This is > important since in most applications you're traversing either up or down a > tree and are likely starting with very few nodes but often ending up with very > broad levels with many nodes. No single type of join will be appropriate for > the whole plan execution. > > b) I do want to be able to support depth-first searching too. I'm not sure how > to reconcile that with the repeated-join conceptual model. We could always > resort the entire result set after generating it but that seems like an > unsatisfactory solution. > ___ Ce message et les �ventuels documents joints peuvent contenir des informations confidentielles. Au cas o� il ne vous serait pas destin�, nous vous remercions de bien vouloir le supprimer et en aviser imm�diatement l'exp�diteur. Toute utilisation de ce message non conforme � sa destination, toute diffusion ou publication, totale ou partielle et quel qu'en soit le moyen est formellement interdite. Les communications sur internet n'�tant pas s�curis�es, l'int�grit� de ce message n'est pas assur�e et la soci�t� �mettrice ne peut �tre tenue pour responsable de son contenu.
Re: [HACKERS] Recursive Queries
Le jeudi 25 janvier 2007 à 12:12 -0500, Gregory Stark a écrit : > "Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > > > That's basically how the existing patch approached the problem. It > > > invents a > > > new type of join and a new type of tuplestore that behaves this way. This > > > has > > > the advantage of working the way Oracle users expect and being relatively > > > simple conceptually. It has the disadvantage of locking us into what's > > > basically a nested loop join and not reusing existing join code so it's > > > quite > > > a large patch. > > > > I believe our Syntax should be whatever the standard dictates, > > regardless of Oracle. > > Well the issue here isn't one of syntax. The syntax is really an orthogonal > issue. The basic question is whether to treat this as a new type of plan node > with its behaviour hard coded or whether to try to reuse existing join types > executing them recursively on their output. I can see advantages either way. > > As far as the syntax goes, now that I've actually read up on both, I have to > say: I'm not entirely sure I'm happy IBM won this battle. The Oracle syntax is > simple easy to use. The IBM/ANSI syntax is, well, baroque. There's a certain > logical beauty to it but I can't see users being happy trying to figure out > how to use it. > I agree with THAT, it's clear that WITH RECURSIVE is more standard... but for the SQL developper CONNECT BY is a paradise... the syntax is clear and powerful... That's why we've chosen to developp our queries with that (using the connectby() function and the evgen potemkin.'s patch (http://gppl.moonbone.ru/) ___ Ce message et les �ventuels documents joints peuvent contenir des informations confidentielles. Au cas o� il ne vous serait pas destin�, nous vous remercions de bien vouloir le supprimer et en aviser imm�diatement l'exp�diteur. Toute utilisation de ce message non conforme � sa destination, toute diffusion ou publication, totale ou partielle et quel qu'en soit le moyen est formellement interdite. Les communications sur internet n'�tant pas s�curis�es, l'int�grit� de ce message n'est pas assur�e et la soci�t� �mettrice ne peut �tre tenue pour responsable de son contenu.
Re: [HACKERS] Recursive Queries
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > > That's basically how the existing patch approached the problem. It invents a > > new type of join and a new type of tuplestore that behaves this way. This > > has > > the advantage of working the way Oracle users expect and being relatively > > simple conceptually. It has the disadvantage of locking us into what's > > basically a nested loop join and not reusing existing join code so it's > > quite > > a large patch. > > I believe our Syntax should be whatever the standard dictates, > regardless of Oracle. Well the issue here isn't one of syntax. The syntax is really an orthogonal issue. The basic question is whether to treat this as a new type of plan node with its behaviour hard coded or whether to try to reuse existing join types executing them recursively on their output. I can see advantages either way. As far as the syntax goes, now that I've actually read up on both, I have to say: I'm not entirely sure I'm happy IBM won this battle. The Oracle syntax is simple easy to use. The IBM/ANSI syntax is, well, baroque. There's a certain logical beauty to it but I can't see users being happy trying to figure out how to use it. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Recursive Queries
Gregory Stark wrote: > "Martijn van Oosterhout" writes: > >> On Thu, Jan 25, 2007 at 11:08:14AM +, Gregory Stark wrote: >>> b) I do want to be able to support depth-first searching too. I'm not sure >>> how >>> to reconcile that with the repeated-join conceptual model. We could always >>> resort the entire result set after generating it but that seems like an >>> unsatisfactory solution. >> If you have a tuplestore storing the intermediate tuples for looping, >> then surely the only difference between depth and breadth searching is >> that for the former new tuples goes to the front of the tuplestore, and >> the latter to the end. > > That's basically how the existing patch approached the problem. It invents a > new type of join and a new type of tuplestore that behaves this way. This has > the advantage of working the way Oracle users expect and being relatively > simple conceptually. It has the disadvantage of locking us into what's > basically a nested loop join and not reusing existing join code so it's quite > a large patch. I believe our Syntax should be whatever the standard dictates, regardless of Oracle. Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Recursive Queries
"Martijn van Oosterhout" writes: > On Thu, Jan 25, 2007 at 11:08:14AM +, Gregory Stark wrote: >> b) I do want to be able to support depth-first searching too. I'm not sure >> how >> to reconcile that with the repeated-join conceptual model. We could always >> resort the entire result set after generating it but that seems like an >> unsatisfactory solution. > > If you have a tuplestore storing the intermediate tuples for looping, > then surely the only difference between depth and breadth searching is > that for the former new tuples goes to the front of the tuplestore, and > the latter to the end. That's basically how the existing patch approached the problem. It invents a new type of join and a new type of tuplestore that behaves this way. This has the advantage of working the way Oracle users expect and being relatively simple conceptually. It has the disadvantage of locking us into what's basically a nested loop join and not reusing existing join code so it's quite a large patch. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Recursive Queries
On Thu, Jan 25, 2007 at 11:08:14AM +, Gregory Stark wrote: > b) I do want to be able to support depth-first searching too. I'm not sure how > to reconcile that with the repeated-join conceptual model. We could always > resort the entire result set after generating it but that seems like an > unsatisfactory solution. If you have a tuplestore storing the intermediate tuples for looping, then surely the only difference between depth and breadth searching is that for the former new tuples goes to the front of the tuplestore, and the latter to the end. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] Recursive Queries
Ühel kenal päeval, N, 2007-01-25 kell 11:08, kirjutas Gregory Stark: > Hm, having skimmed through the Evgen Potemkin's recursive queries patch I find > it quite different from what I was expecting. My own thinking was headed off > in a different direction. > > Basically the existing patch reimplements a new kind of join which implements > a kind of nested loop join (with newer versions adding a kind of hash join) > which feeds a new kind of tuplestore called a tupleconn. > > I was thinking to have a new node above a standard join. The new node would > repeatedly feed back down to the join the results of the previous iteration > and reexecute the join to get the next generation. > > I think my approach is more in line with the DB2/ANSI "WITH" style query which > is expected to do a breadth-first search. IIRC the ISO/ANSI spec has a special clause for specifying both BREADTH FIRST and DEPTH FIRST searches > The Oracle CONNECT BY syntax is expected to do a depth first search. > > I have two major issues with the repeated-join model though. > > a) Ideally we would want to switch between nested loop, merge join, and hash > join depending on the size of the previous generation. That means the join > node wouldn't be the same type of join for all the iterations. This is > important since in most applications you're traversing either up or down a > tree and are likely starting with very few nodes but often ending up with very > broad levels with many nodes. No single type of join will be appropriate for > the whole plan execution. Then it's probably better to optimize for "very broad levels with many nodes" case, as bad plan fro few joins will probably affect you less in case of only a small number of nodes. > b) I do want to be able to support depth-first searching too. I'm not sure how > to reconcile that with the repeated-join conceptual model. We could always > resort the entire result set after generating it but that seems like an > unsatisfactory solution. I guess there may be difference in breadth-first and depth-first actual data, if you use some recursion control clauses or include level variables. -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Recursive Queries
Hm, having skimmed through the Evgen Potemkin's recursive queries patch I find it quite different from what I was expecting. My own thinking was headed off in a different direction. Basically the existing patch reimplements a new kind of join which implements a kind of nested loop join (with newer versions adding a kind of hash join) which feeds a new kind of tuplestore called a tupleconn. I was thinking to have a new node above a standard join. The new node would repeatedly feed back down to the join the results of the previous iteration and reexecute the join to get the next generation. I think my approach is more in line with the DB2/ANSI "WITH" style query which is expected to do a breadth-first search. The Oracle CONNECT BY syntax is expected to do a depth first search. I have two major issues with the repeated-join model though. a) Ideally we would want to switch between nested loop, merge join, and hash join depending on the size of the previous generation. That means the join node wouldn't be the same type of join for all the iterations. This is important since in most applications you're traversing either up or down a tree and are likely starting with very few nodes but often ending up with very broad levels with many nodes. No single type of join will be appropriate for the whole plan execution. b) I do want to be able to support depth-first searching too. I'm not sure how to reconcile that with the repeated-join conceptual model. We could always resort the entire result set after generating it but that seems like an unsatisfactory solution. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Recursive Queries
The only code that is usable (and performant) is the CONNECT BY patch made by Evgen Potemkin, It works on production servers on the 8.1.5 I hope that a WITH RECURSIVE will be in the 8.3... but I don't see anybody working on this... (what a shame...) Le mercredi 24 janvier 2007 à 17:27 +, Gregory Stark a écrit : > I'm looking into recursive queries and what it would take to support them in > Postgres. Is anyone else looking at this already? > > Aside from the Oracle-ish syntax were there other objections to the patch as > posted a while back for 7.3 by Evgen Potemkin? > > I have some ideas myself for how to go about this but I'm going to review the > existing patch first. If anyone else has ideas I would like to hear them. > ___ Ce message et les �ventuels documents joints peuvent contenir des informations confidentielles. Au cas o� il ne vous serait pas destin�, nous vous remercions de bien vouloir le supprimer et en aviser imm�diatement l'exp�diteur. Toute utilisation de ce message non conforme � sa destination, toute diffusion ou publication, totale ou partielle et quel qu'en soit le moyen est formellement interdite. Les communications sur internet n'�tant pas s�curis�es, l'int�grit� de ce message n'est pas assur�e et la soci�t� �mettrice ne peut �tre tenue pour responsable de son contenu.
Re: [HACKERS] Recursive Queries
Bruce Momjian wrote: Wasn't somebody else working on this? Jonah? (Maybe you EDB guys need to talk more ...) He is taking it over for Jonah. Oh, good. That was the piece of missing info. I had a case just yesterday where this feature would have saved us hours of writing client code to compute the same thing. cheers andrew ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Recursive Queries
Andrew Dunstan wrote: > Gregory Stark wrote: > > I'm looking into recursive queries and what it would take to support them in > > Postgres. Is anyone else looking at this already? > > > > Aside from the Oracle-ish syntax were there other objections to the patch as > > posted a while back for 7.3 by Evgen Potemkin? > > > > I have some ideas myself for how to go about this but I'm going to review > > the > > existing patch first. If anyone else has ideas I would like to hear them. > > > > > > My recollection is that the verdict was that it was clode to 100% > unusable - you might want to review the past discussions. Yes, the old patch is unusasble. The change has to be done in a different part of the code. > Wasn't somebody else working on this? Jonah? (Maybe you EDB guys need to > talk more ...) He is taking it over for Jonah. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Recursive Queries
Gregory Stark wrote: I'm looking into recursive queries and what it would take to support them in Postgres. Is anyone else looking at this already? Aside from the Oracle-ish syntax were there other objections to the patch as posted a while back for 7.3 by Evgen Potemkin? I have some ideas myself for how to go about this but I'm going to review the existing patch first. If anyone else has ideas I would like to hear them. My recollection is that the verdict was that it was clode to 100% unusable - you might want to review the past discussions. Wasn't somebody else working on this? Jonah? (Maybe you EDB guys need to talk more ...) cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Recursive Queries
Gregory Stark wrote: > I'm looking into recursive queries and what it would take to support them in > Postgres. Is anyone else looking at this already? Yes your co-employee Jonah. http://archives.postgresql.org/pgsql-hackers/2007-01/msg00989.php Sincerely, Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Recursive Queries
I'm looking into recursive queries and what it would take to support them in Postgres. Is anyone else looking at this already? Aside from the Oracle-ish syntax were there other objections to the patch as posted a while back for 7.3 by Evgen Potemkin? I have some ideas myself for how to go about this but I'm going to review the existing patch first. If anyone else has ideas I would like to hear them. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Recursive queries patch
Michael Meskes wrote: > Just wanted to let you know that if we would be interested in adding > that patch to our main cvs the guy who wrote it would be more than > willing to change his license to BSD. I was under the impression we wanted to implement the ANSI way to do this. Is this what the patch does? If so, I haven't seen it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Recursive queries?
hi again, at last i found patch for WITH and make it working. it's resides in attach. what it does: WITH a AS (SELECT * FROM t) SELECT * FROM a; WITH a AS (SELECT * FROM t), b as (SELECT * FROM t) SELECT * FROM a.id=b.id; WITH a AS (SELECT * FROM t), b AS (SELECT * FROM a) SELECT * FROM b; where t is real table such as (id int, val varchar(10)). what it doesn't: WITH RECURSIVE didn't implemented. it's unclean, executor part is a braindead a bit and need to be rewriten. anything other, almost any checks and restrinctions, so on - it's nothing more than working prototype. how it works: single node "With" contains list of all WITH subqueries, each subquery described by WithSubPlan which stores subquery itself, subquery rtable,working and result tublestores, some flags. each WITH subquery alias represented by WithScan node , it stores pointer to With node, and index in list of subqueries of this subquery. when first access (exec,init,end,...) to any WithScan node is made, it passes to With node, and it process all WITH subqueries and stores results in result tuplestores per each subquery. after that all WithScan nodes fetchs data from result tuplestores and returns it as from table. to add RECURSIVE support need at parse state add function which will be determining right order of WITH subqueries executing, and handling of many passes in executor. regards, .evgen Hans-Jürgen Schönig wrote: evgen wrote: hello hackers, some time ago i played with PgSQL and have written simpliest working prototype of WITH clause for it. it don't do any checks and performs only simpliest selects, but it works. i can contribute it and develop it further to production state. regards, .evgen I suggest sending your patch to this list so that people can have a look at it. Regards, Hans with-0.0.diff.bz2 Description: BZip2 compressed data ---(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
[HACKERS] Recursive queries patch
Just wanted to let you know that if we would be interested in adding that patch to our main cvs the guy who wrote it would be more than willing to change his license to BSD. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Recursive queries?
evgen wrote: hello hackers, some time ago i played with PgSQL and have written simpliest working prototype of WITH clause for it. it don't do any checks and performs only simpliest selects, but it works. i can contribute it and develop it further to production state. regards, .evgen I suggest sending your patch to this list so that people can have a look at it. Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Recursive queries?
hello hackers, some time ago i played with PgSQL and have written simpliest working prototype of WITH clause for it. it don't do any checks and performs only simpliest selects, but it works. i can contribute it and develop it further to production state. regards, .evgen ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Recursive queries?
Neil, > Granted, the primary goal should be implementing the SQL99 syntax, but > providing syntax-level compatibility with Oracle's syntax (provided it > isn't too difficult) seems like a good idea to me. I don't agree. There are lots of non-standard things which Oracle does (outer joins come to mind); supporting them just because Oracle does them would be giving our allegiance to Oracle instead of ANSI as the standard-setter for SQL. I would happily support an Oracle compatibility parser as an *optional add-in* to PostgreSQL for people porting applications, but for the general user we should be promoting standard syntax wherever it is reasonable to do so. Also, for all we know Oracle may have patented "connect by". And for that matter, why just Oracle? Why not MS SQL Server & Sybase? Why not DB2? Now, maybe there's an argumen that "CONNECT BY" supplies some sort of functionality that the SQL99 standard is missing, which would be persuasive; we have included sequences and LIMIT, after all.But even then I would question the term; I've never found "CONNECT BY" to be particularly intuitive. "RECURSIVE JOIN" would make far more sense. -- -Josh Berkus Aglio Database Solutions San Francisco ---(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: [HACKERS] Recursive queries?
Josh Berkus <[EMAIL PROTECTED]> writes: > I think that's as far as we want to go implementing Oracle's syntax Why do you think that? > If we're to implement recursive queries, we should implement the > SQL99 standard. Granted, the primary goal should be implementing the SQL99 syntax, but providing syntax-level compatibility with Oracle's syntax (provided it isn't too difficult) seems like a good idea to me. -Neil ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] Recursive queries?
Chris, Robert > As a side note, I thought Joe Conway also had an implementation of > this... Yes, we already have a connect_by function, and have had since 7.3. Look in /contrib/tablefuc. I think that's as far as we want to go implementing Oracle's syntax, though *external* patches are of course good for GBorg. If we're to implement recursive queries, we should implement the SQL99 standard. -- -Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Recursive queries?
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <[EMAIL PROTECTED]> writes: > In my very personal opinion (don't cut my head off) I'd vote for both > syntaxes. I'm not opposed to that, although it would be a good idea to check that Oracle doesn't have some patent covering their syntax. However, if we go for that then what we probably want to do is implement the SQL-spec syntax and then add something to translate the Oracle syntax into a SQL parsetree. We shouldn't need two implementations in the guts of the system, and I'd expect that translating in the other direction (SQL WITH to an Oracle internal implementation) wouldn't work, because WITH does more. I dunno whether the patch mentioned earlier in this thread could serve as a starting point for that or not. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Recursive queries?
Tom Lane wrote: =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <[EMAIL PROTECTED]> writes: Does this patch have a serious chance to make it into Pg some day? I think Oracle's syntax is not perfect but is easy to handle and many people are used to it. In people's mind recursive queries = CONNECT BY and many people (like me) miss it sadly. I would prefer to see us supporting the SQL-standard syntax (WITH etc), as it is (1) standard and (2) more flexible than CONNECT BY. The Red Hat work mentioned earlier in the thread was aimed at supporting the standard syntax. regards, tom lane I have already expected an answer like that. In my very personal opinion (don't cut my head off) I'd vote for both syntaxes. The reasons for that are fairly easy to explain: - I have to agree with Tom (1, 2). - CONNECT BY makes sense because it is easier to build applications supporting Oracle and PostgreSQL. In case of more complex applications (CONNECT BY is definitely more than pure storage of simple data) Oracle-Pg compliance is really important (I have seen that a dozen times). From a marketing point of view both versions make sense - Oracle->Pg migration is an increasing market share. From a technical point of view I completely agree with Tom (I have learned in the past that Tom us usually right). Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Recursive queries?
Christopher Browne kirjutas K, 04.02.2004 kell 15:10: > The fact that the form of this resembles that of the Lisp/ML "let" > forms means that WITH can be useful in structuring queries as well. > For instance, supposing you're computing a value that gets used > several times, putting it into a WITH clause might allow evading the > need to compute it more than once. The main difference between giving the subquery in WITH and in FROM, is that the subqueries given in FROM claues don't see each other, while the ones given in WITH see the ones preceeding them and the ones in WITH RECURSIVE see all queries in the WITH RECURSIVE clause. -- Hannu ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Recursive queries?
Tom Lane kirjutas K, 04.02.2004 kell 06:04: > Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > > Wasn't there some guy at RedHat doing it? > > Andrew Overholt did some work on SQL99 recursive queries, but went back > to university without having gotten to the point where it actually > worked. One of the many things on my to-do list is to pick up and > finish Andrew's work on this. If someone has time to work on it, > let me know and I'll try to get what he had over to you. I attach my early attempts at doing the same. I also sent this to Andrew while he was working on it, and he claimed that his version was similar. I think he sent me a slightly more advanced verion of this, but I'm currently unable to lovcate it. This has mainly the syntax part (without recursion control IIRC) and some initial documentation (in python's StructuredText and html formats) If I find Andrews variant I'll try to post it too. - Hannu pg-with-recursive.tar.gz Description: application/compressed-tar ---(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: [HACKERS] Recursive queries?
Robert Treat kirjutas K, 04.02.2004 kell 16:55: > Seems it has no chance of getting in as it is GPL'd code... so step one > would be convincing him to relicense it. > > As a side note, I thought Joe Conway also had an implementation of > this... IIRC Joe Conway had the simple join-by-parent-id variant done as set-returning function. --- Hannu ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Recursive queries?
=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <[EMAIL PROTECTED]> writes: > Does this patch have a serious chance to make it into Pg some day? > I think Oracle's syntax is not perfect but is easy to handle and many > people are used to it. In people's mind recursive queries = CONNECT BY > and many people (like me) miss it sadly. I would prefer to see us supporting the SQL-standard syntax (WITH etc), as it is (1) standard and (2) more flexible than CONNECT BY. The Red Hat work mentioned earlier in the thread was aimed at supporting the standard syntax. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Recursive queries?
On Wed, 2004-02-04 at 05:28, Hans-Jürgen Schönig wrote:
> Christopher Kings-Lynne wrote:
> >> There is a website somewhere where a guy posts his patch he is
> >> maintaining that does it. I'll try to find it...
> >
> >
> > Found it. Check it out:
> >
> > http://gppl.terminal.ru/index.eng.html
> >
> > Patch is current for 7.4, Oracle syntax.
> >
> > Chris
>
>
> I had a look at the patch.
> It is still in development but it seems to work nicely - at least I have
> been able to get the same results with Oracle.
>
> I will try it with a lot of data this afternoon so that we can compare
> Oracle vs. Pg performance. I expect horrible results ;).
>
> Does this patch have a serious chance to make it into Pg some day?
> I think Oracle's syntax is not perfect but is easy to handle and many
> people are used to it. In people's mind recursive queries = CONNECT BY
> and many people (like me) miss it sadly.
>
> If this patch has a serious chance I'd like to do some investigation and
> some real-world data testing.
>
Seems it has no chance of getting in as it is GPL'd code... so step one
would be convincing him to relicense it.
As a side note, I thought Joe Conway also had an implementation of
this...
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Recursive queries?
Clinging to sanity, [EMAIL PROTECTED] (Andrew Rawnsley) mumbled into her beard:
> I haven't had any problems with it so far, although I haven't really
> stressed it yet. I was going to make this very plea...
>
> I agree that the syntax can probably be improved, but its familiar to
> those of us unfortunate enough to have used (or still have to use)
> Oracle. I imagine that bringing it more in line with any standard
> would be what people would prefer.
The SQL:1999 form is instead of the form
with recquery (a,b,c,d) as
(select a1,b1,c1,d1 from some table where d1 > 21)
select * from recquery;
Notice that I have indented this in the same way a Lisp programmer
would indent a LET form...
(let
((a value-for-a)
(b value-for-b)
(c compute-c)
(d 42)) ;;; The ultimate answer...
(compute-something-with-values a b c d))
In ML, there is an analagous "let/in" construct:
#let a = 1 and
b = 2 and
c = 3
in
a + b * c;;
- : int = 7
That example is oversimplified, a bit, as it does not do anything
recursive. In order to express a recursive relationship, the query
likely needs to have a UNION ALL, and look more like the following:
with recquery (a,b,c,d) as
(select a,b,c,d from base_table root -- Root level entries
where c > 200
union all
select child.a,child.b,child.c,child.d
from recquery parent, base_table child -- Self-reference here
where parent.a = child.b -- The link between nodes...
and c > 200)
select a,b,c,d from recquery;
The fact that the form of this resembles that of the Lisp/ML "let"
forms means that WITH can be useful in structuring queries as well.
For instance, supposing you're computing a value that gets used
several times, putting it into a WITH clause might allow evading the
need to compute it more than once.
with notrec (radius, pi, month) as
(select radius, 3.1412, date_trunc('month', txn_date) from pie_table)
select month, sum(pi * radius * radius as area), count(*)
from not_rec
where month between '2003-01-01' and '2004-01-01'
group by month;
has some 'elegance' by virtue of only using date_trunc once over
select date_trunc('month', txn_date), sum(3.1412 * radius*radius) as
area, count(*) from pie_table
where
date_trunc('month', txn_date) between '2003-01-01' and '2004-01-01'
group by month;
Admittedly, date_trunc() may not be an ideal example, as the date
constraint would work as well with an untruncated date the point is
that in the no-WITH approach, there is an extra use of date_trunc().
But the recomputation that takes place when a functional value is used
both in the result clause and in the WHERE clause is something that
WITH can eliminate.
--
"aa454","@","freenet.carleton.ca"
http://www.ntlug.org/~cbbrowne/emacs.html
Lisp Users:
Due to the holiday next Monday, there will be no garbage collection.
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Recursive queries?
I haven't had any problems with it so far, although I haven't really stressed it yet. I was going to make this very plea... I agree that the syntax can probably be improved, but its familiar to those of us unfortunate enough to have used (or still have to use) Oracle. I imagine that bringing it more in line with any standard would be what people would prefer. On Feb 4, 2004, at 5:28 AM, Hans-Jürgen Schönig wrote: Christopher Kings-Lynne wrote: There is a website somewhere where a guy posts his patch he is maintaining that does it. I'll try to find it... Found it. Check it out: http://gppl.terminal.ru/index.eng.html Patch is current for 7.4, Oracle syntax. Chris I had a look at the patch. It is still in development but it seems to work nicely - at least I have been able to get the same results with Oracle. I will try it with a lot of data this afternoon so that we can compare Oracle vs. Pg performance. I expect horrible results ;). Does this patch have a serious chance to make it into Pg some day? I think Oracle's syntax is not perfect but is easy to handle and many people are used to it. In people's mind recursive queries = CONNECT BY and many people (like me) miss it sadly. If this patch has a serious chance I'd like to do some investigation and some real-world data testing. Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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 Andrew Rawnsley President The Ravensfield Digital Resource Group, Ltd. (740) 587-0114 www.ravensfield.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Recursive queries?
Christopher Kings-Lynne wrote: There is a website somewhere where a guy posts his patch he is maintaining that does it. I'll try to find it... Found it. Check it out: http://gppl.terminal.ru/index.eng.html Patch is current for 7.4, Oracle syntax. Chris I had a look at the patch. It is still in development but it seems to work nicely - at least I have been able to get the same results with Oracle. I will try it with a lot of data this afternoon so that we can compare Oracle vs. Pg performance. I expect horrible results ;). Does this patch have a serious chance to make it into Pg some day? I think Oracle's syntax is not perfect but is easy to handle and many people are used to it. In people's mind recursive queries = CONNECT BY and many people (like me) miss it sadly. If this patch has a serious chance I'd like to do some investigation and some real-world data testing. Regards, Hans -- Cybertec Geschwinde u Schoenig Schoengrabern 134, A-2020 Hollabrunn, Austria Tel: +43/2952/30706 or +43/664/233 90 75 www.cybertec.at, www.postgresql.at, kernel.cybertec.at ---(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: [HACKERS] Recursive queries?
There is a website somewhere where a guy posts his patch he is maintaining that does it. I'll try to find it... Found it. Check it out: http://gppl.terminal.ru/index.eng.html Patch is current for 7.4, Oracle syntax. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Recursive queries?
Andrew Overholt did some work on SQL99 recursive queries, but went back to university without having gotten to the point where it actually worked. One of the many things on my to-do list is to pick up and finish Andrew's work on this. If someone has time to work on it, let me know and I'll try to get what he had over to you. There is a website somewhere where a guy posts his patch he is maintaining that does it. I'll try to find it... Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Recursive queries?
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > Wasn't there some guy at RedHat doing it? Andrew Overholt did some work on SQL99 recursive queries, but went back to university without having gotten to the point where it actually worked. One of the many things on my to-do list is to pick up and finish Andrew's work on this. If someone has time to work on it, let me know and I'll try to get what he had over to you. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Recursive queries?
Is there anyone working on recursive queries for 7.5? I know there is a patch that implements it on 7.4 (I can't seem to find the guy's webpage), but that uses Oracle syntax. Wasn't there some guy at RedHat doing it? Is RedHat working on PITR? Chris ---(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: [HACKERS] recursive queries
Tom Lane once said: > "Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > >> Andrew Overholt of Red Hat has been working > >> on this, but is certainly not going to make the Tuesday feature-freeze > >> deadline. > > > I was just wondering who was working on it and what the progress was...? It > > seemed to me that it must have been hacked on for quite a long time now? > > Andrew's had the usual quota of corporate demands on his time :-(. > Perhaps he'll weigh in here for himself, but I had thought right along > that getting it done for 7.4 was chancy. Yeah, I had originally thought that I would be able to get it done, but only sporadic development time leaves me in the usual "where was I?"-"oh yeah"-"_this_ is what I was going to do next" time-sink and I haven't progressed as much as I had wanted. I had been attempting to get the non-recursive case in before 7.4, but that's not going to happen either. Sometime this summer for sure. Sorry. Andrew ---(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: [HACKERS] recursive queries
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: >> Andrew Overholt of Red Hat has been working >> on this, but is certainly not going to make the Tuesday feature-freeze >> deadline. > I was just wondering who was working on it and what the progress was...? It > seemed to me that it must have been hacked on for quite a long time now? Andrew's had the usual quota of corporate demands on his time :-(. Perhaps he'll weigh in here for himself, but I had thought right along that getting it done for 7.4 was chancy. > Also, are we getting DB2 or Oracle syntax? SQL99-spec is the intention. I believe this is much closer to DB2 than to Oracle. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] recursive queries
> If you mean SQL99 WITH clauses, approximately zero ... unless you > had an implementation you were planning to whip out of your hip > pocket along about now. Andrew Overholt of Red Hat has been working > on this, but is certainly not going to make the Tuesday feature-freeze > deadline. I was just wondering who was working on it and what the progress was...? It seemed to me that it must have been hacked on for quite a long time now? Also, are we getting DB2 or Oracle syntax? Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] recursive queries
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > What's the chances of getting recursive queries in for 7.4? If you mean SQL99 WITH clauses, approximately zero ... unless you had an implementation you were planning to whip out of your hip pocket along about now. Andrew Overholt of Red Hat has been working on this, but is certainly not going to make the Tuesday feature-freeze deadline. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] recursive queries
What's the chances of getting recursive queries in for 7.4? Chris ---(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
