Hierarchical Queries--Stalled No Longer...

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

On 9/22/05, Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:

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)

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

On 9/22/05, Anthony Molinaro <amolinaro@wgen.net> wrote:

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

--
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 banderas
No reconocemos fronteras" (Jorge Gonz�lez)