RE: even more CIDR weirdness (was equality operator on CIDR colum n as primary key)

Ok, now I'm getting truly confused. It works with some networks, but not
with others:

test=> select * from test;
network | netcol
----------------+--------
192.168/16 | 192/8
192/8 | 192/4
155.198/16 |
155.198.1/24 |
156.198/16 |
156.198.1/24 |
193.63.75.0/27 |
(7 rows)

test=> select * from test where network = '192.168/16';
network | netcol
---------+--------
(0 rows)

test=> select * from test where network = '193.63.75.8/27';
network | netcol
----------------+--------
193.63.75.0/27 |
(1 row)

test=> select * from test where network = '193.63.75.8/27';
network | netcol
----------------+--------
193.63.75.0/27 |
(1 row)

test=> select * from test where network = '155.198/16';
network | netcol
---------+--------
(0 rows)

=======================

WTF? I can't see any pattern to the addresses that work, and those that
don't. Help!

Regards,
Phil

+----------------------------------+
| Phil Mayers, Network Support |
| Centre for Computing Services |
| Imperial College |
+----------------------------------+

-----Original Message-----
From: Mayers, Philip J [mailto:p(dot)mayers(at)ic(dot)ac(dot)uk]
Sent: 30 August 2000 09:58
To: 'pgsql-general(at)postgresql(dot)org'
Subject: [GENERAL] equality operator on CIDR column as primary key

I'm having problems with the CIDR type. I have a table containing a column
of type CIDR, and that is the primary key. The equality operator for the
CIDR type appears to only work once per connection. The following SQL shows
a test case demonstrating the problems.

I'm running stock Redhat 6.2 on an UltraSparc5, and I built PostgreSQL from
the source RPMs available on the website. The version is:

[pjm3(at)blacklotus postgresql-7.0.2]$ rpm -q postgresql
postgresql-7.0.2-2

The problem is clearest in the "select * from test where network =
'192.168/16'" statements. The first works, and the second *exactly the same*
fails. It requires you to disconnect from the database and reconnect in
order for it to work again, and it only works once. This is a problem, since
Zope (my application) caches the open connections.

Any ideas?

FWIW, if the CIDR type is *not* the primary key, it works as expected. As a
quick hack, I can replace the primary key with a SEQUENCE type or something,
but that's not exactly optimal - I suspect this is either a code bug or a
platform-specific bug.

All comments appreciated. If someone can tell my how to even start going
about debugging postgres, I'm willing to step through the code, provide
backtraces, etc. If I think you're trustworthy <G> you can even have a shell
account on the machine in question to look at it. I suspect no-one wants to
do that though... ;o)

============================================== SQL follows

template1=> CREATE DATABASE test;
CREATE DATABASE
template1=> \c test
You are now connected to database test.
test=> CREATE TABLE test (network cidr, netcol cidr, PRIMARY KEY(network));
NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'test_pkey' for
table 'test'
CREATE
test=> insert into test (network, netcol) values ('192.168/16',
'192.168/16');
INSERT 116813 1
test=> insert into test (network, netcol) values ('192.168.1/24',
'192.168.3/24');
INSERT 116814 1
test=> select * from test;
network | netcol
--------------+--------------
192.168/16 | 192.168/16
192.168.1/24 | 192.168.3/24
(2 rows)

test=> select * from test where network = '192.168/16';
network | netcol
---------+--------
(0 rows)

test=> \q
[pjm3(at)blacklotus postgresql-7.0.2]$ psql test
Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

test=> select * from test where network = '192.168/16';
network | netcol
------------+------------
192.168/16 | 192.168/16
(1 row)

test=> select * from test where network = '192.168/16';
network | netcol
---------+--------
(0 rows)

test=> select * from test where netcol = '192.168/16';
network | netcol
------------+------------
192.168/16 | 192.168/16
(1 row)

test=> select * from test where netcol = '192.168/16';
network | netcol
------------+------------
192.168/16 | 192.168/16
(1 row)

=========================================

Regards,
Phil

+----------------------------------+
| Phil Mayers, Network Support |
| Centre for Computing Services |
| Imperial College |
+----------------------------------+