Обсуждение: 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

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