Thread: Wrote a connect-by feature
Hi, I happened to feel need for something like Oracle's connect-by mechanism. In the contribs I found Joe Conways `tablefunc' but I missed some features there. So I had a look at the source code. First of all thanks a lot to Joe, I learned much of your code. Anyway, when I added the features I desire I changed it so heavily that one probably won't recognize it as being derived. By the way I think I removed some memory leaks (Sorry, Joe). I would be glad if someone liked it and perhaps if it found a place in the contribs in any way. I suppose it is even better than Oracle's connect-by. You may have a look at it on <http://www.bertram-scharpf.de/tmp/connectby.tar.gz> There's no documentation yet, only a lightweight example. Postgres-style license, of course. Comments welcome. Bertram -- Bertram Scharpf Stuttgart, Deutschland/Germany http://www.bertram-scharpf.de
Hi, Am Mittwoch, 08. Aug 2007, 00:52:41 +0200 schrieb Bertram Scharpf: > <http://www.bertram-scharpf.de/tmp/connectby.tar.gz> I just extended it. Below an example. Bertram -------------------------------- select * from simp; id | pid | nam ----+-----+---------- 1 | | Abe 2 | 1 | Homer 3 | 2 | Bart 4 | 2 | Lisa 5 | 2 | Maggie 6 | | Ned 7 | 6| Rod 8 | 6 | Todd 9 | | Clancy10 | 9 | Ralph11 | | Hyman12 | 11 | Herschel (12 rows) select repeat(' ',t.lev)||(case when t.term then '`' else '|' end)||'-'||s.nam from connectby_all( 'simp', 'id', 'pid','nam asc', null, null) as t (id integer, pid integer, lev integer, pos integer, term boolean) join simp ass on (t.id=s.id); ?column? ---------------- |-Abe `-Homer |-Bart |-Lisa `-Maggie |-Clancy `-Ralph |-Hyman `-Herschel `-Ned |-Rod `-Todd (12 rows) -- Bertram Scharpf Stuttgart, Deutschland/Germany http://www.bertram-scharpf.de
Hi, Am Mittwoch, 08. Aug 2007, 14:04:23 +0200 schrieb Bertram Scharpf: > Am Mittwoch, 08. Aug 2007, 00:52:41 +0200 schrieb Bertram Scharpf: > > <http://www.bertram-scharpf.de/tmp/connectby.tar.gz> Nobody's answering just a line. Is it such a bore? Bertram -- Bertram Scharpf Stuttgart, Deutschland/Germany http://www.bertram-scharpf.de
Bertram Scharpf wrote: > Hi, > > Am Mittwoch, 08. Aug 2007, 14:04:23 +0200 schrieb Bertram Scharpf: > >> Am Mittwoch, 08. Aug 2007, 00:52:41 +0200 schrieb Bertram Scharpf: >> >>> <http://www.bertram-scharpf.de/tmp/connectby.tar.gz> >>> > > Nobody's answering just a line. Is it such a bore? > > > > Isn't connect by the Oracle non-standard way of doing recursive queries? Please review the mailing list archives on this subject. Also, many people have their heads down trying to punch out a release, right now. cheers andrew
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Andrew Dunstan wrote: > > > Bertram Scharpf wrote: >> Hi, >> >> Am Mittwoch, 08. Aug 2007, 14:04:23 +0200 schrieb Bertram Scharpf: >> >>> Am Mittwoch, 08. Aug 2007, 00:52:41 +0200 schrieb Bertram Scharpf: >>> >>>> <http://www.bertram-scharpf.de/tmp/connectby.tar.gz> >>>> >> >> Nobody's answering just a line. Is it such a bore? I have it downloaded but have not had time to test it. >> >> >> >> > > Isn't connect by the Oracle non-standard way of doing recursive queries? > Please review the mailing list archives on this subject. > > Also, many people have their heads down trying to punch out a release, > right now. > > cheers > > andrew > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGu7mdATb/zqfZUUQRAgFSAJ0U2bPpRfV1G1TXbyBmakwqC4WfhwCcD3B2 wNWKLIF7FqFVo9KMVKMXtt8= =LPls -----END PGP SIGNATURE-----
Hi, Am Donnerstag, 09. Aug 2007, 20:44:21 -0400 schrieb Andrew Dunstan: > Bertram Scharpf wrote: > >Am Mittwoch, 08. Aug 2007, 14:04:23 +0200 schrieb Bertram Scharpf: > >>Am Mittwoch, 08. Aug 2007, 00:52:41 +0200 schrieb Bertram Scharpf: > >> > >>> <http://www.bertram-scharpf.de/tmp/connectby.tar.gz> > >>> > > > >Nobody's answering just a line. Is it such a bore? > > Isn't connect by the Oracle non-standard way of doing recursive queries? > Please review the mailing list archives on this subject. I sure spent more time and effort on googling for "recursive" and "hierarchical" than on writing it myself in the end. My solution has two features you probably will not find in any Postgres or other SQL implementation. - Inverse lookup. Instead of descending down the tassel the path leading to the requested element is shown. - A boolean field that indicates whether we have reached the last element of a node. This is useful when drawing a tree;you can give a ` instead of a | in front of the key. | Bart false | Lisa false ` Maggie true It takes almost no cost in time and memory. > Also, many people have their heads down trying to punch out a release, > right now. Wouldn't the release be a good opportunity for providing this little tool? Thanks for your answers. Bertram -- Bertram Scharpf Stuttgart, Deutschland/Germany http://www.bertram-scharpf.de
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Bertram Scharpf wrote: > Hi, > > Am Donnerstag, 09. Aug 2007, 20:44:21 -0400 schrieb Andrew Dunstan: >> Bertram Scharpf wrote: >>> Am Mittwoch, 08. Aug 2007, 14:04:23 +0200 schrieb Bertram Scharpf: >>>> Am Mittwoch, 08. Aug 2007, 00:52:41 +0200 schrieb Bertram Scharpf: >>>> >>>>> <http://www.bertram-scharpf.de/tmp/connectby.tar.gz> >>>>> >>> Nobody's answering just a line. Is it such a bore? >> Isn't connect by the Oracle non-standard way of doing recursive queries? >> Please review the mailing list archives on this subject. > > I sure spent more time and effort on googling for > "recursive" and "hierarchical" than on writing it myself in > the end. > > My solution has two features you probably will not find in > any Postgres or other SQL implementation. > > - Inverse lookup. Instead of descending down the tassel > the path leading to the requested element is shown. > > - A boolean field that indicates whether we have reached > the last element of a node. This is useful when drawing > a tree; you can give a ` instead of a | in front of the > key. > > | Bart false > | Lisa false > ` Maggie true > > It takes almost no cost in time and memory. > >> Also, many people have their heads down trying to punch out a release, >> right now. > > Wouldn't the release be a good opportunity for providing > this little tool? It would be, except that we are past feature freeze thus if it is good and we can use it, it will have to wait for 8.4. Don't be discouraged though, I am sure people will pick it up and look at it. As I said in an earlier post, I have it, but haven't have a chance to test. Joshua D. Drake > > Thanks for your answers. > > Bertram > > - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGvS6lATb/zqfZUUQRAlYLAJ4nBcgcNN0Emq5k+u6NN/HKKyLH1QCfZcA5 obNlU4RZYh6JmYsRUzmCT6I= =AkgL -----END PGP SIGNATURE-----
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Bertram Scharpf wrote:
>> Wouldn't the release be a good opportunity for providing
>> this little tool?
> It would be, except that we are past feature freeze thus if it is good
> and we can use it, it will have to wait for 8.4.
We are *very* far past feature freeze. I'd love to be doing some new
coding myself, but if we don't keep our eyes on the goal we might never
get 8.3 shipped at all.
As for whether we could accept this for 8.4, I thought the general
consensus was that we should implement the SQL-spec WITH syntax.
The only good reason for supporting CONNECT BY would be to be
Oracle-compatible, which this patch isn't. (Being Oracle-compatible
isn't necessarily good anyway; are we sure they don't have a patent
on their way of doing this?)
Various people have looked at supporting WITH in the past; IIRC Greg
Stark has been the most recent contender. I'd suggest joining forces
with him if you'd like to make this happen.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>> Bertram Scharpf wrote:
>>> Wouldn't the release be a good opportunity for providing
>>> this little tool?
> As for whether we could accept this for 8.4, I thought the general
> consensus was that we should implement the SQL-spec WITH syntax.
> The only good reason for supporting CONNECT BY would be to be
> Oracle-compatible, which this patch isn't. (Being Oracle-compatible
> isn't necessarily good anyway; are we sure they don't have a patent
> on their way of doing this?)
I believe this patch is an update to the table_funcs contrib module.
Sincerely,
Joshua D Drake
>
> Various people have looked at supporting WITH in the past; IIRC Greg
> Stark has been the most recent contender. I'd suggest joining forces
> with him if you'd like to make this happen.
>
> regards, tom lane
>
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFGvbFuATb/zqfZUUQRAiwTAJ45uppSOwSbk/d0KTENmjkMk6GK0wCgk0Tf
kirMXmEOH9fYSYt+CG9UMIM=
=D9x7
-----END PGP SIGNATURE-----
Joshua D. Drake wrote: > I believe this patch is an update to the table_funcs contrib module. > > I spent 2 minutes looking. It has no Makefile and no comments. It doesn't use our code conventions either. At that stage I stopped looking. The author needs to spend some time looking at the developer documentation and perusing the lists to see what our requirements are if he wants to be taken seriously. Also, he needs to understand that the best way to go about such a project is to float ideas first, code later, not the other way around. cheers andrew
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Andrew Dunstan wrote: > > > Joshua D. Drake wrote: >> I believe this patch is an update to the table_funcs contrib module. >> >> > > I spent 2 minutes looking. It has no Makefile and no comments. It > doesn't use our code conventions either. At that stage I stopped looking. > > The author needs to spend some time looking at the developer > documentation and perusing the lists to see what our requirements are if > he wants to be taken seriously. Also, he needs to understand that the > best way to go about such a project is to float ideas first, code later, > not the other way around. Woah there silver! Read the persons first post. He updated an existing module to fit *his* needs. He then, instead of whining to the lists about a missing feature, implemented what *he* needed. It also appears that he fixed a few bugs *and* increased the usability of the module. He then gave that code back. I say bravo and thanks for the patch. Yes the patch needs some work. I don't argue that but come on. Example 1: Son: Dad, I just took cleaned out the garage. Dad: I see you used the wrong broom and didn't close the garage door Example 2: Son: Dad, I just cleaned out the garage. Dad: Oh? Thanks, In the future use the shop broom and close the garage door. Sheesh, I know that everyone is trying to get the release out but let's not remove our community building in the process. Sincerely, Joshua D. Drkae > > cheers > > andrew > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFGvclJATb/zqfZUUQRAuGfAJwMg3ZiZy8XBG8cIMea+X+JkXshAwCeKlmf IYxJfh1FAOJ2k+bDt6QKOg4= =o9VJ -----END PGP SIGNATURE-----
Joshua D. Drake wrote: > > Andrew Dunstan wrote: > >> Joshua D. Drake wrote: >> >>> I believe this patch is an update to the table_funcs contrib module. >>> >>> >>> >> I spent 2 minutes looking. It has no Makefile and no comments. It >> doesn't use our code conventions either. At that stage I stopped looking. >> >> The author needs to spend some time looking at the developer >> documentation and perusing the lists to see what our requirements are if >> he wants to be taken seriously. Also, he needs to understand that the >> best way to go about such a project is to float ideas first, code later, >> not the other way around. >> > > Woah there silver! Read the persons first post. He updated an existing > module to fit *his* needs. He then, instead of whining to the lists > about a missing feature, implemented what *he* needed. > That might be what he said, but maybe you need to go and actually compare this with the contrib module. And he did whine that nobody was responding to his posts. :-) > It also appears that he fixed a few bugs *and* increased the usability > of the module. He then gave that code back. I say bravo and thanks for > the patch. > Again, he said so, but there if there are bugs they should be fixed quite separately from any new feature. Bug fixes we can include in this release and backport if necessary, and he should post patches for those ASAP. I'm not saying "no thanks" to his code, although we almost certainly don't want the feature in anything like this form. I am saying that if he wants the code to be considered for inclusion, he needs to follow processes and meet standards. cheers andrew
Joshua D. Drake wrote: > Tom Lane wrote: >>> Bertram Scharpf wrote: >>>> Wouldn't the release be a good opportunity for providing >>>> this little tool? > >> As for whether we could accept this for 8.4, I thought the general >> consensus was that we should implement the SQL-spec WITH syntax. >> The only good reason for supporting CONNECT BY would be to be >> Oracle-compatible, which this patch isn't. (Being Oracle-compatible >> isn't necessarily good anyway; are we sure they don't have a patent >> on their way of doing this?) > > I believe this patch is an update to the table_funcs contrib module. I guess I should weigh in here. I have to agree with Tom, namely that: 1. It is way to late for a massive change to the existing contrib for Postgres 8.3 2. Any effort involving significant changes to this functionality ought to be directed at getting SQL spec compliant recursivebehavior rather than extending the existing hack. For those reasons, I for one was not too excited to spend much time right now reviewing the patch. If Bertram would send a narrow patch that just plugged existing bugs, I'd be all ears. It would likely be applied to 8.3 and possibly earlier. Joe
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Joe Conway wrote:
> Joshua D. Drake wrote:
>> Tom Lane wrote:
>>>> Bertram Scharpf wrote:
>>>>> Wouldn't the release be a good opportunity for providing
>>>>> this little tool?
>>
>>> As for whether we could accept this for 8.4, I thought the general
>>> consensus was that we should implement the SQL-spec WITH syntax.
>>> The only good reason for supporting CONNECT BY would be to be
>>> Oracle-compatible, which this patch isn't. (Being Oracle-compatible
>>> isn't necessarily good anyway; are we sure they don't have a patent
>>> on their way of doing this?)
>>
>> I believe this patch is an update to the table_funcs contrib module.
>
> I guess I should weigh in here. I have to agree with Tom, namely that:
>
> 1. It is way to late for a massive change to the existing contrib for
> Postgres 8.3
I am not in anyway promoting this for inclusion in 8.3.
Joshua D. Drake
- --
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFGvdVlATb/zqfZUUQRAmF9AJ99WTbAD4ALyEhajSWVtD62FJ6ieQCfT49s
F8UBcgGen6Pl6M9cmVG29UI=
=ZV14
-----END PGP SIGNATURE-----
Hi, Am Samstag, 11. Aug 2007, 10:22:24 -0400 schrieb Andrew Dunstan: > Joshua D. Drake wrote: > >I believe this patch is an update to the table_funcs contrib module. > > > > > > I spent 2 minutes looking. It has no Makefile and no comments. It > doesn't use our code conventions either. At that stage I stopped looking. > > The author needs to spend some time looking at the developer > documentation and perusing the lists to see what our requirements are if > he wants to be taken seriously. I added a Makefile and a README. I scanned the Developers FAQ but found no pointer to any coding conventions. I at least changed the SQL keywords to upper case although in my eyes this is rather a bad habit than a convention. It compiles and installs correctly as well standalone as in the contribs directory. <http://www.bertram-scharpf.de/tmp/connectby.tar.gz> or $ cvs -d :pserver:anonymous@bertram-scharpf.homelinux.com:/var/cvsroot/open export -D now connectby > Also, he needs to understand that the best way to go about > such a project is to float ideas first, code later, not > the other way around. All I wanted to do is to float an idea by presenting a piece of code that does what I mean instead of describing what it should do if I considered right. In fact the most aspects intruded when I wrote it, not when I wrote about it. Sorry for any inconvenience. Bertram -- Bertram Scharpf Stuttgart, Deutschland/Germany http://www.bertram-scharpf.de
Bertram Scharpf wrote: > > All I wanted to do is to float an idea by presenting a piece > of code that does what I mean instead of describing what it > should do if I considered right. But that's exactly what I was making a point about. If you want to get something included in PostgreSQL (and that's what the -hackers list is about, after all) then writing code first is the wrong way to go about it. You need to discuss the idea and implementation before you write a single line of code, or you run the risk that it will be pretty much rejected out of hand. If you had perused the mailing list archives you would have found that thjis subject has come up before, and as Tom Lane told you we are really only interested in the SQL standard way of doing this. In addition, presenting a piece of code that has not a single comment is not likely to advance your cause at all. You say you read the Developers FAQ, but you clearly ignored this entry: http://www.postgresql.org/docs/faqs.FAQ_DEV.html#item1.4 > In fact the most aspects > intruded when I wrote it, not when I wrote about it. I don't understand this sentence. > Sorry > for any inconvenience. > > > There's no inconvenience (except possibly to you). I wouldn't have replied at all except that you didn't seem to understand why people didn't embrace your code with unalloyed enthusiasm and joy. cheers andrew
Hi, Am Samstag, 11. Aug 2007, 19:57:59 -0400 schrieb Andrew Dunstan: > You say you read the Developers FAQ, but you clearly ignored this entry: > http://www.postgresql.org/docs/faqs.FAQ_DEV.html#item1.4 > [...] you didn't seem to understand [...] Yes. I ignore and I don't understand. Thanks. Good bye! Bertram -- Bertram Scharpf Stuttgart, Deutschland/Germany http://www.bertram-scharpf.de
Am Sonntag, 12. Aug 2007, 18:36:47 +0200 schrieb Bertram Scharpf: > Good bye! I just forgot to refer to--of course--the German knight of the iron hand. Bertram -- Bertram Scharpf Stuttgart, Deutschland/Germany http://www.bertram-scharpf.de
Bertram, Just so you know, there is a site for external projects associated with PostgreSQL which are not part of the core code: www.pgfoundry.org You are quite welcome to propose your connect-by code as a project on pgFoundry. This is how many new features start out before acceptance anyway. -- Josh Berkus PostgreSQL @ Sun San Francisco
On 8/14/07, Bertram Scharpf <lists@bertram-scharpf.de> wrote: > I just forgot to refer to--of course--the German knight of > the iron hand. Prima donna? Not quite done yet? Denk mal nach. Kritik kann wohl begruendet sein. Und was die Jungs gesagt haben war sowohl inhaltlich als auch von Votrag her vollkommen angemessen. Oh well. Pity. I thought it was an interesting contribution. > Bertram Cheers, Andrej