inet regression test

Bruce Momjian

pgman@candle.pha.pa.us

almost 23 years ago

In reply to: Rod Taylor (#1)

1 attachment(s)

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

Attachments:

/bjm/difftext/plainDownload

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;

tgl@sss.pgh.pa.us

almost 23 years ago

In reply to: Rod Taylor (#1)

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

Bruce Momjian

pgman@candle.pha.pa.us

almost 23 years ago

In reply to: Tom Lane (#3)

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

rbt@rbt.ca

almost 23 years ago

In reply to: Tom Lane (#3)

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>

tgl@sss.pgh.pa.us

almost 23 years ago

In reply to: Rod Taylor (#5)

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

rbt@rbt.ca

almost 23 years ago

In reply to: Tom Lane (#6)

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>

tgl@sss.pgh.pa.us

almost 23 years ago

In reply to: Rod Taylor (#7)

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

rbt@rbt.ca

almost 23 years ago

In reply to: Tom Lane (#8)

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>

#10

tgl@sss.pgh.pa.us

almost 23 years ago

In reply to: Rod Taylor (#9)

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

#11

rbt@rbt.ca

almost 23 years ago

In reply to: Tom Lane (#10)

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>

#12

tgl@sss.pgh.pa.us

almost 23 years ago

In reply to: Rod Taylor (#11)

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

#13

Sean Chittenden

sean@chittenden.org

almost 23 years ago

In reply to: Tom Lane (#12)

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

#14

rbt@rbt.ca

almost 23 years ago

In reply to: Tom Lane (#12)

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>