Re: additional patch for contrib/tablefunc - added to regression - Mailing list pgsql-patches

From Bruce Momjian
Subject Re: additional patch for contrib/tablefunc - added to regression
Date
Msg-id 200209290530.g8T5UeH03722@candle.pha.pa.us
Whole thread Raw
In response to additional patch for contrib/tablefunc - added to regression test  (Joe Conway <mail@joeconway.com>)
List pgsql-patches
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

pgsql-patches by date:

Previous
From: Bruce Momjian
Date:
Subject: Re: 20020822_02_pitr.patch.gz
Next
From: Bruce Momjian
Date:
Subject: Re: tweak CREATE SEQUENCE grammar