Thread: Hierarchical Queries--Stalled No Longer...

Hey everyone,<br /><br /> Evgen Potemkin has granted me a BSD license on the patch for hierarchical queries (WITH and
CONNECTBY) and I'd like to get it on track for PostgreSQL 8.2.  Tom, Bruce, Simon, Alvaro, Josh, et al., have you guys
usedthe patch?  If so, what comments do you have as to what you'd like to see.<br /><br /> In the mean time, I'll pull
8.1,patch it, write some regression tests, test it, and submit it back.  BTW, what's the estimated schedule of 8.2?<br
clear="all"/><br />-- <br />Respectfully,<br /><br />Jonah H. Harris, Database Internals Architect<br />EnterpriseDB
Corporation<br/><a href="http://www.enterprisedb.com/">http://www.enterprisedb.com/</a><br /> 

On Thu, Sep 22, 2005 at 11:19:13AM -0400, Jonah H. Harris wrote:

Hi,

> Evgen Potemkin has granted me a BSD license on the patch for hierarchical
> queries (WITH and CONNECT BY) and I'd like to get it on track for PostgreSQL
> 8.2. Tom, Bruce, Simon, Alvaro, Josh, et al., have you guys used the patch?
> If so, what comments do you have as to what you'd like to see.

Tom has repeteadly said the patch is more or less useless, and that if
you wanted to implement this feature you'd better start from scratch.

I've looked at the patch a couple of times and I somewhat agree with
this, though I don't remember what my reservations were.  One important
point is that CONNECT BY is not really SQL syntax, is it?  In this case,
I think you should pull out the CONNECT BY part and implement only WITH,
which is the SQL-mandated syntax AFAIK.

One point with the patch AFAIR is that it didn't try to optimize the
query at all, which may be OK as a first cut but for a real-world
implementation you really need it to do.

-- 
Alvaro Herrera                        http://www.advogato.org/person/alvherre
"Oh, great altar of passive entertainment, bestow upon me thy discordant images
at such speed as to render linear thought impossible" (Calvin a la TV)

Alvaro,

I agree, there are some things that need to be done before calling it a done-deal including some planning, commenting, optimizer stuff, etc.  Also, for PostgreSQL reasons, I agree that supporting ANSI/ISO WITH is the best option; I'm willing to take on implementation for both if you guys want.

-Jonah






--
Respectfully,

Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
http://www.enterprisedb.com/

"Jonah H. Harris" <jonah.harris@gmail.com> writes:
> Evgen Potemkin has granted me a BSD license on the patch for hierarchical
> queries (WITH and CONNECT BY) and I'd like to get it on track for PostgreSQ=
> L
> 8.2. Tom, Bruce, Simon, Alvaro, Josh, et al., have you guys used the patch?

If this is the same patch that periodically burns Gentoo users, then yes,
we've seen it, and we were unimpressed.

> If so, what comments do you have as to what you'd like to see.

A rewrite from the ground up, and use of SQL-standard syntax (WITH etc)
not Oracle-proprietary.
        regards, tom lane

Jonah,

> Tom, Bruce, Simon, Alvaro, Josh, et al., have you guys used the patch?
> If so, what comments do you have as to what you'd like to see.

Unfortunately, yes I have. I had to help several users who got burned by 
it: the patch was buggy as all-get out.  For example, it wouldn't access 
TOAST tables, so any TEXT fields larger than 500chars got cut off; and 
wouldn't support user-added data types or domains.  (This was a year ago, 
so maybe Evgen fixed these things)

So it's really nice of Evgen to re-license, but the license was *not* the 
primary thing blocking acceptance of the patch.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

So is postgresql going into the direction of WITH or CONNECT BY (or
both)?

I am authoring O'Reilly's "SQL Cookbook" and I'd like to mention it in
the
Hierarchical chapter to give the pg readers a heads up.

Thanks and regards, Anthony Molinaro

-----Original Message-----
From: pgsql-hackers-owner@postgresql.org
[mailto:pgsql-hackers-owner@postgresql.org] On Behalf Of Josh Berkus
Sent: Thursday, September 22, 2005 2:02 PM
To: pgsql-hackers@postgresql.org; Jonah H. Harris
Subject: Re: [HACKERS] Hierarchical Queries--Stalled No Longer...

Jonah,

> Tom, Bruce, Simon, Alvaro, Josh, et al., have you guys used the patch?
> If so, what comments do you have as to what you'd like to see.

Unfortunately, yes I have. I had to help several users who got burned by

it: the patch was buggy as all-get out.  For example, it wouldn't access

TOAST tables, so any TEXT fields larger than 500chars got cut off; and
wouldn't support user-added data types or domains.  (This was a year
ago,
so maybe Evgen fixed these things)

So it's really nice of Evgen to re-license, but the license was *not*
the
primary thing blocking acceptance of the patch.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
              http://archives.postgresql.org

Anthony,

> So is postgresql going into the direction of WITH or CONNECT BY (or
> both)?

PostgreSQL would do ONLY "with".  We're not interested in 
Oracle-proprietary syntax.

That being said, there is a CONNECT_BY() function in /contrib/tablefunc.   
But this would never be part of the core syntax.

