Обсуждение: additional patch for contrib/tablefunc - added to regression test
<resend> 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. Thanks, Joe Index: contrib/tablefunc/sql/tablefunc.sql =================================================================== RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/sql/tablefunc.sql,v retrieving revision 1.2 diff -c -r1.2 tablefunc.sql *** contrib/tablefunc/sql/tablefunc.sql 14 Sep 2002 19:53:59 -0000 1.2 --- contrib/tablefunc/sql/tablefunc.sql 26 Sep 2002 23:53:29 -0000 *************** *** 58,60 **** --- 58,81 ---- -- 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, levelint, 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, levelint, 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, levelint, branch text); + Index: contrib/tablefunc/expected/tablefunc.out =================================================================== RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/expected/tablefunc.out,v retrieving revision 1.2 diff -c -r1.2 tablefunc.out *** contrib/tablefunc/expected/tablefunc.out 14 Sep 2002 19:53:59 -0000 1.2 --- contrib/tablefunc/expected/tablefunc.out 26 Sep 2002 23:53:33 -0000 *************** *** 177,179 **** --- 177,217 ---- 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, levelint, 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, 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 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, levelint, 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) +
Your patch has been added to the PostgreSQL unapplied patches list at: http://candle.pha.pa.us/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --------------------------------------------------------------------------- Joe Conway wrote: > <resend> > > 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. > > Thanks, > > Joe > > Index: contrib/tablefunc/sql/tablefunc.sql > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/sql/tablefunc.sql,v > retrieving revision 1.2 > diff -c -r1.2 tablefunc.sql > *** contrib/tablefunc/sql/tablefunc.sql 14 Sep 2002 19:53:59 -0000 1.2 > --- contrib/tablefunc/sql/tablefunc.sql 26 Sep 2002 23:53:29 -0000 > *************** > *** 58,60 **** > --- 58,81 ---- > -- 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, levelint, 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, levelint, 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, levelint, branch text); > + > Index: contrib/tablefunc/expected/tablefunc.out > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/expected/tablefunc.out,v > retrieving revision 1.2 > diff -c -r1.2 tablefunc.out > *** contrib/tablefunc/expected/tablefunc.out 14 Sep 2002 19:53:59 -0000 1.2 > --- contrib/tablefunc/expected/tablefunc.out 26 Sep 2002 23:53:33 -0000 > *************** > *** 177,179 **** > --- 177,217 ---- > 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, levelint, 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, 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 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, levelint, 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) > + > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Patch applied. Thanks. --------------------------------------------------------------------------- Joe Conway wrote: > <resend> > > 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. > > Thanks, > > Joe > > Index: contrib/tablefunc/sql/tablefunc.sql > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/sql/tablefunc.sql,v > retrieving revision 1.2 > diff -c -r1.2 tablefunc.sql > *** contrib/tablefunc/sql/tablefunc.sql 14 Sep 2002 19:53:59 -0000 1.2 > --- contrib/tablefunc/sql/tablefunc.sql 26 Sep 2002 23:53:29 -0000 > *************** > *** 58,60 **** > --- 58,81 ---- > -- 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, levelint, 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, levelint, 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, levelint, branch text); > + > Index: contrib/tablefunc/expected/tablefunc.out > =================================================================== > RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/expected/tablefunc.out,v > retrieving revision 1.2 > diff -c -r1.2 tablefunc.out > *** contrib/tablefunc/expected/tablefunc.out 14 Sep 2002 19:53:59 -0000 1.2 > --- contrib/tablefunc/expected/tablefunc.out 26 Sep 2002 23:53:33 -0000 > *************** > *** 177,179 **** > --- 177,217 ---- > 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, levelint, 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, 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 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, levelint, 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) > + > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073