Looks like some ORDER BY statements would be useful.

*** ./expected/inet.out	Sat Jun 16 22:05:20 2001
--- ./results/inet.out	Wed Jan 15 10:18:40 2003
***************
*** 193,212 ****
  select * from inet_tbl where i<<'192.168.1.0/24'::cidr;
         c        |        i         
  ----------------+------------------
   192.168.1.0/24 | 192.168.1.0/25
   192.168.1.0/24 | 192.168.1.255/25
-  192.168.1.0/24 | 192.168.1.226
  (3 rows)
  
  select * from inet_tbl where i<<='192.168.1.0/24'::cidr;
         c        |        i         
  ----------------+------------------
-  192.168.1.0/24 | 192.168.1.0/24
   192.168.1.0/24 | 192.168.1.226/24
!  192.168.1.0/24 | 192.168.1.255/24
   192.168.1.0/24 | 192.168.1.0/25
   192.168.1.0/24 | 192.168.1.255/25
-  192.168.1.0/24 | 192.168.1.226
  (6 rows)
  
  set enable_seqscan to on;
--- 193,212 ----
  select * from inet_tbl where i<<'192.168.1.0/24'::cidr;
         c        |        i         
  ----------------+------------------
+  192.168.1.0/24 | 192.168.1.226
   192.168.1.0/24 | 192.168.1.0/25
   192.168.1.0/24 | 192.168.1.255/25
  (3 rows)
  
  select * from inet_tbl where i<<='192.168.1.0/24'::cidr;
         c        |        i         
  ----------------+------------------
   192.168.1.0/24 | 192.168.1.226/24
!  192.168.1.0/24 | 192.168.1.226
!  192.168.1.0/24 | 192.168.1.0/24
   192.168.1.0/24 | 192.168.1.0/25
+  192.168.1.0/24 | 192.168.1.255/24
   192.168.1.0/24 | 192.168.1.255/25
  (6 rows)
  
  set enable_seqscan to on;

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
15 Jan 2003 11:05:25 -0500
Rod Taylor <rbt@rbt.ca>
inet regression test
OK, I have applied an patch to ORDER BY on those queries. I don't see
the ordering you have here, but this should fix it for you.  I also
uppercased the keywords while I was in there.

---------------------------------------------------------------------------

Rod Taylor wrote:
-- Start of PGP signed section.
> Looks like some ORDER BY statements would be useful.
> 
> *** ./expected/inet.out	Sat Jun 16 22:05:20 2001
> --- ./results/inet.out	Wed Jan 15 10:18:40 2003
> ***************
> *** 193,212 ****
>   select * from inet_tbl where i<<'192.168.1.0/24'::cidr;
>          c        |        i         
>   ----------------+------------------
>    192.168.1.0/24 | 192.168.1.0/25
>    192.168.1.0/24 | 192.168.1.255/25
> -  192.168.1.0/24 | 192.168.1.226
>   (3 rows)
>   
>   select * from inet_tbl where i<<='192.168.1.0/24'::cidr;
>          c        |        i         
>   ----------------+------------------
> -  192.168.1.0/24 | 192.168.1.0/24
>    192.168.1.0/24 | 192.168.1.226/24
> !  192.168.1.0/24 | 192.168.1.255/24
>    192.168.1.0/24 | 192.168.1.0/25
>    192.168.1.0/24 | 192.168.1.255/25
> -  192.168.1.0/24 | 192.168.1.226
>   (6 rows)
>   
>   set enable_seqscan to on;
> --- 193,212 ----
>   select * from inet_tbl where i<<'192.168.1.0/24'::cidr;
>          c        |        i         
>   ----------------+------------------
> +  192.168.1.0/24 | 192.168.1.226
>    192.168.1.0/24 | 192.168.1.0/25
>    192.168.1.0/24 | 192.168.1.255/25
>   (3 rows)
>   
>   select * from inet_tbl where i<<='192.168.1.0/24'::cidr;
>          c        |        i         
>   ----------------+------------------
>    192.168.1.0/24 | 192.168.1.226/24
> !  192.168.1.0/24 | 192.168.1.226
> !  192.168.1.0/24 | 192.168.1.0/24
>    192.168.1.0/24 | 192.168.1.0/25
> +  192.168.1.0/24 | 192.168.1.255/24
>    192.168.1.0/24 | 192.168.1.255/25
>   (6 rows)
>   
>   set enable_seqscan to on;
> 
> -- 
> Rod Taylor <rbt@rbt.ca>
> 
> PGP Key: http://www.rbt.ca/rbtpub.asc
-- End of PGP section, PGP failed!

-- 
  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

Index: src/test/regress/expected/inet.out
===================================================================
RCS file: /cvsroot/pgsql-server/src/test/regress/expected/inet.out,v
retrieving revision 1.13
diff -c -c -r1.13 inet.out
*** src/test/regress/expected/inet.out	17 Jun 2001 02:05:20 -0000	1.13
--- src/test/regress/expected/inet.out	15 Jan 2003 16:34:09 -0000
***************
*** 168,174 ****
  (14 rows)
  
  -- check the conversion to/from text and set_netmask
! select '' AS ten, set_masklen(inet(text(i)), 24) FROM INET_TBL;
   ten |   set_masklen    
  -----+------------------
       | 192.168.1.226/24
--- 168,174 ----
  (14 rows)
  
  -- check the conversion to/from text and set_netmask
! SELECT '' AS ten, set_masklen(inet(text(i)), 24) FROM INET_TBL;
   ten |   set_masklen    
  -----+------------------
       | 192.168.1.226/24
***************
*** 188,196 ****
  (14 rows)
  
  -- check that index works correctly
! create index inet_idx1 on inet_tbl(i);
! set enable_seqscan to off;
! select * from inet_tbl where i<<'192.168.1.0/24'::cidr;
         c        |        i         
  ----------------+------------------
   192.168.1.0/24 | 192.168.1.0/25
--- 188,196 ----
  (14 rows)
  
  -- check that index works correctly
! CREATE INDEX inet_idx1 ON inet_tbl(i);
! SET enable_seqscan TO off;
! SELECT * FROM inet_tbl WHERE i<<'192.168.1.0/24'::cidr ORDER BY c,i;
         c        |        i         
  ----------------+------------------
   192.168.1.0/24 | 192.168.1.0/25
***************
*** 198,204 ****
   192.168.1.0/24 | 192.168.1.226
  (3 rows)
  
! select * from inet_tbl where i<<='192.168.1.0/24'::cidr;
         c        |        i         
  ----------------+------------------
   192.168.1.0/24 | 192.168.1.0/24
--- 198,204 ----
   192.168.1.0/24 | 192.168.1.226
  (3 rows)
  
! SELECT * FROM inet_tbl WHERE i<<='192.168.1.0/24'::cidr ORDER BY c,i;
         c        |        i         
  ----------------+------------------
   192.168.1.0/24 | 192.168.1.0/24
***************
*** 209,213 ****
   192.168.1.0/24 | 192.168.1.226
  (6 rows)
  
! set enable_seqscan to on;
! drop index inet_idx1;
--- 209,213 ----
   192.168.1.0/24 | 192.168.1.226
  (6 rows)
  
! SET enable_seqscan TO on;
! DROP INDEX inet_idx1;
Index: src/test/regress/sql/inet.sql
===================================================================
RCS file: /cvsroot/pgsql-server/src/test/regress/sql/inet.sql,v
retrieving revision 1.7
diff -c -c -r1.7 inet.sql
*** src/test/regress/sql/inet.sql	17 Jun 2001 02:05:20 -0000	1.7
--- src/test/regress/sql/inet.sql	15 Jan 2003 16:34:10 -0000
***************
*** 52,63 ****
    FROM INET_TBL;
  
  -- check the conversion to/from text and set_netmask
! select '' AS ten, set_masklen(inet(text(i)), 24) FROM INET_TBL;
  -- check that index works correctly
! create index inet_idx1 on inet_tbl(i);
! set enable_seqscan to off;
! select * from inet_tbl where i<<'192.168.1.0/24'::cidr;
! select * from inet_tbl where i<<='192.168.1.0/24'::cidr;
! set enable_seqscan to on;
! drop index inet_idx1;
  
--- 52,63 ----
    FROM INET_TBL;
  
  -- check the conversion to/from text and set_netmask
! SELECT '' AS ten, set_masklen(inet(text(i)), 24) FROM INET_TBL;
  -- check that index works correctly
! CREATE INDEX inet_idx1 ON inet_tbl(i);
! SET enable_seqscan TO off;
! SELECT * FROM inet_tbl WHERE i<<'192.168.1.0/24'::cidr ORDER BY c,i;
! SELECT * FROM inet_tbl WHERE i<<='192.168.1.0/24'::cidr ORDER BY c,i;
! SET enable_seqscan TO on;
! DROP INDEX inet_idx1;
Wed, 15 Jan 2003 11:35:30 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: inet regression test
Rod Taylor <rbt@rbt.ca> writes:
> Looks like some ORDER BY statements would be useful.