> I am authoring O'Reilly's "SQL Cookbook" and I'd like to mention it in
> the
> Hierarchical chapter to give the pg readers a heads up.

Keen.   Please feel free to ask questions so that the PG section can be as 
accurate as possible.  #postgresql on irc.freenode.net is quite active, 
and you can get any number of Postgres - SQL questions answered there.

--Josh

-- 
__Aglio Database Solutions_______________
Josh Berkus               Consultant
josh@agliodbs.com     www.agliodbs.com
Ph: 415-752-2500    Fax: 415-752-2387
2166 Hayes Suite 200    San Francisco, CA

Josh,
Thanks man, good to know.

Sorry if the question was a bit out of place on this list
but I wanted to make sure I reached the right people.

I love what you guys are doing and made sure postgresql
was included in my book.

also, while I got your ear. I bugged Simon about this
earlier this year and was wondering if you guys are still
planning on added the window functions added to the '03 standard?

I have a ton of recipes that use them and if you guys are still
planning on implementing them, I'd like to mention that as well.

Thanks, Anthony

-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Thursday, September 22, 2005 2:43 PM
To: Anthony Molinaro
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Hierarchical Queries--Stalled No Longer...

Anthony,

> So is postgresql going into the direction of WITH or CONNECT BY (or
> both)?

PostgreSQL would do ONLY "with".  We're not interested in
Oracle-proprietary syntax.

That being said, there is a CONNECT_BY() function in /contrib/tablefunc.

But this would never be part of the core syntax.

> I am authoring O'Reilly's "SQL Cookbook" and I'd like to mention it in
> the
> Hierarchical chapter to give the pg readers a heads up.

Keen.   Please feel free to ask questions so that the PG section can be
as
accurate as possible.  #postgresql on irc.freenode.net is quite active,
and you can get any number of Postgres - SQL questions answered there.

--Josh

--
__Aglio Database Solutions_______________
Josh Berkus               Consultant
josh@agliodbs.com     www.agliodbs.com
Ph: 415-752-2500    Fax: 415-752-2387
2166 Hayes Suite 200    San Francisco, CA

Anthony,

> also, while I got your ear. I bugged Simon about this
> earlier this year and was wondering if you guys are still
> planning on added the window functions added to the '03 standard?
>
> I have a ton of recipes that use them and if you guys are still
> planning on implementing them, I'd like to mention that as well.

Planning, yes.   Have started, no.  It's a major feature implementation if 
we want them to be at all worthwhile; I'd like users to be able to create 
custom windowing aggregates, for that matter.

-- 
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Josh, Great, thanks for the update.

> It's a major feature implementation if
> we want them to be at all worthwhile

agreed. SS 2005 added partial support for window functions
(can't create moving windows of aggregation, ie, the portion of the syntax the standard calls the "framing clause")
and I didn't like that.

Imho, that wasn't cool at all.
Either support these functions all the way or don't; halfway is silly.

Btw, some of my postgres reviewers, when they came across recipes
that used the new GENERATE_SERIES function, were quite happy
(in particular for pivoting so you don't need to have extra tableslying around).
It's a cool addition and I've gotten positive feedback from it.
So, whoever dreamt it up, nice job. :)

Thanks and regards, Anthony
-----Original Message-----
From: Josh Berkus [mailto:josh@agliodbs.com]
Sent: Thursday, September 22, 2005 3:05 PM
To: pgsql-hackers@postgresql.org
Cc: Anthony Molinaro
Subject: Re: [HACKERS] Hierarchical Queries--Stalled No Longer...

Anthony,

> also, while I got your ear. I bugged Simon about this
> earlier this year and was wondering if you guys are still
> planning on added the window functions added to the '03 standard?
>
> I have a ton of recipes that use them and if you guys are still
> planning on implementing them, I'd like to mention that as well.

Planning, yes.   Have started, no.  It's a major feature implementation
if
we want them to be at all worthwhile; I'd like users to be able to
create
custom windowing aggregates, for that matter.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Anthony,

I'm reviewing your book :).  One of the reasons that I want to add this support is because, in your recipies, it's obvious that PostgreSQL is lacking in this area... likewise, we've had several EDB requests for hierarchical queries (ala Oracle-style)...  For the PostgreSQL community, I'll work on adding the ANSI/ISO WITH standard syntax just as SQL Server/DB2 have.

-Jonah




--
Respectfully,

Jonah H. Harris, Database Internals Architect
EnterpriseDB Corporation
http://www.enterprisedb.com/

On Thu, Sep 22, 2005 at 05:37:51PM -0400, Jonah H. Harris wrote:
> Anthony,
> 
> I'm reviewing your book :). One of the reasons that I want to add this
> support is because, in your recipies, it's obvious that PostgreSQL is
> lacking in this area... likewise, we've had several EDB requests for
> hierarchical queries (ala Oracle-style)... For the PostgreSQL community,
> I'll work on adding the ANSI/ISO WITH standard syntax just as SQL Server/DB2
> have.

Maybe the recipes could be reworked to use the connect_by() function,
wherever possible ...

-- 
Alvaro Herrera                        http://www.advogato.org/person/alvherre
"No necesitamos banderasNo reconocemos fronteras"                  (Jorge González)