Re: 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

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)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(at)postgresql(dot)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(at)ravensfield(dot)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-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.

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

=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres(at)cybertec(dot)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(at)familyhealth(dot)com(dot)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

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:
> =?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres(at)cybertec(dot)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

=?ISO-8859-1?Q?Hans-J=FCrgen_Sch=F6nig?= <postgres(at)cybertec(dot)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