Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
Tom, I think this patch is plenty complicated enough without adding useless restrictive options. +1 for no additonal GUC options. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
Gregory Stark wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Couldn't we just have it pay attention to the existing max_stack_depth? Recursive query does not consume stack. The server enters an infinite loop without consuming stack. Stack-depth error does not happen. We could have a separate guc variable which limits the maximum number of levels of recursive iterations. That might be a useful feature for DBAs that want to limit their users from issuing an infinite query. statement_timeout :) Good point. Though it occurs to me that if you set FETCH_COUNT in psql (or do the equivalent in your code ) statement_timeout becomes much less useful. i don't think statement_timeout is a good idea at all. it is not deterministic. depending on the load on the server some queries will execute while others fail. a separate GUC is needed. best regards, hans -- Cybertec Schönig Schönig GmbH PostgreSQL Solutions and Support Gröhrmühlgasse 26, A-2700 Wiener Neustadt Tel: +43/1/205 10 35 / 340 www.postgresql-support.de, www.postgresql-support.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
Hans-Juergen Schoenig wrote: Gregory Stark wrote: Joshua D. Drake [EMAIL PROTECTED] writes: i don't think statement_timeout is a good idea at all. it is not deterministic. depending on the load on the server some queries will execute while others fail. a separate GUC is needed. I don't think we need to add clutter to GUC for something that exists to handle the problem at hand. If our real concern is server utilization based on user or query resources we need to look at an overall solution for that issue not a one off for a single feature. Sincerely, Joshua D. Drake -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
[ catching up on back email ] Gregory Stark [EMAIL PROTECTED] writes: Yoshiyuki Asaba [EMAIL PROTECTED] writes: Recursive query does not consume stack. The server enters an infinite loop without consuming stack. Stack-depth error does not happen. We could have a separate guc variable which limits the maximum number of levels of recursive iterations. That might be a useful feature for DBAs that want to limit their users from issuing an infinite query. This whole thread seems to be proposing more and more complicated solutions for what is really a non-problem given Yoshiyuki-san's point. It's trivial to construct SQL queries that will run for longer than the MTBF of your hardware --- eg, forget a few join constraints. We've gotten along fine with nothing but query cancel and statement_timeout for that, and I've seen no one proposing that we need to fix it. We don't disallow you from writing an infinite loop in plpgsql, either. I think this patch is plenty complicated enough without adding useless restrictive options. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
Hi,
From: Zoltan Boszormenyi [EMAIL PROTECTED]
Subject: Re: [PATCHES] WITH RECURSIVE patch V0.1
Date: Sun, 18 May 2008 23:22:02 +0200
But I have a little problem with the output.
If it's not obvious, here is the query tweaked a little below.
...
Can we get the rows in tree order, please? I.e. something like this:
?column?
--
38
15
10
5
2
3
17
9
8
6
26
13
1
12
18
11
7
(17 rows)
No, you can't. However, you can obtain recursive path by using ARRAY
type, as another way. Here is a sample SQL.
WITH RECURSIVE x(level, parent, child, path) AS
(SELECT 1::integer, * , array[child] FROM test_connect_by
WHERE parent IS NULL
UNION ALL
SELECT x.level + 1, base.*, array_append(path, base.child)
FROM test_connect_by AS base, x WHERE base.parent = x.child
)
SELECT path, array_to_string(path, '-') FROM x
WHERE NOT EXISTS (SELECT 1 FROM test_connect_by WHERE parent =
x.child);
path | array_to_string
-+-
{18,11} | 18-11
{18,7} | 18-7
{26,13} | 26-13
{26,1} | 26-1
{26,12} | 26-12
{38,6} | 38-6
{38,17,9} | 38-17-9
{38,17,8} | 38-17-8
{38,15,10} | 38-15-10
{38,15,5,2} | 38-15-5-2
{38,15,5,3} | 38-15-5-3
(11 rows)
Regards,
--
Yoshiyuki Asaba
[EMAIL PROTECTED]
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
Hi, From: David Fetter [EMAIL PROTECTED] Subject: Re: [PATCHES] WITH RECURSIVE patch V0.1 Date: Sun, 18 May 2008 11:47:37 -0700 I tried a bunch of different queries, and so far, only these two haven't worked. Any ideas what I'm doing wrong here? WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t WHERE n 100 ) SELECT * FROM t; ERROR: cannot extract attribute from empty tuple slot Thank you for the report. I've fixed. postgres=# WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t WHERE n 100 ) SELECT count(*) FROM t; count --- 100 (1 row) Regards, -- Yoshiyuki Asaba [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
On Sat, May 24, 2008 at 03:21:01AM +0900, Yoshiyuki Asaba wrote: Hi, From: David Fetter [EMAIL PROTECTED] Subject: Re: [PATCHES] WITH RECURSIVE patch V0.1 Date: Sun, 18 May 2008 11:47:37 -0700 I tried a bunch of different queries, and so far, only these two haven't worked. Any ideas what I'm doing wrong here? WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t WHERE n 100 ) SELECT * FROM t; ERROR: cannot extract attribute from empty tuple slot Thank you for the report. I've fixed. postgres=# WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t WHERE n 100 ) SELECT count(*) FROM t; count --- 100 (1 row) Regards, -- Yoshiyuki Asaba [EMAIL PROTECTED] Great! Where is the new patch? Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
Hi, From: David Fetter [EMAIL PROTECTED] Subject: Re: [PATCHES] WITH RECURSIVE patch V0.1 Date: Fri, 23 May 2008 11:26:30 -0700 Where is the new patch? I will create the revised patch on June. This is a patch for this problem. *** ../../pgsql/src/backend/executor/nodeRecursivescan.c2008-05-24 04:45:23.0 +0900 --- src/backend/executor/nodeRecursivescan.c2008-05-24 04:47:54.0 +0900 *** *** 37,43 node-ss.ps.state-es_tuplestorestate = tuplestore_begin_heap(true, false, work_mem); } ! slot = node-ss.ps.ps_ResultTupleSlot; if (tuplestore_gettupleslot(node-ss.ps.state-es_tuplestorestate, true, slot)) return slot; --- 37,43 node-ss.ps.state-es_tuplestorestate = tuplestore_begin_heap(true, false, work_mem); } ! slot = node-ss.ss_ScanTupleSlot; if (tuplestore_gettupleslot(node-ss.ps.state-es_tuplestorestate, true, slot)) return slot; Regards, -- Yoshiyuki Asaba [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
On Sat, May 24, 2008 at 05:01:11AM +0900, Yoshiyuki Asaba wrote: Hi, From: David Fetter [EMAIL PROTECTED] Subject: Re: [PATCHES] WITH RECURSIVE patch V0.1 Date: Fri, 23 May 2008 11:26:30 -0700 Where is the new patch? I will create the revised patch on June. This is a patch for this problem. Thanks very much :) Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
On Sun, May 18, 2008 at 08:51:29PM +0900, Tatsuo Ishii wrote: WITH RECURSIVE patch V0.1 Here are patches to implement WITH RECURSIVE clause. There are some limitiations and TODO items(see the Current limitations section below). Comments are welcome. 1. Credit These patches were developed by Yoshiyuki Asaba ([EMAIL PROTECTED]) with some discussions with Tatsuo Ishii ([EMAIL PROTECTED]). This is really great! Kudos to all who made this happen :) Thanks. In addition to above, Sumitomo Electric Information Systems Co., and SRA OSS, Inc. Japan made this happen. I and Yoshiyuki Asaba are now in Ottawa to join PGCon. I hope to have some discussions on this here with anyone who are interested in this. -- Tatsuo Ishii SRA OSS, Inc. Japan I tried a bunch of different queries, and so far, only these two haven't worked. Any ideas what I'm doing wrong here? WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t WHERE n 100 ) SELECT * FROM t; ERROR: cannot extract attribute from empty tuple slot WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n 100 ) SELECT * FROM t; ERROR: cannot extract attribute from empty tuple slot Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
Hi, From: David Fetter [EMAIL PROTECTED] Subject: Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1 Date: Mon, 19 May 2008 04:36:30 -0700 I think it's the other way around. The server should not emit infinite number of records. How about adding new GUC parameter max_recursive_call? Couldn't we just have it pay attention to the existing max_stack_depth? Recursive query does not consume stack. The server enters an infinite loop without consuming stack. Stack-depth error does not happen. Regards, -- Yoshiyuki Asaba [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
Yoshiyuki Asaba [EMAIL PROTECTED] writes: Hi, From: David Fetter [EMAIL PROTECTED] Subject: Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1 Date: Mon, 19 May 2008 04:36:30 -0700 I think it's the other way around. The server should not emit infinite number of records. How about adding new GUC parameter max_recursive_call? Couldn't we just have it pay attention to the existing max_stack_depth? Recursive query does not consume stack. The server enters an infinite loop without consuming stack. Stack-depth error does not happen. We could have a separate guc variable which limits the maximum number of levels of recursive iterations. That might be a useful feature for DBAs that want to limit their users from issuing an infinite query. Note that users can always construct their query to limit the number of recursive iterations. So this would only be useful for DBAs that don't trust their users and want to impose a limit. It doesn't add any actual expressive power that SQL doesn't have already. The recursive query syntax in the spec actually does include the ability to assign an output column to show what level of recursive iteration you're on. So alternately we could have a GUC variable which just allows the DBA to prohibit any recursive query without such a column and a fiter imposing a maximum value on it. That's probably the most appropriate option. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
Couldn't we just have it pay attention to the existing max_stack_depth? Recursive query does not consume stack. The server enters an infinite loop without consuming stack. Stack-depth error does not happen. We could have a separate guc variable which limits the maximum number of levels of recursive iterations. That might be a useful feature for DBAs that want to limit their users from issuing an infinite query. statement_timeout :) Joshua D. Drake -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
Joshua D. Drake [EMAIL PROTECTED] writes: Couldn't we just have it pay attention to the existing max_stack_depth? Recursive query does not consume stack. The server enters an infinite loop without consuming stack. Stack-depth error does not happen. We could have a separate guc variable which limits the maximum number of levels of recursive iterations. That might be a useful feature for DBAs that want to limit their users from issuing an infinite query. statement_timeout :) Good point. Though it occurs to me that if you set FETCH_COUNT in psql (or do the equivalent in your code ) statement_timeout becomes much less useful. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's On-Demand Production Tuning -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
Hi, From: Zoltan Boszormenyi [EMAIL PROTECTED] Subject: Re: [PATCHES] WITH RECURSIVE patch V0.1 Date: Mon, 19 May 2008 08:19:17 +0200 Also, it seems there are no infinite recursion detection: # with recursive x(level, parent, child) as ( select 1::integer, * from test_connect_by where parent is null union all select x.level + 1, base.* from test_connect_by as base, x where base.child = x.child ) select * from x; ... it waits and waits and waits ... Well, psql might wait and wait but it's actually receiving rows. A cleverer client should be able to deal with infinite streams of records. I think it's the other way around. The server should not emit infinite number of records. How about adding new GUC parameter max_recursive_call? Regards, -- Yoshiyuki Asaba [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
Yoshiyuki Asaba írta: Hi, From: Zoltan Boszormenyi [EMAIL PROTECTED] Subject: Re: [PATCHES] WITH RECURSIVE patch V0.1 Date: Mon, 19 May 2008 08:19:17 +0200 Also, it seems there are no infinite recursion detection: # with recursive x(level, parent, child) as ( select 1::integer, * from test_connect_by where parent is null union all select x.level + 1, base.* from test_connect_by as base, x where base.child = x.child ) select * from x; ... it waits and waits and waits ... Well, psql might wait and wait but it's actually receiving rows. A cleverer client should be able to deal with infinite streams of records. I think it's the other way around. The server should not emit infinite number of records. How about adding new GUC parameter max_recursive_call? Yes, why not? MSSQL has a similar MAXRECURSION hint for WITH RECURSIVE queries according to their docs. http://msdn.microsoft.com/en-us/library/ms186243.aspx Regards, -- Yoshiyuki Asaba [EMAIL PROTECTED] -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
On Mon, May 19, 2008 at 05:57:17PM +0900, Yoshiyuki Asaba wrote: Hi, I think it's the other way around. The server should not emit infinite number of records. How about adding new GUC parameter max_recursive_call? Couldn't we just have it pay attention to the existing max_stack_depth? Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
On Sun, May 18, 2008 at 08:51:29PM +0900, Tatsuo Ishii wrote: WITH RECURSIVE patch V0.1 Here are patches to implement WITH RECURSIVE clause. There are some limitiations and TODO items(see the Current limitations section below). Comments are welcome. 1. Credit These patches were developed by Yoshiyuki Asaba ([EMAIL PROTECTED]) with some discussions with Tatsuo Ishii ([EMAIL PROTECTED]). This is really great! Kudos to all who made this happen :) I tried a bunch of different queries, and so far, only these two haven't worked. Any ideas what I'm doing wrong here? WITH RECURSIVE t(n) AS ( SELECT 1 UNION ALL SELECT n+1 FROM t WHERE n 100 ) SELECT * FROM t; ERROR: cannot extract attribute from empty tuple slot WITH RECURSIVE t(n) AS ( VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n 100 ) SELECT * FROM t; ERROR: cannot extract attribute from empty tuple slot Cheers, David. -- David Fetter [EMAIL PROTECTED] http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
On Sun, May 18, 2008 at 5:22 PM, Zoltan Boszormenyi [EMAIL PROTECTED] wrote: Can we get the rows in tree order, please? I.e. something like this: Is ordering by tree order defined in the standard when no explicit order is given? If not, it probably returns them in the order they are pulled up, which might be the fastest way. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCHES] WITH RECURSIVE patch V0.1
Merlin Moncure wrote: On Sun, May 18, 2008 at 5:22 PM, Zoltan Boszormenyi [EMAIL PROTECTED] wrote: Can we get the rows in tree order, please? I.e. something like this: Is ordering by tree order defined in the standard when no explicit order is given? If not, it probably returns them in the order they are pulled up, which might be the fastest way +1 for the fastest way, which I expect to often be find all level 1 matches, find all level 2 matches, ... If ORDER BY is important, it should be specified (although it may be difficult or impossible to properly represent ORDER BY for a tree? not sure?) I think most uses of recursive require extra client side code to deal with anyways, so only relative order is important (order within a particular branch). There are things I'd like to use this for right now. Currently I use plpgsql procedures to implement my own recursion. :-) Cheers, mark -- Mark Mielke [EMAIL PROTECTED]
