Skip site navigation (1) Skip section navigation (2)

Selects on tables with cidr type primary keys are broken

From: Lennert Buytenhek <buytenh(at)gnu(dot)org>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Selects on tables with cidr type primary keys are broken
Date: 2000-10-15 14:56:34
Message-ID: Pine.LNX.4.21.0010151654310.2130-100000@mara.math.leidenuniv.nl (view raw or flat)
Thread:
Lists: pgsql-bugs

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


Your name		:	Lennert Buytenhek
Your email address	:	buytenh(at)gnu(dot)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.... :(


Responses

pgsql-bugs by date

Next:From: Palle GirgensohnDate: 2000-10-15 16:25:26
Subject: reproducable command sequence to get "mdopen: Couldn't open..."
Previous:From: Philip WarnerDate: 2000-10-15 08:25:40
Subject: Re: PostgreSQL BugTool Submission

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group