Thread

1. Selects on tables with cidr type primary keys are broken

   Lennert Buytenhek <buytenh@gnu.org> — 2000-10-15T14:56:34Z

   
   ============================================================================
                           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.... :(
   
   
   

2. Re: Selects on tables with cidr type primary keys are broken

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-10-15T16:30:19Z

   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