Re: Hierarchical queries a la Oracle patch. for 7.3rc1.

Hi there!

This is a new version of patch i've posted.
It's applied to version 7.3rc1.

Changed syntax, now it's more closer to Oracle's and allows operator other
than '='.
Removed Const/Var trick, now it's a new FakeVar node used, as a side effect
it's not need initdb now.
Added little regression test.
Added more comments on code.
A bit extended README.hier .

regards,

---
.evgen

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Evgen Potemkin wrote:
> Hi there!
>
> This is a new version of patch i've posted.
> It's applied to version 7.3rc1.
>
> Changed syntax, now it's more closer to Oracle's and allows operator other
> than '='.
> Removed Const/Var trick, now it's a new FakeVar node used, as a side effect
> it's not need initdb now.
> Added little regression test.
> Added more comments on code.
> A bit extended README.hier .
>
> regards,
>
> ---
> .evgen

Content-Description:

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

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

Bruce Momjian wrote:
> Your patch has been added to the PostgreSQL unapplied patches list at:
>
> http://momjian.postgresql.org/cgi-bin/pgpatches
>
> I will try to apply it within the next 48 hours.
>
> ---------------------------------------------------------------------------
>
>
> Evgen Potemkin wrote:
>
>>Hi there!
>>
>>This is a new version of patch i've posted.
>>It's applied to version 7.3rc1.
^^^^^^^^^

I've already contacted Evgen (off-list) about this patch -- it's against
7.3rc1, and has many failed hunks against cvs HEAD.

Joe

Oh, bummer. Have we drifted that much already?

---------------------------------------------------------------------------

Joe Conway wrote:
> Bruce Momjian wrote:
> > Your patch has been added to the PostgreSQL unapplied patches list at:
> >
> > http://momjian.postgresql.org/cgi-bin/pgpatches
> >
> > I will try to apply it within the next 48 hours.
> >
> > ---------------------------------------------------------------------------
> >
> >
> > Evgen Potemkin wrote:
> >
> >>Hi there!
> >>
> >>This is a new version of patch i've posted.
> >>It's applied to version 7.3rc1.
> ^^^^^^^^^
>
> I've already contacted Evgen (off-list) about this patch -- it's against
> 7.3rc1, and has many failed hunks against cvs HEAD.
>
> Joe
>
>

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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Your patch has been added to the PostgreSQL unapplied patches list at:
> http://momjian.postgresql.org/cgi-bin/pgpatches
> I will try to apply it within the next 48 hours.

Please do not apply this. I would like to see us build something that
complies to the SQL99 spec, instead. (I'm trying to organize some work
along that line within the RHDB group, but no promises yet.)

regards, tom lane

This is a pretty sizable patch, and there was discussion about it. Tom,
are you saying you will apply it with different syntax, or that you will
do another implementation.

I need feedback from another person before I can just reject this.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Your patch has been added to the PostgreSQL unapplied patches list at:
> > http://momjian.postgresql.org/cgi-bin/pgpatches
> > I will try to apply it within the next 48 hours.
>
> Please do not apply this. I would like to see us build something that
> complies to the SQL99 spec, instead. (I'm trying to organize some work
> along that line within the RHDB group, but no promises yet.)
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

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

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> This is a pretty sizable patch, and there was discussion about it. Tom,
> are you saying you will apply it with different syntax, or that you will
> do another implementation.

A new implementation --- I don't think this is usable as is (at least
not the parser part; I haven't studied the executor part closely yet).
For one thing, it ignores the agreed-on TODO items:
* Add SQL99 WITH clause to SELECT
* Add SQL99 WITH RECURSIVE to SELECT

We had the RHDB project meeting today and agreed that we'd work on this
(among other things) for 7.4. You can put down Fernando Nasser and me
as the responsible developers for these TODO items.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > This is a pretty sizable patch, and there was discussion about it. Tom,
> > are you saying you will apply it with different syntax, or that you will
> > do another implementation.
>
> A new implementation --- I don't think this is usable as is (at least
> not the parser part; I haven't studied the executor part closely yet).
> For one thing, it ignores the agreed-on TODO items:
> * Add SQL99 WITH clause to SELECT
> * Add SQL99 WITH RECURSIVE to SELECT
>
> We had the RHDB project meeting today and agreed that we'd work on this
> (among other things) for 7.4. You can put down Fernando Nasser and me
> as the responsible developers for these TODO items.

OK, TODO updated:

> * Add SQL99 WITH clause to SELECT (Tom, Fernando)
> * Add SQL99 WITH RECURSIVE to SELECT (Tom, Fernando)

and Fernando's name was added to the bottom of TODO.

OK, now who wants to tell Evgen? Have we been fair with him?

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

Bruce Momjian wrote:> This is a pretty sizable patch, and there was discussion
about it.

On which there were objections that were not addressed. I, for instance,
complained about the addition of a syntax that is neither SQL not Oracle. And
the last version of the patch still has it.

Also, I felt that there was a consensus that we should implement the SQL99 (and
DB2) recursive queries instead because:
1) It is the _standard_
2) The Oracle clause is less generic (less powerful) than the standard
3) One can add the Oracle weird clause as a syntactic sugar to a specific
recursive query construct (as I have shown in one posting)
4) Adding the Oracle clause as a hack may prevent us to implement the real SQL thing

--
Fernando Nasser
Red Hat - Toronto E-Mail: fnasser(at)redhat(dot)com
2323 Yonge Street, Suite #300
Toronto, Ontario M4P 2C9

Bruce Momjian kirjutas R, 06.12.2002 kell 00:36:
> This is a pretty sizable patch, and there was discussion about it. Tom,
> are you saying you will apply it with different syntax, or that you will
> do another implementation.
>
> I need feedback from another person before I can just reject this.

I agree with Tom, that oracle's syntax is is both limited and horrible.

OTOH, if the internal implementation is ok, and the syntax does not
clash with possible future standard SQL syntax, I'd say it would be ok
to implement.

OTOOH, the full SQL99 hierarchical queries are much more powerful and
may require more (and possibly different) internal changes than the
"oracle" way of doing it.

The question is basically - do we want to tie ourselves toforever
supporting Oracle syntax here - for example we don't do outer joins the
Oracle way using =(+).

--
Hannu Krosing <hannu(at)tm(dot)ee>

That's all I needed to hear (and Tom's comment). Thanks.

I am sure you understand I need something concrete to tell the submitter
when I reject it. Thanks.

---------------------------------------------------------------------------

Fernando Nasser wrote:
> Bruce Momjian wrote:> This is a pretty sizable patch, and there was discussion
> about it.
>
> On which there were objections that were not addressed. I, for instance,
> complained about the addition of a syntax that is neither SQL not Oracle. And
> the last version of the patch still has it.
>
> Also, I felt that there was a consensus that we should implement the SQL99 (and
> DB2) recursive queries instead because:
> 1) It is the _standard_
> 2) The Oracle clause is less generic (less powerful) than the standard
> 3) One can add the Oracle weird clause as a syntactic sugar to a specific
> recursive query construct (as I have shown in one posting)
> 4) Adding the Oracle clause as a hack may prevent us to implement the real SQL thing
>
>
>
>
>
>
> --
> Fernando Nasser
> Red Hat - Toronto E-Mail: fnasser(at)redhat(dot)com
> 2323 Yonge Street, Suite #300
> Toronto, Ontario M4P 2C9
>
>

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

Sorry, rejected due to peer review. Hopefully some of the code can be
used by them when they implement the SQL99 syntax. Thanks.

---------------------------------------------------------------------------

Evgen Potemkin wrote:
> Hi there!
>
> This is a new version of patch i've posted.
> It's applied to version 7.3rc1.
>
> Changed syntax, now it's more closer to Oracle's and allows operator other
> than '='.
> Removed Const/Var trick, now it's a new FakeVar node used, as a side effect
> it's not need initdb now.
> Added little regression test.
> Added more comments on code.
> A bit extended README.hier .
>
> regards,
>
> ---
> .evgen

Content-Description:

[ Attachment, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
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 Thu, 2002-12-05 at 20:32, Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > This is a pretty sizable patch, and there was discussion about it. Tom,
> > > are you saying you will apply it with different syntax, or that you will
> > > do another implementation.
> >
> > A new implementation --- I don't think this is usable as is (at least
> > not the parser part; I haven't studied the executor part closely yet).
> > For one thing, it ignores the agreed-on TODO items:
> > * Add SQL99 WITH clause to SELECT
> > * Add SQL99 WITH RECURSIVE to SELECT
> >
> > We had the RHDB project meeting today and agreed that we'd work on this
> > (among other things) for 7.4.

Can you disclose RHDB projects's plans for 7.4, what are these other
things ?

> > You can put down Fernando Nasser and me
> > as the responsible developers for these TODO items.

Great.

> OK, TODO updated:
>
> > * Add SQL99 WITH clause to SELECT (Tom, Fernando)
> > * Add SQL99 WITH RECURSIVE to SELECT (Tom, Fernando)
>
> and Fernando's name was added to the bottom of TODO.
>
> OK, now who wants to tell Evgen? Have we been fair with him?

I guess he can be proud of finally forcing Tom & co to do it ;)

--
Hannu Krosing <hannu(at)tm(dot)ee>

Hannu Krosing <hannu(at)tm(dot)ee> writes:
> Tom Lane wrote:
>> We had the RHDB project meeting today and agreed that we'd work on this
>> (among other things) for 7.4.

> Can you disclose RHDB projects's plans for 7.4, what are these other
> things ?

We're publicly committed to making sure point-in-time recovery happens
for 7.4. I'm not sure what else I can promise, although certainly
there'll be a lot more work on the RHDB tools --- see
http://sources.redhat.com/rhdb/

regards, tom lane