Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf490dbe

Browse files
committed
> Now I'm testing connectby() in the /contrib/tablefunc in 7.3b1, which would
> be a useful function for many users. However, I found the fact that> if connectby_tree has the following data, connectby() tries to search the end> of roots without knowing that the relations are infinite(-5-9-10-11-9-10-11-).> I hope connectby() supports a check routine to find infinite relations.>>> CREATE TABLE connectby_tree(keyid int, parent_keyid int);> INSERT INTO connectby_tree VALUES(1,NULL);> INSERT INTO connectby_tree VALUES(2,1);> INSERT INTO connectby_tree VALUES(3,1);> INSERT INTO connectby_tree VALUES(4,2);> INSERT INTO connectby_tree VALUES(5,2);> INSERT INTO connectby_tree VALUES(6,4);> INSERT INTO connectby_tree VALUES(7,3);> INSERT INTO connectby_tree VALUES(8,6);> INSERT INTO connectby_tree VALUES(9,5);>> INSERT INTO connectby_tree VALUES(10,9);> INSERT INTO connectby_tree VALUES(11,10);> INSERT INTO connectby_tree VALUES(9,11); <-- infinite>The attached patch fixes the infinite recursion bug incontrib/tablefunc/tablefunc.c:connectby found by Masaru Sugawara.test=# SELECT * FROM connectby('connectby_tree', 'keyid','parent_keyid', '2', 4, '~') AS t(keyid int, parent_keyid int, levelint, branch text); keyid | parent_keyid | level | branch-------+--------------+-------+------------- 2 | | 0 | 2 4 | 2 | 1 | 2~4 6 | 4 | 2 | 2~4~6 8 | 6 | 3 | 2~4~6~8 5 | 2 | 1 | 2~5 9 | 5 | 2 | 2~5~9 10 | 9 | 3 | 2~5~9~10 11 | 10 | 4 | 2~5~9~10~11(8 rows)test=# SELECT * FROM connectby('connectby_tree', 'keyid','parent_keyid', '2', 5, '~') AS t(keyid int, parent_keyid int, levelint, branch text);ERROR: infinite recursion detectedI implemented it by checking the branch string for repeated keys(whether or not the branch is returned). The performance hit was prettyminimal -- about 1% for a moderately complex test case (220000 recordtable, 9 level tree with 3800 members).Joe Conway
1 parentb2711a0 commitf490dbe

File tree

1 file changed

+16
-15
lines changed

1 file changed

+16
-15
lines changed

‎contrib/tablefunc/tablefunc.c

Lines changed: 16 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -801,6 +801,10 @@ build_tuplestore_recursively(char *key_fld,
801801
charcurrent_level[INT32_STRLEN];
802802
char*current_branch;
803803
char**values;
804+
StringInfobranchstr=NULL;
805+
806+
/* start a new branch */
807+
branchstr=makeStringInfo();
804808

805809
if (show_branch)
806810
values= (char**)palloc(CONNECTBY_NCOLS*sizeof(char*));
@@ -852,14 +856,8 @@ build_tuplestore_recursively(char *key_fld,
852856

853857
for (i=0;i<proc;i++)
854858
{
855-
StringInfobranchstr=NULL;
856-
857-
/* start a new branch */
858-
if (show_branch)
859-
{
860-
branchstr=makeStringInfo();
861-
appendStringInfo(branchstr,"%s",branch);
862-
}
859+
/* initialize branch for this pass */
860+
appendStringInfo(branchstr,"%s",branch);
863861

864862
/* get the next sql result tuple */
865863
spi_tuple=tuptable->vals[i];
@@ -868,17 +866,16 @@ build_tuplestore_recursively(char *key_fld,
868866
current_key=SPI_getvalue(spi_tuple,spi_tupdesc,1);
869867
current_key_parent=pstrdup(SPI_getvalue(spi_tuple,spi_tupdesc,2));
870868

869+
/* check to see if this key is also an ancestor */
870+
if (strstr(branchstr->data,current_key))
871+
elog(ERROR,"infinite recursion detected");
872+
871873
/* get the current level */
872874
sprintf(current_level,"%d",level);
873875

874876
/* extend the branch */
875-
if (show_branch)
876-
{
877-
appendStringInfo(branchstr,"%s%s",branch_delim,current_key);
878-
current_branch=branchstr->data;
879-
}
880-
else
881-
current_branch=NULL;
877+
appendStringInfo(branchstr,"%s%s",branch_delim,current_key);
878+
current_branch=branchstr->data;
882879

883880
/* build a tuple */
884881
values[0]=pstrdup(current_key);
@@ -916,6 +913,10 @@ build_tuplestore_recursively(char *key_fld,
916913
per_query_ctx,
917914
attinmeta,
918915
tupstore);
916+
917+
/* reset branch for next pass */
918+
xpfree(branchstr->data);
919+
initStringInfo(branchstr);
919920
}
920921
}
921922

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp