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(at)postgresql(dot)org

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073