Thread: WITH support

WITH support

From
Edwin Ramirez
Date:
Hello,

What is the status of supporting the "WITH" keyword?

-- 
Edwin S. Ramirez, Senior Developer
Information Technology Department - ITDC
Mount Sinai Medical Center
edwin.ramirez@mssm.edu * 646-217-3112



Re: WITH support

From
Bruce Momjian
Date:
Edwin Ramirez wrote:
> Hello,
> 
> What is the status of supporting the "WITH" keyword?

I see these TODO items:
* Add SQL99 WITH clause to SELECT* Add SQL:2003 WITH RECURSIVE (hierarchical) queries to SELECT

Are they the same item?  Someone has said they will do RECURSIVE for
8.3, but there is no guarantee.

--  Bruce Momjian   bruce@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: WITH support

From
Hubert FONGARNAND
Date:
Hello,<br /><br /> I can see : WITH RECURSIVE hierarchical queries (Jonah H. Harris) in :<br /><br /><a
href="http://developer.postgresql.org/index.php/Todo:WishlistFor83">http://developer.postgresql.org/index.php/Todo:WishlistFor83</a><br
/><br/> GREAT!!! <br /><br /> Such feature is very important, because it is supported in most of commercial database<br
/>SQL Server 2003 support WITH RECURSIVE<br /> DB2 support it too (WITH RECURSIVE has been invented by IBM)<br />
Oraclesupport CONNECT BY (which is not standard, but very easier to use compare to WITH RECURSIVE)<br /><br />
PostGreSQLwould be the first opensource database supporting hierarchical queries...<br /><br /> Today we use the
"CONNECTBY" patch made by <font size="1">evgen potemkin</font> <a
href="http://gppl.moonbone.ru/,">http://gppl.moonbone.ru/,</a>it works well on 8.1...<br /> But we are ready to drop it
ifWITH RECURSIVE is integrated!!!<br /><br /> It would be great to open a page on the developper wiki on this
subject?<br/><br /> Sincerely<br /><br /> Hubert FONGARNAND<br /><br /><table cellpadding="0" cellspacing="0"
width="100%"><tr><td><pre>______     _            _       _   ____  _         __  __ _             
 
