From a62873d279ef5ad97dabf077f00e2ba4b40640a4 Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Thu, 3 Oct 2002 17:15:36 +0000 Subject: [PATCH] The attached adds a bit to the contrib/tablefunc regression test for behavior of connectby() in the presence of infinite recursion. Please apply this one in addition to the one sent earlier. Joe Conway --- contrib/tablefunc/expected/tablefunc.out | 38 ++++++++++++++++++++++++++++++++ contrib/tablefunc/sql/tablefunc.sql | 21 ++++++++++++++++++ 2 files changed, 59 insertions(+) diff --git a/contrib/tablefunc/expected/tablefunc.out b/contrib/tablefunc/expected/tablefunc.out index 06d8b41e60..9b04bfa4f5 100644 --- a/contrib/tablefunc/expected/tablefunc.out +++ b/contrib/tablefunc/expected/tablefunc.out @@ -177,3 +177,41 @@ SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(k 9 | 5 | 2 (6 rows) +-- recursion detection +INSERT INTO connectby_int VALUES(10,9); +INSERT INTO connectby_int VALUES(11,10); +INSERT INTO connectby_int VALUES(9,11); +-- should fail due to infinite recursion +SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch text); +ERROR: infinite recursion detected +-- infinite recursion failure avoided by depth limit +SELECT * FROM connectby('connectby_int', '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 for falsely detected recursion +DROP TABLE connectby_int; +CREATE TABLE connectby_int(keyid int, parent_keyid int); +INSERT INTO connectby_int VALUES(11,NULL); +INSERT INTO connectby_int VALUES(10,11); +INSERT INTO connectby_int VALUES(111,11); +INSERT INTO connectby_int VALUES(1,111); +-- this should not fail due to recursion detection +SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '11', 0, '-') AS t(keyid int, parent_keyid int, level int, branch text); + keyid | parent_keyid | level | branch +-------+--------------+-------+---------- + 11 | | 0 | 11 + 10 | 11 | 1 | 11-10 + 111 | 11 | 1 | 11-111 + 1 | 111 | 2 | 11-111-1 +(4 rows) + diff --git a/contrib/tablefunc/sql/tablefunc.sql b/contrib/tablefunc/sql/tablefunc.sql index 25b430e4a0..a450670d50 100644 --- a/contrib/tablefunc/sql/tablefunc.sql +++ b/contrib/tablefunc/sql/tablefunc.sql @@ -58,3 +58,24 @@ SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') A -- without branch SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0) AS t(keyid int, parent_keyid int, level int); +-- recursion detection +INSERT INTO connectby_int VALUES(10,9); +INSERT INTO connectby_int VALUES(11,10); +INSERT INTO connectby_int VALUES(9,11); + +-- should fail due to infinite recursion +SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 0, '~') AS t(keyid int, parent_keyid int, level int, branch text); + +-- infinite recursion failure avoided by depth limit +SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '2', 4, '~') AS t(keyid int, parent_keyid int, level int, branch text); + +-- test for falsely detected recursion +DROP TABLE connectby_int; +CREATE TABLE connectby_int(keyid int, parent_keyid int); +INSERT INTO connectby_int VALUES(11,NULL); +INSERT INTO connectby_int VALUES(10,11); +INSERT INTO connectby_int VALUES(111,11); +INSERT INTO connectby_int VALUES(1,111); +-- this should not fail due to recursion detection +SELECT * FROM connectby('connectby_int', 'keyid', 'parent_keyid', '11', 0, '-') AS t(keyid int, parent_keyid int, level int, branch text); + -- 2.11.0