I'm having some trouble inserting into newly-created partitions
of a partitioned table scheme.
My main question is whether the following constitutes a bug, or
(more likely) a gap in my understanding of what should
happen? Production problem observed on 8.1.3, this test case was
on 8.2RC1. (I posted something along these lines back in April
2006, not sure I explained it particularly well at that time).
reporting=> select version() ;
version
-----------------------------------------------------------------------------------------------------------
PostgreSQL 8.2rc1 on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.0.0 20050519 (Red Hat 4.0.0-8)
(1 row)
Real scenario involves partitioning based on a timestamp, but the
simplified scenario is:
=========================================
1. basic partition table setup, e.g.:
CREATE SCHEMA mytest ;
SET search_path TO mytest ;
-- ...base table, no inserts allowed
CREATE TABLE silly (
bcid varchar(16) NOT NULL
,state char(2) NOT NULL
,some_value int NOT NULL
,created timestamp without time zone NOT NULL DEFAULT
CURRENT_TIMESTAMP
) ;
CREATE OR REPLACE FUNCTION reject_silly_inserts()
RETURNS trigger AS $$
DECLARE
BEGIN
RAISE EXCEPTION 'inserts only allowed into silly partition tables
(state was %)', NEW.state ;
RETURN NEW ;
END ;
$$ LANGUAGE plpgsql ;
CREATE TRIGGER silly_insblock BEFORE INSERT
ON silly
FOR EACH ROW
EXECUTE PROCEDURE reject_silly_inserts() ;
-- ...stored proc for doing the inserts
CREATE OR REPLACE FUNCTION silly_insert(a_bcid varchar(16), a_state
char(2), a_value int) RETURNS void AS $$
DECLARE
BEGIN
INSERT INTO silly(bcid,state,some_value)
VALUES(a_bcid, a_state, a_value) ;
END ;
$$ LANGUAGE plpgsql ;
-- ...partition for new jersey
CREATE TABLE silly_nj (
CHECK(state='nj')
) INHERITS (silly) ;
CREATE RULE silly_ins_nj AS
ON INSERT TO silly WHERE (state='nj')
DO INSTEAD INSERT INTO silly_nj VALUES(NEW.bcid, NEW.state,
NEW.some_value, NEW.created) ;
=========================================
2. A long-running process connects to Postgres and calls the stored
procedure to insert values into "silly", e.g.:
pg> set search_path to mytest ;
pg> PREPARE silly_prep (varchar(16), char(2), int) AS
SELECT silly_insert($1,$2,$3) ;
pg> EXECUTE silly_prep('cccc','nj',9999) ;
=========================================
3. so far, so good. problem arises when a partition is added for
another state while 2 is connected and running, for example from
another Postgres connection:
pg> SET search_path TO mytest ;
pg> CREATE TABLE silly_va (
CHECK(state='va')
) INHERITS (silly) ;
pg> CREATE RULE silly_ins_va AS
ON INSERT TO silly WHERE (state='va')
DO INSTEAD INSERT INTO silly_va VALUES(NEW.bcid, NEW.state,
NEW.some_value, NEW.created) ;
=========================================
4. now if the already-connected process in (2) tries to insert
a Virgina record it gets rejected as if the partition is not
there:
pg> EXECUTE silly_prep('cccc','va',999) ;
ERROR: inserts only allowed into silly partition tables (state was va)
=========================================
5. I thought DEALLOCATEing and re-preparing the stmt might work, but
no:
-- ...still in existing connection from (2)
pg> DEALLOCATE silly_prep ;
pg> PREPARE silly_prep (varchar(16), char(2), int) AS
SELECT silly_insert($1,$2,$3) ;
pg> EXECUTE silly_prep('cccc','va',999) ;
ERROR: inserts only allowed into silly partition tables (state was va)
=========================================
6. thinking it might be the prepared stmt causing the problem I
tried a direct call to the stored proc, to no avail:
pg> SELECT silly_insert('cccc','va',999) ;
ERROR: inserts only allowed into silly partition tables (state was va)
=========================================
7. a direct insert does work, however:
pg> INSERT INTO silly(bcid,state,some_value) VALUES('asdf','ny',8888) ;
INSERT 0 0
8. if the process from (2) disconnects and reconnects everything
works as expected (i.e. it can insert Virgina rows).
