Thread: WITH RECUSIVE patches 0723
> Hi, > > Here is the lastest WITH RECURSIVE patches against 2007/07/17 CVS (CVS > HEAD won't compile for me). > > This version includes regression tests and is almost ready for commit > IMO. I pulled fresh CVS HEAD and it seems the problem is gone. Here is the lastest WITH RECURSIVE patches against CVS HEAD. Reviewers, please let me know if you find problems with the patches. If none, I would like to commit this weekend. -- Tatsuo Ishii SRA OSS, Inc. Japan
Attachment
Tatsuo Ishii <ishii@postgresql.org> writes:
> Reviewers, please let me know if you find problems with the
> patches. If none, I would like to commit this weekend.
Has this patch actually been reviewed yet? The only reports I've
seen are from testing; nothing from anyone actually reading the
code. I know I've not looked at it yet.
regards, tom lane
> Tatsuo Ishii <ishii@postgresql.org> writes: > > Reviewers, please let me know if you find problems with the > > patches. If none, I would like to commit this weekend. > > Has this patch actually been reviewed yet? The only reports I've > seen are from testing; nothing from anyone actually reading the > code. I know I've not looked at it yet. The reviewer registered at the Wiki is David Fetter and I believe he is reading the patches. Michael Makes has contributed the ecpg part. So apparently he is knowing the ecpg part at least. I know the patch is huge. Reviewers, please let me know if you have any question about the code. I would like to do anything for helping the review. -- Tatsuo Ishii SRA OSS, Inc. Japan
On Wed, Jul 23, 2008 at 10:59:20AM -0400, Tom Lane wrote:
> Tatsuo Ishii <ishii@postgresql.org> writes:
> > Reviewers, please let me know if you find problems with the
> > patches. If none, I would like to commit this weekend.
>
> Has this patch actually been reviewed yet? The only reports I've
> seen are from testing; nothing from anyone actually reading the
> code. I know I've not looked at it yet.
I've read the code, for what that's worth, which isn't much. I just
tried out this patch on a fresh checkout of CVS TIP and found:
EXPLAIN WITH RECURSIVE t(i) AS (VALUES(1::int) UNION ALL SELECT i+1 FROM t WHERE i < 5) SELECT * FROM t AS t1 JOIN t AS
t2USING(i);
QUERY PLAN
-------------------------------------------------------------------------------------
Hash Join (cost=0.08..0.16 rows=2 width=4)
Hash Cond: (t1.i = t2.i)
-> Recursion on t1 (cost=0.00..0.06 rows=2 width=4)
-> Append (cost=0.00..0.04 rows=2 width=4)
-> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4)
-> Recursive Scan on t (cost=0.00..0.00 rows=1 width=4)
Filter: (i < 5)
-> Hash (cost=0.06..0.06 rows=2 width=4)
-> Recursion on t2 (cost=0.00..0.06 rows=2 width=4)
-> Append (cost=0.00..0.04 rows=2 width=4)
-> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4)
-> Recursive Scan on t (cost=0.00..0.00 rows=1 width=4)
Filter: (i < 5)
(13 rows)
When I try to execute the query without the EXPLAIN, having attached a debugger
to the back-end, I get.
(gdb) continue
Continuing.
Program received signal SIGSEGV, Segmentation fault.
0x08192dcd in ExecQual (qual=0xa183824, econtext=0xa183230, resultForNull=0 '\0') at execQual.c:4513
4513 expr_value = ExecEvalExpr(clause, econtext, &isNull, NULL);
(gdb) i s
#0 0x08192dcd in ExecQual (qual=0xa183824, econtext=0xa183230, resultForNull=0 '\0') at execQual.c:4513
#1 0x08199b23 in ExecScan (node=0xa1831a4, accessMtd=0x81a6bb0 <RecursivescanNext>) at execScan.c:131
#2 0x081a6ba9 in ExecRecursiveScan (node=0xa1831a4) at nodeRecursivescan.c:48
#3 0x08192320 in ExecProcNode (node=0xa1831a4) at execProcnode.c:380
#4 0x081a6923 in RecursionNext (node=0xa17fe18) at nodeRecursion.c:68
#5 0x08199a83 in ExecScan (node=0xa17fe18, accessMtd=0x81a6840 <RecursionNext>) at execScan.c:68
#6 0x081a6839 in ExecRecursion (node=0xa17fe18) at nodeRecursion.c:116
#7 0x081923e0 in ExecProcNode (node=0xa17fe18) at execProcnode.c:339
#8 0x081a1c13 in MultiExecHash (node=0xa17fcc4) at nodeHash.c:94
#9 0x081a28b9 in ExecHashJoin (node=0xa17b654) at nodeHashjoin.c:159
#10 0x081922d8 in ExecProcNode (node=0xa17b654) at execProcnode.c:395
#11 0x081901db in standard_ExecutorRun (queryDesc=0xa15c334, direction=ForwardScanDirection, count=0) at
execMain.c:1271
#12 0x08240dc4 in PortalRunSelect (portal=0xa15631c, forward=1 '\001', count=0, dest=0xa1733d8) at pquery.c:937
#13 0x082420e6 in PortalRun (portal=0xa15631c, count=2147483647, isTopLevel=1 '\001', dest=0xa1733d8,
altdest=0xa1733d8,
completionTag=0xbfcacaea "") at pquery.c:793
#14 0x0823d0a7 in exec_simple_query (
query_string=0xa12fc9c "WITH RECURSIVE t(i) AS (VALUES(1::int) UNION ALL SELECT i+1 FROM t WHERE i < 5) SELECT *
FROMt AS t1 JOIN t AS t2 USING(i);") at postgres.c:977
#15 0x0823e84c in PostgresMain (argc=4, argv=0xa0d0dac, username=0xa0d0d7c "shackle") at postgres.c:3559
#16 0x0820957f in ServerLoop () at postmaster.c:3238
#17 0x0820a4e0 in PostmasterMain (argc=3, argv=0xa0ced50) at postmaster.c:1023
#18 0x081b69d6 in main (argc=3, argv=0xa0ced50) at main.c:188
What other information could help track down this problem?
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
> On Wed, Jul 23, 2008 at 10:59:20AM -0400, Tom Lane wrote: > > Tatsuo Ishii <ishii@postgresql.org> writes: > > > Reviewers, please let me know if you find problems with the > > > patches. If none, I would like to commit this weekend. > > > > Has this patch actually been reviewed yet? The only reports I've > > seen are from testing; nothing from anyone actually reading the > > code. I know I've not looked at it yet. > > I've read the code, for what that's worth, which isn't much. I just > tried out this patch on a fresh checkout of CVS TIP and found: > > EXPLAIN WITH RECURSIVE t(i) AS (VALUES(1::int) UNION ALL SELECT i+1 FROM t WHERE i < 5) SELECT * FROM t AS t1 JOIN t ASt2 USING(i); > QUERY PLAN > ------------------------------------------------------------------------------------- > Hash Join (cost=0.08..0.16 rows=2 width=4) > Hash Cond: (t1.i = t2.i) > -> Recursion on t1 (cost=0.00..0.06 rows=2 width=4) > -> Append (cost=0.00..0.04 rows=2 width=4) > -> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4) > -> Recursive Scan on t (cost=0.00..0.00 rows=1 width=4) > Filter: (i < 5) > -> Hash (cost=0.06..0.06 rows=2 width=4) > -> Recursion on t2 (cost=0.00..0.06 rows=2 width=4) > -> Append (cost=0.00..0.04 rows=2 width=4) > -> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4) > -> Recursive Scan on t (cost=0.00..0.00 rows=1 width=4) > Filter: (i < 5) > (13 rows) > > When I try to execute the query without the EXPLAIN, having attached a debugger > to the back-end, I get. Thanks for the report. We will look into this. -- Tatsuo Ishii SRA OSS, Inc. Japan > (gdb) continue > Continuing. > > Program received signal SIGSEGV, Segmentation fault. > 0x08192dcd in ExecQual (qual=0xa183824, econtext=0xa183230, resultForNull=0 '\0') at execQual.c:4513 > 4513 expr_value = ExecEvalExpr(clause, econtext, &isNull, NULL); > (gdb) i s > #0 0x08192dcd in ExecQual (qual=0xa183824, econtext=0xa183230, resultForNull=0 '\0') at execQual.c:4513 > #1 0x08199b23 in ExecScan (node=0xa1831a4, accessMtd=0x81a6bb0 <RecursivescanNext>) at execScan.c:131 > #2 0x081a6ba9 in ExecRecursiveScan (node=0xa1831a4) at nodeRecursivescan.c:48 > #3 0x08192320 in ExecProcNode (node=0xa1831a4) at execProcnode.c:380 > #4 0x081a6923 in RecursionNext (node=0xa17fe18) at nodeRecursion.c:68 > #5 0x08199a83 in ExecScan (node=0xa17fe18, accessMtd=0x81a6840 <RecursionNext>) at execScan.c:68 > #6 0x081a6839 in ExecRecursion (node=0xa17fe18) at nodeRecursion.c:116 > #7 0x081923e0 in ExecProcNode (node=0xa17fe18) at execProcnode.c:339 > #8 0x081a1c13 in MultiExecHash (node=0xa17fcc4) at nodeHash.c:94 > #9 0x081a28b9 in ExecHashJoin (node=0xa17b654) at nodeHashjoin.c:159 > #10 0x081922d8 in ExecProcNode (node=0xa17b654) at execProcnode.c:395 > #11 0x081901db in standard_ExecutorRun (queryDesc=0xa15c334, direction=ForwardScanDirection, count=0) at execMain.c:1271 > #12 0x08240dc4 in PortalRunSelect (portal=0xa15631c, forward=1 '\001', count=0, dest=0xa1733d8) at pquery.c:937 > #13 0x082420e6 in PortalRun (portal=0xa15631c, count=2147483647, isTopLevel=1 '\001', dest=0xa1733d8, altdest=0xa1733d8, > completionTag=0xbfcacaea "") at pquery.c:793 > #14 0x0823d0a7 in exec_simple_query ( > query_string=0xa12fc9c "WITH RECURSIVE t(i) AS (VALUES(1::int) UNION ALL SELECT i+1 FROM t WHERE i < 5) SELECT * FROMt AS t1 JOIN t AS t2 USING(i);") at postgres.c:977 > #15 0x0823e84c in PostgresMain (argc=4, argv=0xa0d0dac, username=0xa0d0d7c "shackle") at postgres.c:3559 > #16 0x0820957f in ServerLoop () at postmaster.c:3238 > #17 0x0820a4e0 in PostmasterMain (argc=3, argv=0xa0ced50) at postmaster.c:1023 > #18 0x081b69d6 in main (argc=3, argv=0xa0ced50) at main.c:188 > > What other information could help track down this problem? > > Cheers, > David. > -- > David Fetter <david@fetter.org> http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david.fetter@gmail.com > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate
> On Wed, Jul 23, 2008 at 10:59:20AM -0400, Tom Lane wrote: > > Tatsuo Ishii <ishii@postgresql.org> writes: > > > Reviewers, please let me know if you find problems with the > > > patches. If none, I would like to commit this weekend. > > > > Has this patch actually been reviewed yet? The only reports I've > > seen are from testing; nothing from anyone actually reading the > > code. I know I've not looked at it yet. > > I've read the code, for what that's worth, which isn't much. I just > tried out this patch on a fresh checkout of CVS TIP and found: > > EXPLAIN WITH RECURSIVE t(i) AS (VALUES(1::int) UNION ALL SELECT i+1 FROM t WHERE i < 5) SELECT * FROM t AS t1 JOIN t ASt2 USING(i); > QUERY PLAN > ------------------------------------------------------------------------------------- > Hash Join (cost=0.08..0.16 rows=2 width=4) > Hash Cond: (t1.i = t2.i) > -> Recursion on t1 (cost=0.00..0.06 rows=2 width=4) > -> Append (cost=0.00..0.04 rows=2 width=4) > -> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4) > -> Recursive Scan on t (cost=0.00..0.00 rows=1 width=4) > Filter: (i < 5) > -> Hash (cost=0.06..0.06 rows=2 width=4) > -> Recursion on t2 (cost=0.00..0.06 rows=2 width=4) > -> Append (cost=0.00..0.04 rows=2 width=4) > -> Values Scan on "*VALUES*" (cost=0.00..0.01 rows=1 width=4) > -> Recursive Scan on t (cost=0.00..0.00 rows=1 width=4) > Filter: (i < 5) > (13 rows) > > When I try to execute the query without the EXPLAIN, having attached a debugger > to the back-end, I get. > > (gdb) continue > Continuing. > > Program received signal SIGSEGV, Segmentation fault. Thanks for the report. Here is the new patches from Yoshiyuki. It appeared that addRangeTableEntryForRecursive() needs to do deep copy for the subquery and ref in the RangeTblEntry to avoid double free bug (remember that your example is a self join case). Also I added your query to the regression test case with minor modifications. -- Tatsuo Ishii SRA OSS, Inc. Japan
Attachment
Tatsuo Ishii <ishii@postgresql.org> writes:
> Reviewers, please let me know if you find problems with the
> patches. If none, I would like to commit this weekend.
Given that everyone who has tested this has found a different way to
crash it, and that the frequency of crash reports shows no signs of
slowing down, I have to think that committing it is premature.
I tried to look through the patch just now and failed to make any
sense of it, because of the complete absence of documentation.
Two unexplained examples added to the SELECT reference page don't
do it for me. I want to see an explanation of exactly what behaviors
are intended to be provided (and, in view of the long TODO list that
was posted awhile back, what isn't provided). And there needs to be
more than zero internal documentation. A README file, or perhaps
a very long file header comment, needs to be provided to explain
what's supposed to happen, when, and where when processing a recursive
query. (For comparison look at the README.HOT file that was created
to explain the HOT patch --- something at about that level of detail
would help this patch a lot. Or consider adding a section to
chapter 43 in the SGML docs.)
We really can't accept a patch that is so poorly documented as to
be unreviewable. Unreviewable also means it'll be unmaintainable
going forward.
regards, tom lane
On Thu, Jul 24, 2008 at 01:55:37PM +0900, Tatsuo Ishii wrote:
> > Program received signal SIGSEGV, Segmentation fault.
>
> Thanks for the report. Here is the new patches from Yoshiyuki.
Thanks for the patch :)
Now, I get a different problem, this time with the following code
intended to materialize paths on the fly and summarize down to a
certain depth in a tree:
CREATE TABLE tree(
id INTEGER PRIMARY KEY,
parent_id INTEGER REFERENCES tree(id)
);
INSERT INTO tree
VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3),
(9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11);
WITH RECURSIVE t(id, path) AS (
VALUES(1,ARRAY[NULL::integer])
UNION ALL
SELECT tree.id, t.path || tree.id
FROM tree JOIN t ON (tree.parent_id = t.id)
)
SELECT
t1.id, count(t2.*)
FROM
t t1
JOIN
t t2
ON (
t1.path[1:2] = t2.path[1:2]
AND
array_upper(t1.path,1) = 2
AND
array_upper(t2.path,1) > 2
)
GROUP BY t1.id;
ERROR: unrecognized node type: 203
Please apply the attached patch to help out with tab
completion in psql.
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Attachment
> Now, I get a different problem, this time with the following code > intended to materialize paths on the fly and summarize down to a > certain depth in a tree: > > CREATE TABLE tree( > id INTEGER PRIMARY KEY, > parent_id INTEGER REFERENCES tree(id) > ); > > INSERT INTO tree > VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3), > (9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11); > > WITH RECURSIVE t(id, path) AS ( > VALUES(1,ARRAY[NULL::integer]) > UNION ALL > SELECT tree.id, t.path || tree.id > FROM tree JOIN t ON (tree.parent_id = t.id) > ) > SELECT > t1.id, count(t2.*) > FROM > t t1 > JOIN > t t2 > ON ( > t1.path[1:2] = t2.path[1:2] > AND > array_upper(t1.path,1) = 2 > AND > array_upper(t2.path,1) > 2 > ) > GROUP BY t1.id; > ERROR: unrecognized node type: 203 Thanks for the report. We will look into this. > Please apply the attached patch to help out with tab > completion in psql. Ok, it will appear in the next patches. -- Tatsuo Ishii SRA OSS, Inc. Japan
> Thanks for the patch :) > > Now, I get a different problem, this time with the following code > intended to materialize paths on the fly and summarize down to a > certain depth in a tree: > > CREATE TABLE tree( > id INTEGER PRIMARY KEY, > parent_id INTEGER REFERENCES tree(id) > ); > > INSERT INTO tree > VALUES (1, NULL), (2, 1), (3,1), (4,2), (5,2), (6,2), (7,3), (8,3), > (9,4), (10,4), (11,7), (12,7), (13,7), (14, 9), (15,11), (16,11); > > WITH RECURSIVE t(id, path) AS ( > VALUES(1,ARRAY[NULL::integer]) > UNION ALL > SELECT tree.id, t.path || tree.id > FROM tree JOIN t ON (tree.parent_id = t.id) > ) > SELECT > t1.id, count(t2.*) > FROM > t t1 > JOIN > t t2 > ON ( > t1.path[1:2] = t2.path[1:2] > AND > array_upper(t1.path,1) = 2 > AND > array_upper(t2.path,1) > 2 > ) > GROUP BY t1.id; > ERROR: unrecognized node type: 203 Thanks for the report. Here is the new patches from Yoshiyuki against CVS HEAD. Also I have added your test case to the regression test. > Please apply the attached patch to help out with tab > completion in psql. Thanks. Your patches has been included. -- Tatsuo Ishii SRA OSS, Inc. Japan