pgsql-server/contrib/tablefunc tablefunc.c

Поиск
Список
Период
Сортировка
Искать

pgsql-server/contrib/tablefunc tablefunc.c

От:
momjian@postgresql.org (Bruce Momjian - CVS)
Дата:
CVSROOT:	/cvsroot
Module name:	pgsql-server
Changes by:	momjian@postgresql.org	02/09/11 20:19:44

Modified files:
	contrib/tablefunc: tablefunc.c 

Log message:
	> 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 in
	contrib/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, level
	int, 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, level
	int, branch text);
	ERROR:  infinite recursion detected
	
	I implemented it by checking the branch string for repeated keys
	(whether or not the branch is returned). The performance hit was pretty
	minimal -- about 1% for a moderately complex test case (220000 record
	table, 9 level tree with 3800 members).
	
	Joe Conway

FAQ