Participants
Bruce Momjian
Bruce Momjian
Joe Conway
Joe Conway
Attachments
Download Latest Patchset
+59-0
Show all attachments
Show all patchsets
Thread Outline
#1Joe ConwayJoe Conway
Sep 27, 2002
#2Bruce Momjian↳Bruce Momjianto Joe Conway (#1)
Sep 29, 2002
#3Bruce Momjian↳Bruce Momjianto Joe Conway (#1)
Oct 03, 2002

additional patch for contrib/tablefunc - added to regression test

Started by Joe Conwayalmost 24 years ago3 messagespatches
Jump to latest
#1Joe Conway
Joe Conway
mail@joeconway.com

<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

Attachments:

contrib-tablefunc-regr.1.patchtext/plain; name=contrib-tablefunc-regr.1.patchDownload+59-0
#2Bruce Momjian
Bruce Momjian
bruce@momjian.us
In reply to: Joe Conway (#1)
Re: additional patch for contrib/tablefunc - added to regression

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, 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);
+ 
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, 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)
+

---------------------------(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
#3Bruce Momjian
Bruce Momjian
bruce@momjian.us
In reply to: Joe Conway (#1)
Re: additional patch for contrib/tablefunc - added to regression

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, 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);
+ 
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, 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)
+

---------------------------(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
← Back to Topics