To do what?  Those queries should already be producing indexscan plans.
If you're not getting the expected answers, there is something that
needs to be fixed, not papered over.

Please try

create index inet_idx1 on inet_tbl(i);
set enable_seqscan to off;
explain select * from inet_tbl where i<<'192.168.1.0/24'::cidr;

			regards, tom lane

Wed, 15 Jan 2003 14:58:34 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: inet regression test 

If you get it working, I can remove the ORDER BY's I added.

---------------------------------------------------------------------------

Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> > Looks like some ORDER BY statements would be useful.
> 
> To do what?  Those queries should already be producing indexscan plans.
> If you're not getting the expected answers, there is something that
> needs to be fixed, not papered over.
> 
> Please try
> 
> create index inet_idx1 on inet_tbl(i);
> set enable_seqscan to off;
> explain select * from inet_tbl where i<<'192.168.1.0/24'::cidr;
> 
> 			regards, tom lane
> 

-- 
  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

Wed, 15 Jan 2003 15:00:00 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: inet regression test
regression=# 
regression=# 
regression=# create index inet_idx1 on inet_tbl(i);
CREATE INDEX
regression=# set enable_seqscan to off;
SET
regression=# explain select * from inet_tbl where
i<<'192.168.1.0/24'::cidr;
                                  QUERY
PLAN                                   
-------------------------------------------------------------------------------
 Index Scan using inet_idx1 on inet_tbl  (cost=0.00..4.68 rows=7
width=64)
   Index Cond: ((i > '192.168.1.0/24'::inet) AND (i <=
'192.168.1.255'::inet))
   Filter: (i << '192.168.1.0/24'::inet)
(3 rows)

regression=# select * from inet_tbl where i<<'192.168.1.0/24'::cidr;
       c        |        i         
----------------+------------------
 192.168.1.0/24 | 192.168.1.0/25
 192.168.1.0/24 | 192.168.1.255/25
 192.168.1.0/24 | 192.168.1.226
(3 rows)


On Wed, 2003-01-15 at 14:58, Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> > Looks like some ORDER BY statements would be useful.
> 
> To do what?  Those queries should already be producing indexscan plans.
> If you're not getting the expected answers, there is something that
> needs to be fixed, not papered over.
> 
> Please try
> 
> create index inet_idx1 on inet_tbl(i);
> set enable_seqscan to off;
> explain select * from inet_tbl where i<<'192.168.1.0/24'::cidr;
> 
> 			regards, tom lane
-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
15 Jan 2003 15:19:25 -0500
Rod Taylor <rbt@rbt.ca>
Re: inet regression test
Rod Taylor <rbt@rbt.ca> writes:
>  Index Scan using inet_idx1 on inet_tbl  (cost=3D0.00..4.68 rows=3D7
> width=3D64)
>    Index Cond: ((i > '192.168.1.0/24'::inet) AND (i <=3D
> '192.168.1.255'::inet))
>    Filter: (i << '192.168.1.0/24'::inet)
> (3 rows)

That's the expected plan ...

> regression=3D# select * from inet_tbl where i<<'192.168.1.0/24'::cidr;
>        c        |        i        =20
> ----------------+------------------
>  192.168.1.0/24 | 192.168.1.0/25
>  192.168.1.0/24 | 192.168.1.255/25
>  192.168.1.0/24 | 192.168.1.226
> (3 rows)

... and that's the expected result.  So why'd you get a different result
while running the regression test?  Curiouser and curiouser...

			regards, tom lane

Wed, 15 Jan 2003 15:22:43 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: inet regression test 
With Bruce's patch came a few others -- plus a recompile with all
updates.

> ... and that's the expected result.  So why'd you get a different result
> while running the regression test?  Curiouser and curiouser...
-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
15 Jan 2003 15:27:17 -0500
Rod Taylor <rbt@rbt.ca>
Re: inet regression test
Rod Taylor <rbt@rbt.ca> writes:
> With Bruce's patch came a few others -- plus a recompile with all
> updates.

Which patch exactly?  The regression test was still passing for me
as of yesterday's sources plus the large planner commit I just made.
I'm resyncing to CVS tip at the moment ...

			regards, tom lane

