Search Postgresql Archives

trouble inserting into new partitions of partitioned tables

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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).



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux