Re: SQL99 hierarchical queries stalled

Looks like hierarchical queries are now officially stalled :(

Anyone want to take this up for 8.1?

Chris

-------- Original Message --------
Subject: Re: [HACKERS] SQL99 Hierarchical queries
Date: Sun, 15 May 2005 07:31:16 +0400
From: Evgen Potemkin <eugen(dot)potemkin(at)gmail(dot)com>
Reply-To: Evgen Potemkin <eugen(dot)potemkin(at)gmail(dot)com>
To: Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au>
References: <44458e32050224020236ac358f(at)mail(dot)gmail(dot)com>
<42797B7C(dot)6090502(at)familyhealth(dot)com(dot)au>

Hi,

I haven't done any significant progress on that way because of lack of
free time.
Beside this, I'm recently changed my job and now I'm woking for MySQL.
I think it's not possible for me to continue work on PostgreSQL.
Feel free to take the patch and develop it further as long as you
mention me as author of initial version.

Regards, Evgen.

On 5/5/05, Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> wrote:
> Hi Evgen,
>
> I just keep pinging this patch thread every once in a while to make sure
> it doesn't get forgotten :)
>
> How is the syncing with 8.1 CVS coming along?
>
> Chris
>
> Evgen Potemkin wrote:
> > Hi hackers!
> >
> > I have done initial implementation of SQL99 WITH clause (attached).
> > It's now only for v7.3.4 and haven't a lot of checks and restrictions.
> > It can execute only simple WITH queries like
> > WITH tree AS (SELECT id,pnt,name,1::int AS level FROM t WHERE id=0
> > UNION SELECT t.id,t.pnt,t.name,tree.level+1 FROM t JOIN tree ON
> > tree.id=t.pnt ) SELECT * FROM tree;
> > It would be great if someone with knowledge of Pg internals can make a
> > kind of code review and make some advices how to better implement all
> > this.
> >
> > Regards, Evgen.
> >
> >
> > ------------------------------------------------------------------------
> >
> >
> > ---------------------------(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
>

On Sun, May 15, 2005 at 04:44:57PM +0800, Christopher Kings-Lynne wrote:
> Looks like hierarchical queries are now officially stalled :(
>
> Anyone want to take this up for 8.1?

Sergei and Jason,

Feel like taking SQL:1999 WITH RECURSIVE? It would be a giant help to
the PostgreSQL community. :)

http://gppl.moonbone.ru/index.shtml

has part of it, and

http://candle.pha.pa.us/mhonarc/patches2/msg00175.html

has others.

There's also MERGE, which is covered starting on page 47 of
http://wiscorp.com/sql/SQL2003Features.pdf

also pp 839-845 of 5WD-02-Foundation-2003-09.pdf which is part of
this:
http://wiscorp.com/sql/sql_2003_standard.zip

and an overview here:
http://www.varlena.com/varlena/GeneralBits/73.php

Cheers,
D
> Hi,
>
> I haven't done any significant progress on that way because of lack of
> free time.
> Beside this, I'm recently changed my job and now I'm woking for MySQL.
> I think it's not possible for me to continue work on PostgreSQL.
> Feel free to take the patch and develop it further as long as you
> mention me as author of initial version.
>
> Regards, Evgen.
>
> On 5/5/05, Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> wrote:
> >Hi Evgen,
> >
> >I just keep pinging this patch thread every once in a while to make sure
> >it doesn't get forgotten :)
> >
> >How is the syncing with 8.1 CVS coming along?
> >
> >Chris
> >
> >Evgen Potemkin wrote:
> >> Hi hackers!
> >>
> >> I have done initial implementation of SQL99 WITH clause (attached).
> >> It's now only for v7.3.4 and haven't a lot of checks and restrictions.
> >> It can execute only simple WITH queries like
> >> WITH tree AS (SELECT id,pnt,name,1::int AS level FROM t WHERE id=0
> >> UNION SELECT t.id,t.pnt,t.name,tree.level+1 FROM t JOIN tree ON
> >> tree.id=t.pnt ) SELECT * FROM tree;
> >> It would be great if someone with knowledge of Pg internals can make a
> >> kind of code review and make some advices how to better implement all
> >> this.
> >>
> >> Regards, Evgen.
> >>
> >>
> >> ------------------------------------------------------------------------
> >>
> >>
> >> ---------------------------(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
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings

On Wed, May 11, 2005 at 07:01:50PM -0700, Sergey Ten wrote:
> Hello all,
>
> We would like to contribute to the Postgresql community by implementing
> the following items from the TODO list
> (http://developer.postgresql.org/todo.php):
> . Allow COPY to understand \x as a hex byte . Allow COPY to optionally
> include column headings in the first line . Add XML output to COPY
>
> The changes are straightforward and include implementation of the
> features as well as modification of the regression tests and documentation.
>
> Before sending a diff file with the changes, we would like to know if
> these features have been already implemented.
>
> Best regards,
> Jason Lucas and Sergey Ten
> SourceLabs
>
> Dependable Open Source Systems
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

David--

My boss has given me approval to put up to 8 hours per week of SourceLabs'
time in on the SQL99 hierarchical query implementation. (I'm free, of
course, to supplement this with whatever of my own time I can spare.) I'm
willing to take on the work. What's the next step?

--Jason

On Mon, May 16, 2005 at 03:09:18PM -0700, jason(at)sourcelabs(dot)com wrote:
> David--
>
> My boss has given me approval to put up to 8 hours per week of
> SourceLabs' time in on the SQL99 hierarchical query implementation.

That's great! :)

> (I'm free, of course, to supplement this with whatever of my own
> time I can spare.) I'm willing to take on the work. What's the
> next step?

I suppose the first thing would be to look over the patches I
mentioned and the SQL:2003 specification, then put together a
preliminary patch and send it to -hackers.

Cheers,
David.
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!

David Fetter <david(at)fetter(dot)org> writes:
>> What's the next step?

> I suppose the first thing would be to look over the patches I
> mentioned and the SQL:2003 specification, then put together a
> preliminary patch and send it to -hackers.

You can get useful feedback long before having anything that looks
like a patch --- and I'd encourage you to do so. Send us design
notes, rough data structures, etc.

Quite honestly, hierarchical queries aren't the easiest thing in the
world and wouldn't be my recommendation of the first ... or even the
second ... backend hacking project for a new posthacker to tackle.
If that's where you feel you must start, OK, but try to get as much
feedback as soon as you can, sooner not later.

I seem to recall some discussion of how to do this in the past;
have you trolled the pghackers archives?

regards, tom lane

On T, 2005-05-17 at 00:42 -0400, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> >> What's the next step?
>
> > I suppose the first thing would be to look over the patches I
> > mentioned and the SQL:2003 specification, then put together a
> > preliminary patch and send it to -hackers.
...
> I seem to recall some discussion of how to do this in the past;
> have you trolled the pghackers archives?

I think that Jasons inspiration for doing it came from the the fact that
there are already now abandoned patches for doing it.

So studying/understanding the current patch, and describing and getting
feedback from pgsql-hackers should be quite a good way of gaining
insight in trickier parts of postgres.

So it will not be jumping at new problem and writing a patch, but rather
trying to get an existing patch into good shape for being accepted in
the backend.

--
Hannu Krosing <hannu(at)skype(dot)net>

Hannu Krosing <hannu(at)skype(dot)net> writes:
> On T, 2005-05-17 at 00:42 -0400, Tom Lane wrote:
>> I seem to recall some discussion of how to do this in the past;
>> have you trolled the pghackers archives?

> I think that Jasons inspiration for doing it came from the the fact that
> there are already now abandoned patches for doing it.

Having looked over the latest patch, my advice would be to ignore it :-(
It's almost completely devoid of documentation, except for comments
that he copied-and-pasted from elsewhere without modification. Wrong
comments are even worse than none.

What I'd like to see before anyone writes a line of code is a text
document explaining how this is going to work: what's the plan tree
structure, what happens at execution time, how much of the SQL99 spec
is going to get implemented. If you don't have that understanding
first, you're going to get buried in irrelevant details.

regards, tom lane

Tom Lane wrote:
> Hannu Krosing <hannu(at)skype(dot)net> writes:
> > On T, 2005-05-17 at 00:42 -0400, Tom Lane wrote:
> >> I seem to recall some discussion of how to do this in the past;
> >> have you trolled the pghackers archives?
>
> > I think that Jasons inspiration for doing it came from the the fact that
> > there are already now abandoned patches for doing it.
>
> Having looked over the latest patch, my advice would be to ignore it :-(
> It's almost completely devoid of documentation, except for comments
> that he copied-and-pasted from elsewhere without modification. Wrong
> comments are even worse than none.
>
> What I'd like to see before anyone writes a line of code is a text
> document explaining how this is going to work: what's the plan tree
> structure, what happens at execution time, how much of the SQL99 spec
> is going to get implemented. If you don't have that understanding
> first, you're going to get buried in irrelevant details.

I have updated the developer's FAQ to cover these suggestions on how to
start a patch:

1.4) What do I do after choosing an item to work on?

Send an email to pgsql-hackers with a proposal for what you want to do
(assuming your contribution is not trivial). Working in isolation is not
advisable because others might be working on the same TODO item, or you
might have misunderstood the TODO item. In the email, discuss both the
internal implementation method you plan to use, and any user-visible
changes (new syntax, etc). For complex patches, it is important to get
community feeback on your proposal before starting work. Failure to do
so might mean your patch is rejected.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On T, 2005-05-17 at 11:22 -0400, Tom Lane wrote:
> Hannu Krosing <hannu(at)skype(dot)net> writes:
> > On T, 2005-05-17 at 00:42 -0400, Tom Lane wrote:
> >> I seem to recall some discussion of how to do this in the past;
> >> have you trolled the pghackers archives?
>
> > I think that Jasons inspiration for doing it came from the the fact that
> > there are already now abandoned patches for doing it.
>
> Having looked over the latest patch, my advice would be to ignore it :-(
> It's almost completely devoid of documentation, except for comments
> that he copied-and-pasted from elsewhere without modification. Wrong
> comments are even worse than none.

ANd even worse - this is from the README on the website:

----8<---------8<---------8<---------8<---------8<---------8<---------8<-----
WHAT'S THIS

This is a patch which allows PgSQL to make hierarchical queries a la
Oracle do.

(c) Evgen Potemkin 2003,2004, < gppl at inbox dot ru >, entirely based
on PostgreSQL (http://www.postgresql.org)
Patch itself distributed under GPL. No warranty of any kind is given,
use it at your own risk.
----8<---------8<---------8<---------8<---------8<---------8<---------8<-----

So the license is also incompatible :(

--
Hannu Krosing <hannu(at)skype(dot)net>

On Tue, May 17, 2005 at 11:24:19PM +0300, Hannu Krosing wrote:
> On T, 2005-05-17 at 11:22 -0400, Tom Lane wrote:
> > Hannu Krosing <hannu(at)skype(dot)net> writes:
> > > On T, 2005-05-17 at 00:42 -0400, Tom Lane wrote:
> > >> I seem to recall some discussion of how to do this in the past;
> > >> have you trolled the pghackers archives?
> >
> > > I think that Jasons inspiration for doing it came from the the fact that
> > > there are already now abandoned patches for doing it.
> >
> > Having looked over the latest patch, my advice would be to ignore it :-(
> > It's almost completely devoid of documentation, except for comments
> > that he copied-and-pasted from elsewhere without modification. Wrong
> > comments are even worse than none.
>
> ANd even worse - this is from the README on the website:
>
> ----8<---------8<---------8<---------8<---------8<---------8<---------8<-----
> WHAT'S THIS
>
> This is a patch which allows PgSQL to make hierarchical queries a la
> Oracle do.
>
> (c) Evgen Potemkin 2003,2004, < gppl at inbox dot ru >, entirely based
> on PostgreSQL (http://www.postgresql.org)
> Patch itself distributed under GPL. No warranty of any kind is given,
> use it at your own risk.

It's the notes on the CONNECT BY patch, not the WITH patch. The WITH
patch, as far as I can tell, is in the public domain.

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!