Selects on tables with cidr type primary keys are broken

Started by Lennert Buytenhekover 25 years ago2 messagesbugs
Jump to latest
#1Lennert Buytenhek
buytenh@gnu.org

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Lennert Buytenhek
Your email address : buytenh@gnu.org

System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel Pentium II

Operating System (example: Linux 2.0.26 ELF) : Red Hat Linux 6.2 (custom 2.2.16 kernel)

PostgreSQL version (example: PostgreSQL-7.0): PostgreSQL-7.0.2

Compiler used (example: gcc 2.8.0) : Binary RPM distribution for Red Hat

Please enter a FULL description of your problem:
------------------------------------------------
Selects on tables with cidr type primary keys are broken.

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
Behold:

<quote>
test=# \d jobschedule
      Table "jobschedule"
 Attribute |  Type   | Modifier
-----------+---------+----------
 prefix    | cidr    | not null
 interval  | integer |
Index: jobschedule_pkey

test=# select * from jobschedule;
prefix | interval
----------------+----------
132.229.230/24 | 2592000
132.229.231/24 | 2592000
132.229.232/24 | 2592000
132.229.12/24 | 2592000
132.229.50/24 | 2592000
132.229.52/24 | 2592000
132.229.93/24 | 2592000
132.229.95/24 | 2592000
(8 rows)

test=# explain select * from jobschedule where prefix='132.229.230/24';
NOTICE: QUERY PLAN:

Index Scan using jobschedule_pkey on jobschedule (cost=0.00..8.14 rows=10 width=16)

EXPLAIN
test=# select * from jobschedule where prefix='132.229.230/24';
prefix | interval
--------+----------
(0 rows)
</quote>

If I recreate the table without prefix being a primary key, the selects
are planned as sequential scans and magically start working again.

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

Eeeh.. not really. I haven't really had time yet to delve into the
PostgreSQL codebase.... :(

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Lennert Buytenhek (#1)
Re: Selects on tables with cidr type primary keys are broken

Yeah, that's a known bug :-(. The indexing routines for CIDR/INET don't
agree with the comparison operators about sort order. It'll be fixed in
7.1.

regards, tom lane