RE: even more CIDR weirdness (was equality operator on CIDR colum n as primary key) - Mailing list pgsql-general

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.mayers@ic.ac.uk]
Sent: 30 August 2000 09:58
To: 'pgsql-general@postgresql.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@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@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                 |
+----------------------------------+