Обсуждение: additional patch for contrib/tablefunc - added to regression test

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

additional patch for contrib/tablefunc - added to regression test

От
Joe Conway
Дата:
<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)
+

Re: additional patch for contrib/tablefunc - added to regression

От
Bruce Momjian
Дата:
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

Re: additional patch for contrib/tablefunc - added to regression

От
Bruce Momjian
Дата:
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

Сайт использует файлы cookie для корректной работы и повышения удобства. Нажимая кнопку «Принять» или продолжая пользоваться сайтом, вы соглашаетесь на их использование в соответствии с Политикой в отношении обработки cookie ООО «ППГ», в том числе на передачу данных из файлов cookie сторонним статистическим и рекламным службам. Вы можете управлять настройками cookie через параметры вашего браузера