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:[EMAIL PROTECTED]]
Sent: 30 August 2000 09:58
To: '[EMAIL PROTECTED]'
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 |
+----------------------------------+