|  ___(_) __| |_   _  ___(_) __ _| | / ___|| |_ __ _ / _|/ _(_)_ __   __ _ 
| |_  | |/ _` | | | |/ __| |/ _` | | \___ \| __/ _` | |_| |_| | '_ \ / _` |
|  _| | | (_| | |_| | (__| | (_| | |  ___) | || (_| |  _|  _| | | | | (_| |
|_|   |_|\__,_|\__,_|\___|_|\__,_|_| |____/ \__\__,_|_| |_| |_|_| |_|\__, |
                                                                     |___/ 
</pre><br /></td></tr></table><br /><br /> Le jeudi 28 décembre 2006 à 22:52 -0500, Bruce Momjian a écrit : <blockquote
type="CITE"><pre>
<font color="#000000">Edwin Ramirez wrote:</font>
<font color="#000000">> Hello,</font>
<font color="#000000">> </font>
<font color="#000000">> What is the status of supporting the "WITH" keyword?</font>

<font color="#000000">I see these TODO items:</font>

<font color="#000000">    * Add SQL99 WITH clause to SELECT</font>
<font color="#000000">    * Add SQL:2003 WITH RECURSIVE (hierarchical) queries to SELECT</font>

<font color="#000000">Are they the same item?  Someone has said they will do RECURSIVE for</font>
<font color="#000000">8.3, but there is no guarantee.</font>
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td>
<br />
<br />
</td>
</tr>
</table>
</pre></blockquote> _______________________________________________<br />Ce message et les �ventuels documents joints
peuventcontenir des informations confidentielles.<br />Au cas o� il ne vous serait pas destin�, nous vous remercions de
bienvouloir 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.<br/>Les communications sur internet n'�tant pas s�curis�es, l'int�grit� de ce message n'est pas assur�e et
lasoci�t� �mettrice ne peut �tre tenue pour responsable de son contenu. 

Re: WITH support

From
David Fetter
Date:
On Thu, Dec 28, 2006 at 10:52:45PM -0500, Bruce Momjian wrote:
> Edwin Ramirez wrote:
> > Hello,
> > 
> > What is the status of supporting the "WITH" keyword?
> 
> I see these TODO items:
> 
>     * Add SQL99 WITH clause to SELECT
>     * Add SQL:2003 WITH RECURSIVE (hierarchical) queries to SELECT
> 
> Are they the same item?

Not exactly.  There is a WITH in the SQL:2003 spec (T121 and T122)
which doesn't include RECURSIVE, but they're related to the RECURSIVE
one (T131).

> Someone has said they will do RECURSIVE for 8.3, but there is no
> guarantee.

Is there code for this yet?  Who's the someone? :)

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!


Re: WITH support

From
Bruce Momjian
Date:
David Fetter wrote:
> On Thu, Dec 28, 2006 at 10:52:45PM -0500, Bruce Momjian wrote:
> > Edwin Ramirez wrote:
> > > Hello,
> > > 
> > > What is the status of supporting the "WITH" keyword?
> > 
> > I see these TODO items:
> > 
> >     * Add SQL99 WITH clause to SELECT
> >     * Add SQL:2003 WITH RECURSIVE (hierarchical) queries to SELECT
> > 
> > Are they the same item?
> 
> Not exactly.  There is a WITH in the SQL:2003 spec (T121 and T122)
> which doesn't include RECURSIVE, but they're related to the RECURSIVE
> one (T131).
> 
> > Someone has said they will do RECURSIVE for 8.3, but there is no
> > guarantee.
> 
> Is there code for this yet?  Who's the someone? :)

No code yet, and I don't remember who said they were working on it.

--  Bruce Momjian   bruce@momjian.us EnterpriseDB    http://www.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: WITH support

From
"Jonah H. Harris"
Date:
On 12/29/06, Bruce Momjian <bruce@momjian.us> wrote:
> No code yet, and I don't remember who said they were working on it.

I'm still waiting to hear from Mark Cave-Ayland on whether he's going
to pick it up or whether I'll just do it.  One way or another, there
should be some movement regarding design discussion within a week or
two.

-- 
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation            | fax: 732.331.1301
33 Wood Ave S, 3rd Floor            | jharris@enterprisedb.com
Iselin, New Jersey 08830            | http://www.enterprisedb.com/


Re: WITH support

From
Mark Cave-Ayland
Date:
On Sat, 2006-12-30 at 00:49 -0500, Jonah H. Harris wrote:
> On 12/29/06, Bruce Momjian <bruce@momjian.us> wrote:
> > No code yet, and I don't remember who said they were working on it.
> 
> I'm still waiting to hear from Mark Cave-Ayland on whether he's going
> to pick it up or whether I'll just do it.  One way or another, there
> should be some movement regarding design discussion within a week or
> two.


Hi everyone,

Sorry to come in late on this discussion - I've been playing catch-up
with emails over the festive season...

The situation regarding WITH support is that it's something which I am
picking up in my spare time (out of work hours) and so unfortunately
progress isn't as rapid as I would like. See here for my last meaningful
patch and Tom's feedback:
http://archives.postgresql.org/pgsql-patches/2006-09/msg00260.php. The
idea was to first implement just the WITH clause itself as a first
patch, and then extend to include the recursive features with a
secondary patch.

In short, if people don't mind waiting for my free cycles to come along
then I will continue to chip away at it; otherwise if it's considered an
essential for 8.3 with an April deadline then I will happily hand over
to Jonah.


Kind regards,

Mark.




Re: WITH support

From
"Jonah H. Harris"
Date:
On 12/30/06, Mark Cave-Ayland <mark.cave-ayland@ilande.co.uk> wrote:
> In short, if people don't mind waiting for my free cycles to come along
> then I will continue to chip away at it; otherwise if it's considered an
> essential for 8.3 with an April deadline then I will happily hand over
> to Jonah.

I'd say it's probably essential given my last, shall we say, "mishap".So I guess I'll start up discussion on the design
againsoon.  I'm
 
leaving in a couple days to travel up to NJ so expect discussion to
start again on Tuesday of next week.

As always, I too wouldn't have a problem if someone else is going to
see it through for 8.3.

-- 
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation            | fax: 732.331.1301
33 Wood Ave S, 3rd Floor            | jharris@enterprisedb.com
Iselin, New Jersey 08830            | http://www.enterprisedb.com/


Re: WITH support

From
Hubert FONGARNAND
Date:
Le mardi 02 janvier 2007 à 18:08 -0700, Jonah H. Harris a écrit : <blockquote type="CITE"><pre>
<font color="#000000">On 12/30/06, Mark Cave-Ayland <<a
href="mailto:mark.cave-ayland@ilande.co.uk">mark.cave-ayland@ilande.co.uk</a>>wrote:</font>
 
<font color="#000000">> In short, if people don't mind waiting for my free cycles to come along</font>
<font color="#000000">> then I will continue to chip away at it; otherwise if it's considered an</font>
<font color="#000000">> essential for 8.3 with an April deadline then I will happily hand over</font>
<font color="#000000">> to Jonah.</font>

<font color="#000000">I'd say it's probably essential given my last, shall we say, "mishap".</font>
<font color="#000000"> So I guess I'll start up discussion on the design again soon.  I'm</font>
<font color="#000000">leaving in a couple days to travel up to NJ so expect discussion to</font>
<font color="#000000">start again on Tuesday of next week.</font>

<font color="#000000">As always, I too wouldn't have a problem if someone else is going to</font>
<font color="#000000">see it through for 8.3.</font>

</pre></blockquote> Why not looking at <a href="http://gppl.moonbone.ru/">http://gppl.moonbone.ru/</a> <font
size="1">evgenpotemkin.</font> has ever made a patch for WITH and CONNECT BY?<br /><br /> I'm ready to test these
features...(RECURSIVE) when they'll land in CVS...<br /><br /> Hubert FONGARNAND
_______________________________________________<br/>Ce message et les �ventuels documents joints peuvent contenir des
informationsconfidentielles.<br />Au cas o� il ne vous serait pas destin�, nous vous remercions de bien vouloir le
supprimeret en aviser imm�diatement l'exp�diteur. Toute utilisation de ce message non conforme � sa destination, toute
diffusionou publication, totale ou partielle et quel qu'en soit le moyen est formellement interdite.<br />Les
communicationssur internet n'�tant pas s�curis�es, l'int�grit� de ce message n'est pas assur�e et la soci�t� �mettrice
nepeut �tre tenue pour responsable de son contenu. 

Re: WITH support

From
Mark Cave-Ayland
Date:
On Wed, 2007-01-03 at 09:45 +0100, Hubert FONGARNAND wrote:

> Why not looking at http://gppl.moonbone.ru/ evgen potemkin. has ever
> made a patch for WITH and CONNECT BY?
> 
> I'm ready to test these features... (RECURSIVE) when they'll land in
> CVS...


Hi Hubert,

IIRC there were two issues - firstly the license for the patch was GPL
as opposed to BSD used for PostgreSQL, and secondly I believe Tom and
some of the other developers were not happy with some of the internal
changes made by the patch. Fortunately it looks as if Jonah can commit
some serious time to this, so your offer of helping to test when the
code hits CVS is gratefully received :)


Kind regards,

Mark.




Re: WITH support

From
"Jonah H. Harris"
Date:
On 1/3/07, Hubert FONGARNAND <informatique.internet@fiducial.fr> wrote:
> Why not looking at http://gppl.moonbone.ru/ evgen potemkin. has ever made a
> patch for WITH and CONNECT BY?

Nope, no good.  This is what I started with last time and the
refactoring attempt at WITH is just too massive.  As for CONNECT BY,
PostgreSQL does not want the syntax, they want ANSI/ISO WITH
[RECURSIVE]... so even starting there isn't great.

This is really something that needs to be decided on before
implementation... or it will probably never get in.

-- 
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation            | fax: 732.331.1301
33 Wood Ave S, 3rd Floor            | jharris@enterprisedb.com
Iselin, New Jersey 08830            | http://www.enterprisedb.com/


Re: WITH support

From
"Jonah H. Harris"
Date:
On 1/3/07, Mark Cave-Ayland <mark.cave-ayland@ilande.co.uk> wrote:
> IIRC there were two issues - firstly the license for the patch was GPL
> as opposed to BSD used for PostgreSQL

Yes, however Evgen was kind enough to grant me a BSD license for it
should I get it committed into PostgreSQL.  However, with the amount
of refactoring and community design decisions to be made, I doubt any
of the patch would be usable.

> ... secondly I believe Tom and some of the other developers were not
> happy with some of the internal changes made by the patch.

Yes, there are numerous bug reports from patched versions especially
on Gentoo.  As for the WITH support, it was written for PostgreSQL
7.2/7.3 and was a basic preliminary proof-of-concept.

-- 
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation            | fax: 732.331.1301
33 Wood Ave S, 3rd Floor            | jharris@enterprisedb.com
Iselin, New Jersey 08830            | http://www.enterprisedb.com/


Re: WITH support

From
David Fetter
Date:
On Wed, Jan 03, 2007 at 04:19:19PM -0700, Jonah H. Harris wrote:
> On 1/3/07, Hubert FONGARNAND <informatique.internet@fiducial.fr> wrote:
> >Why not looking at http://gppl.moonbone.ru/ evgen potemkin. has
> >ever made a patch for WITH and CONNECT BY?
> 
> Nope, no good.  This is what I started with last time and the
> refactoring attempt at WITH is just too massive.  As for CONNECT BY,
> PostgreSQL does not want the syntax, they want ANSI/ISO WITH
> [RECURSIVE]... so even starting there isn't great.
> 
> This is really something that needs to be decided on before
> implementation... or it will probably never get in.

As I recall, it was decided long ago, and the conclusions were:

* Only BSD-compatibly licensed code goes in PostgreSQL's code base,

and

* PostgreSQL will only support the SQL:2003 standard WITH (RECURSIVE) syntax in the main line code.

Cheers,
D
-- 
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666                             Skype: davidfetter

Remember to vote!


Re: WITH support

From
"Jonah H. Harris"
Date:
On 1/3/07, David Fetter <david@fetter.org> wrote:
> As I recall, it was decided long ago, and the conclusions were:
> * Only BSD-compatibly licensed code goes in PostgreSQL's code base,
> * PostgreSQL will only support the SQL:2003 standard WITH (RECURSIVE)
>  syntax in the main line code.

Yes, see later response :)

-- 
Jonah H. Harris, Software Architect | phone: 732.331.1324
EnterpriseDB Corporation            | fax: 732.331.1301
33 Wood Ave S, 3rd Floor            | jharris@enterprisedb.com
Iselin, New Jersey 08830            | http://www.enterprisedb.com/