Thread: inet regression test
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
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;
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
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, Pennsylvania19073
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.68rows=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/25192.168.1.0/24 | 192.168.1.255/25192.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
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
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
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
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
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
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
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
> >> 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
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