Thread: 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 tofind the guy's webpage), but that uses Oracle syntax. Wasn't there some guy at RedHat doing it? Is RedHat working on PITR? Chris
Christopher Kings-Lynne <chriskl@familyhealth.com.au> 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
> 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
> 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
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
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 majordomo@postgresql.org 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
Clinging to sanity, ronz@ravensfield.com (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-valuesa 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.
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
Hans-Jürgen Schönig <postgres@cybertec.at> 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
Tom Lane kirjutas K, 04.02.2004 kell 06:04: > Christopher Kings-Lynne <chriskl@familyhealth.com.au> 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
Attachment
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
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
Tom Lane wrote:
> Hans-Jürgen Schönig <postgres@cybertec.at> 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
Hans-Jürgen Schönig <postgres@cybertec.at> 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
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 BerkusAglio Database SolutionsSan Francisco
Josh Berkus <josh@agliodbs.com> 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
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 BerkusAglio Database SolutionsSan Francisco
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
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
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 > >