Hello,
I've encountered a 'strange' behavior when using partitioning and
prepared statements (or SQL in PL/pgSQL procedures) on 8.1.5. Imagine
you have a table partitioned by an ID (say in 1 milion blocks), thus you
have an empty 'parent' table PARENT and child tables CHILD_0, CHILD_1, ...
======================================================================
CREATE TABLE PARENT (
id INTEGER;
);
CREATE TABLE CHILD_0 (
CHECK id BETWEEN (0 AND 999999)
) INHERITS (PARENT);
CREATE RULE insert_child_0 AS ON INSERT TO PARENT WHERE (id BETWEEN (0
AND 999999)) DO INSTEAD INSERT INTO CHILD_0 VALUES (NEW.id);
======================================================================
Then I have a prepared statement
PREPARE tmp AS SELECT max(id) FROM PARENT;
giving for example this execution plan
======================================================================
EXPLAIN EXECUTE tmp;
------------------------------------------------------------------------
Aggregate (cost=73.50..73.51 rows=1 width=4)
-> Append (cost=0.00..62.80 rows=4280 width=4)
-> Seq Scan on parent (cost=0.00..31.40 rows=2140 width=4)
-> Seq Scan on child_0 parent (cost=0.00..31.40 rows=2140 width=4)
======================================================================
So far everything seems ok, but let's create another child table
======================================================================
CREATE TABLE CHILD_1 (
CHECK id BETWEEN (1000000 AND 1999999)
) INHERITS (PARENT);
CREATE RULE insert_child_1 AS ON INSERT TO PARENT WHERE (id BETWEEN
(1000000 AND 1999999)) DO INSTEAD INSERT INTO CHILD_1 VALUES (NEW.id);
======================================================================
and do the execution plan again:
======================================================================
EXPLAIN EXECUTE tmp;
------------------------------------------------------------------------
Aggregate (cost=73.50..73.51 rows=1 width=4)
-> Append (cost=0.00..62.80 rows=4280 width=4)
-> Seq Scan on parent (cost=0.00..31.40 rows=2140 width=4)
-> Seq Scan on child_0 parent (cost=0.00..31.40 rows=2140 width=4)
======================================================================
Ooops, the new child table is missing ;(
I do understand how this happens - at the time the statement was
prepared the child table did not exist so it's not in the execution
plan. But shouldn't it been somehow invalidated or something like that?
Is this a known issue / feature / side effect / drawback? (I have not
tried the 8.2 branch yet.)
With the plain prepared statements the solution is pretty simple - just
deallocate and prepare them again. But SQL in the stored procedures are
prepared statements too, and the only option is to drop / recreate that
function (or use dynamic SQL), as far as I know.
This time I'll use the dynamic SQL as the procedure is used for
maintenance and will be executed say once a day, but with heavily used
procedures doing a lot of operation on this partitioned table that
really is not an option. Is there some other solution?
Tomas
