equality operator on CIDR column as primary key
От | Mayers, Philip J |
---|---|
Тема | equality operator on CIDR column as primary key |
Дата | |
Msg-id | A0F836836670D41183A800508BAF190B35E040@icex1.cc.ic.ac.uk обсуждение исходный текст |
Ответы |
Re: equality operator on CIDR column as primary key
|
Список | pgsql-general |
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 | +----------------------------------+
В списке pgsql-general по дате отправления:
Следующее
От: "Mayers, Philip J"Дата:
Сообщение: RE: even more CIDR weirdness (was equality operator on CIDR colum n as primary key)