BUG #2080: Partitioning does not work with PREPARE

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

BUG #2080: Partitioning does not work with PREPARE

Janko Richter

The following bug has been logged online:

Bug reference:      2080
Logged by:          Janko Richter
Email address:      [hidden email]
PostgreSQL version: 8.1
Operating system:   Linux
Description:        Partitioning does not work with PREPARE
Details:

Partitioning does not work with PREPARE. The following
statements show the effect:

CREATE TABLE master
(
  p_id int2 NOT NULL,
  PRIMARY KEY (p_id)
) WITHOUT OIDS;

CREATE TABLE partition_0
(
  PRIMARY KEY (p_id),
  CONSTRAINT mod CHECK ( p_id  = 0::smallint )
) INHERITS (master)
WITHOUT OIDS;

CREATE TABLE partition_1
(
  PRIMARY KEY (p_id),
  CONSTRAINT mod CHECK ( p_id  = 1::smallint )
) INHERITS (master)
WITHOUT OIDS;

INSERT INTO partition_0 VALUES (0);

INSERT INTO partition_1 VALUES (1);



EXPLAIN SELECT p_id FROM master WHERE p_id=1::smallint ;
                                              QUERY PLAN                    
                         
----------------------------------------------------------------------------
---------------------------
 Result  (cost=0.00..10.65 rows=2 width=2)
   ->  Append  (cost=0.00..10.65 rows=2 width=2)
         ->  Index Scan using master_pkey on master  (cost=0.00..4.82 rows=1
width=2)
               Index Cond: (p_id = 1::smallint)
         ->  Index Scan using partition_1_pkey on partition_1 master
(cost=0.00..5.82 rows=1 width=2)
               Index Cond: (p_id = 1::smallint)
(6 rows)



PREPARE foo(int2) AS SELECT p_id FROM master WHERE p_id=$1::smallint;
EXPLAIN EXECUTE foo(1);
                                              QUERY PLAN
----------------------------------------------------------------------------
---------------------------
 Result  (cost=0.00..16.47 rows=3 width=2)
   ->  Append  (cost=0.00..16.47 rows=3 width=2)
         ->  Index Scan using master_pkey on master  (cost=0.00..4.82 rows=1
width=2)
               Index Cond: (p_id = $1)
         ->  Index Scan using partition_0_pkey on partition_0 master
(cost=0.00..5.82 rows=1 width=2)
               Index Cond: (p_id = $1)
         ->  Index Scan using partition_1_pkey on partition_1 master
(cost=0.00..5.82 rows=1 width=2)
               Index Cond: (p_id = $1)
(8 rows)


Regards,
  Janko Richter

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: BUG #2080: Partitioning does not work with PREPARE

Tom Lane-2
"Janko Richter" <[hidden email]> writes:
> Partitioning does not work with PREPARE.

Well, no.  How would you expect the prepared plan to know which
partition to look in, for a key value it doesn't have yet?

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster