Re: [HACKERS] Hierarchical Queries--Status
On Thu, 2007-02-08 at 20:49 -0500, Bruce Momjian wrote: Who is working on this item? Jonah was trying to complete this for 8.3, but I believe that he has handed it onto Gregory Stark - I think http://archives.postgresql.org/pgsql-hackers/2007-01/msg01586.php is the latest update. Kind regards, Mark. ---(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
Re: [HACKERS] Hierarchical Queries--Status
Mark Cave-Ayland [EMAIL PROTECTED] writes: On Thu, 2007-02-08 at 20:49 -0500, Bruce Momjian wrote: Who is working on this item? Jonah was trying to complete this for 8.3, but I believe that he has handed it onto Gregory Stark - I think http://archives.postgresql.org/pgsql-hackers/2007-01/msg01586.php is the latest update. There's also http://archives.postgresql.org/pgsql-patches/2007-02/msg00086.php -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(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
Re: [HACKERS] Hierarchical Queries--Status
Who is working on this item? --- Martijn van Oosterhout wrote: -- Start of PGP signed section. On Mon, Sep 04, 2006 at 05:15:57PM +0100, Mark Cave-Ayland wrote: 3) Add planner support so that WITH clauses are mapped to a new type of node that utilises two tuplestores - an output tuplestore and a working tuplestore. The output tuple store will in effect be the contents of the table expression while the working tuplestore holds the results of the last iteration if recursive. Also implement some kind of WithState node which keeps track of the recursion state. That's basically what I came up with. Basically you have a sort of loop in the execution plan where tuples that come out are copied into a tuplestore and run through a particular part of the executor again. The top-down approach of the executor makes it a bit trickier... Having spent some more time today looking at 1) and also at the SQL 2003 spec, it would seem that other databases offer the WITH clause within subqueries and also as part of a view. I'd be interested to hear feedback from other developers as to whether it would be possible to achieve this level of support within PostgreSQL. Absolutly possible. The question is how much work :) Incidently, if you find a way to support common subplans (where a part of the executor is shared between two executions) that might provide a way to solve some of the trickier multiple evaluation problems with rules. Again, it would just be a tuplestore the stored the results for multiple executions. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. -- End of PGP section, PGP failed! -- Bruce Momjian [EMAIL PROTECTED] http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Hierarchical Queries--Status
On Sat, 2006-08-26 at 22:46 -0400, Jonah H. Harris wrote: On 8/26/06, Alvaro Herrera [EMAIL PROTECTED] wrote: Actually I was thinking in the design rather than the code ... Doh! We hadn't posted the design just yet. Let me write him and see where he's at and we'll throw something together for the list. [Note to Jonah: I've tried sending a similar version of this email to you a couple of times, but I'm not sure that it's getting through, hence the post to -hackers in the hope you may be able to pick it up there.] Hi everyone, I've had a chance to sit down for a day or so to see how to approach adding hierarchical queries to PostgreSQL, so I thought I'd post my initial thoughts on how to proceed. My aim is to refine the list below based upon feedback from hackers until it gets to the point where I can start work on it. Here's what I've got so far: 1) Add detection of the WITH clause to the parser. 2) Create a new type of RangeTblEntry to represent each common table expression specified within the WITH clause where the subquery field points to the nodes representing the common table expression. 3) Add planner support so that WITH clauses are mapped to a new type of node that utilises two tuplestores - an output tuplestore and a working tuplestore. The output tuple store will in effect be the contents of the table expression while the working tuplestore holds the results of the last iteration if recursive. Also implement some kind of WithState node which keeps track of the recursion state. Having spent some more time today looking at 1) and also at the SQL 2003 spec, it would seem that other databases offer the WITH clause within subqueries and also as part of a view. I'd be interested to hear feedback from other developers as to whether it would be possible to achieve this level of support within PostgreSQL. Many thanks, Mark. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Hierarchical Queries--Status
On Mon, Sep 04, 2006 at 05:15:57PM +0100, Mark Cave-Ayland wrote: 3) Add planner support so that WITH clauses are mapped to a new type of node that utilises two tuplestores - an output tuplestore and a working tuplestore. The output tuple store will in effect be the contents of the table expression while the working tuplestore holds the results of the last iteration if recursive. Also implement some kind of WithState node which keeps track of the recursion state. That's basically what I came up with. Basically you have a sort of loop in the execution plan where tuples that come out are copied into a tuplestore and run through a particular part of the executor again. The top-down approach of the executor makes it a bit trickier... Having spent some more time today looking at 1) and also at the SQL 2003 spec, it would seem that other databases offer the WITH clause within subqueries and also as part of a view. I'd be interested to hear feedback from other developers as to whether it would be possible to achieve this level of support within PostgreSQL. Absolutly possible. The question is how much work :) Incidently, if you find a way to support common subplans (where a part of the executor is shared between two executions) that might provide a way to solve some of the trickier multiple evaluation problems with rules. Again, it would just be a tuplestore the stored the results for multiple executions. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Hierarchical Queries--Status
Jonah H. Harris wrote: All, In the spirit of our previous discussion, I am writing to inform you that Mark Cave-Ayland and I will be working on this TODO-item together. We are thinking through a new design (not based on the current patch) and will post it to -hackers for approval soon. Was it posted? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Hierarchical Queries--Status
On 8/26/06, Alvaro Herrera [EMAIL PROTECTED] wrote: Was it posted? No, the patch has not yet been posted. I'm not sure of where Mark's at with it at this point in time. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Hierarchical Queries--Status
Jonah H. Harris wrote: On 8/26/06, Alvaro Herrera [EMAIL PROTECTED] wrote: Was it posted? No, the patch has not yet been posted. I'm not sure of where Mark's at with it at this point in time. Actually I was thinking in the design rather than the code ... -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Hierarchical Queries--Status
On 8/26/06, Alvaro Herrera [EMAIL PROTECTED] wrote: Actually I was thinking in the design rather than the code ... Doh! We hadn't posted the design just yet. Let me write him and see where he's at and we'll throw something together for the list. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Hierarchical Queries--Status
All, In the spirit of our previous discussion, I am writing to inform you that Mark Cave-Ayland and I will be working on this TODO-item together. We are thinking through a new design (not based on the current patch) and will post it to -hackers for approval soon. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
