Re: Hierarchical queries a la Oracle. Patch. - Mailing list pgsql-patches

From Evgen Potemkin
Subject Re: Hierarchical queries a la Oracle. Patch.
Date
Msg-id Pine.LNX.4.33.0211231325200.1364-100000@ns.terminal.ru
Whole thread Raw
In response to Re: Hierarchical queries a la Oracle. Patch.  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-patches
Hi Tom,

On Fri, 22 Nov 2002, Tom Lane wrote:

> Evgen Potemkin <evgent@ns.terminal.ru> writes:
> > This is a patch which allows PG to make hierarchical queries a la Oracle do.
>
> This is an extremely large patch with an extreme paucity of
> documentation.
You're right, i'll add more.
> For example, I searched in vain for any explanation of
> what the varfake field does or why it's necessary to add such a field
> to every single Var node. That's a nontrivial amount of overhead to
> pay for a feature that relatively few people will use.  (And if it is
> needed, you've surely neglected to deal with it in an awful lot of
> places that create or manipulate Vars.
Trick with Const/Var+varfake is to add to result a new column which
behave like var, but not fetched from tables by subplan.
For Var+varfake nodes everything is same as for usual Var nodes, so no need
to check them specially everywhere , with exclusion of some particular
place where it is checked. It's not a neglect.

> Perhaps a new node type would
> be a better idea than overloading Var like this.  Misusing Const is a
> bad idea too.)
May be you're right. i'll try to add a new node.

>
> I'm also wondering what you may have broken in altering the handling of
> HAVING clauses in planner.c.  What's going on there?
I add this for posibility of qualification rows after connecting them.
Like HAVING with GROUP case. I'm leave WHERE clause for subplan, because it
can considerably descrease amount of rows which is needed to connect, and
thus improve performance of query. But also there is need to qual rows after
connecting, to check _level_ column for example. This is HAVING for.

As far as i've check, nothing is broken.
>
> A more global concern is whether this really implements what Oracle
> calls CONNECT BY.  I had the idea that that was a deeper feature than
> just controlling the ordering of output, which AFAICT is what this does.
AFAIK from Oracle's SQL reference, CONNECT BY feature is exactly for controlling
order of output. The matter is that it is the specifical order, which can't
be achieved by ORDER BY clause.

> Also, the documents I've been able to find on the Web suggest that
> Oracle's view of the syntax for the feature is only tenuously related
> to what you've implemented here.
Difference only in CONNECT BY clause. This allows only to choose 2 columns
and then compare them by EQ, when Oracle allows a more complex child
selection. But this is i'm need right now. So i've made a patch.
I thought that someone else may be needed it too. So i'm post it here.
Everything is very simple.

PS May i ask a question: what amount of overhead bring addition of varfake
field? Even if there is a 1K vars only 4K memory overhead.It have checked in
a few places, so performance overhead is not so big. I just want to understand.


regards,

---
.evgen



pgsql-patches by date:

Previous
From: Larry Rosenman
Date:
Subject: Re: Resultmap for FreeBSD 4.7
Next
From: Barry Lind
Date:
Subject: Re: JDBC SQLStatus for Dynamic SQL, unsupported feature,