Wed, 15 Jan 2003 15:29:40 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: inet regression test 
On Wed, 2003-01-15 at 15:29, Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> > With Bruce's patch came a few others -- plus a recompile with all
> > updates.
> 
> Which patch exactly?  The regression test was still passing for me
> as of yesterday's sources plus the large planner commit I just made.
> I'm resyncing to CVS tip at the moment ...

Last update was about 5 minutes after Bruce's header fix went into
place.

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
15 Jan 2003 15:31:25 -0500
Rod Taylor <rbt@rbt.ca>
Re: inet regression test
Rod Taylor <rbt@rbt.ca> writes:
> Last update was about 5 minutes after Bruce's header fix went into
> place.

Hm.  I just finished verifying that CVS tip builds and passes cleanly
on both HPUX and Linux (Red Hat 8.0).  So either you've got a build
error (did you do a "make clean" after your last update?) or there's
some really weird platform dependency involved.  What's your platform
again?

			regards, tom lane

Wed, 15 Jan 2003 16:07:41 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: inet regression test 
On Wed, 2003-01-15 at 16:07, Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> > Last update was about 5 minutes after Bruce's header fix went into
> > place.

> Hm.  I just finished verifying that CVS tip builds and passes cleanly
> on both HPUX and Linux (Red Hat 8.0).  So either you've got a build
> error (did you do a "make clean" after your last update?) or there's
> some really weird platform dependency involved.  What's your platform
> again?

I do a make distclean.

FreeBSD 4.7

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
15 Jan 2003 16:16:36 -0500
Rod Taylor <rbt@rbt.ca>
Re: inet regression test
Rod Taylor <rbt@rbt.ca> writes:
> On Wed, 2003-01-15 at 16:07, Tom Lane wrote:
>> Hm.  I just finished verifying that CVS tip builds and passes cleanly
>> on both HPUX and Linux (Red Hat 8.0).  So either you've got a build
>> error (did you do a "make clean" after your last update?) or there's
>> some really weird platform dependency involved.  What's your platform
>> again?

> I do a make distclean.

> FreeBSD 4.7

I'm still not able to duplicate any problem.  Any other FreeBSD folk see
inet regression failures in CVS tip?

			regards, tom lane

Wed, 15 Jan 2003 20:15:01 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: inet regression test 
> >> Hm.  I just finished verifying that CVS tip builds and passes cleanly
> >> on both HPUX and Linux (Red Hat 8.0).  So either you've got a build
> >> error (did you do a "make clean" after your last update?) or there's
> >> some really weird platform dependency involved.  What's your platform
> >> again?
> 
> > I do a make distclean.
> 
> > FreeBSD 4.7
> 
> I'm still not able to duplicate any problem.  Any other FreeBSD folk see
> inet regression failures in CVS tip?

Unable to reproduce this on 5.0 with PostgreSQL HEAD as of 8:10PST.
-sc

-- 
Sean Chittenden

Wed, 15 Jan 2003 20:09:48 -0800
Sean Chittenden <sean@chittenden.org>
Re: inet regression test
On Wed, 2003-01-15 at 20:15, Tom Lane wrote:
> Rod Taylor <rbt@rbt.ca> writes:
> > On Wed, 2003-01-15 at 16:07, Tom Lane wrote:
> >> Hm.  I just finished verifying that CVS tip builds and passes cleanly
> >> on both HPUX and Linux (Red Hat 8.0).  So either you've got a build
> >> error (did you do a "make clean" after your last update?) or there's
> >> some really weird platform dependency involved.  What's your platform
> >> again?
> 
> > I do a make distclean.
> 
> > FreeBSD 4.7
> 
> I'm still not able to duplicate any problem.  Any other FreeBSD folk see
> inet regression failures in CVS tip?

Comes out fine now (~ 12:30 EST5EDT). I'll keep an eye out, but it must
be something else I was doing at the time that threw it off (compiler
glitch?)

Ran the below:

make distclean > /dev/null
cvs update -dP

CFLAGS="-O2 -Wall -Wmissing-prototypes -Wmissing-declarations"
export CFLAGS

PGDIR=`pwd | sed -E 's/(.*)\/pgsql([^\/]+)$/db\2/g'`
export PGDIR

./configure --prefix=${HOME}/work/postgresql/${PGDIR} \
            --enable-depend \
            --enable-debug \
            --enable-cassert \
            --with-pgport=`perl -e 'print int(rand(a) * 999) + 6000;'`

make install > /dev/null
make check


-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
16 Jan 2003 00:42:44 -0500
Rod Taylor <rbt@rbt.ca>
Re: inet regression test