1) I have table
CREATE TABLE tor
(
events_date timestamp without time zone NOT NULL,
message text
CONSTRAINT tor_pkey PRIMARY KEY (events_date, message)
)PARTITION BY RANGE (events_date);
2) There is a default partition where the data gets, if there is no partition where you can put it
CREATE TABLE tor_part_default partition OF tor default;
3) There is a trigger on an INSERT to tor_part_default:
CREATE OR REPLACE FUNCTION create_next_part()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
partname VARCHAR(11);
startdate VARCHAR(10);
enddate VARCHAR(10);
BEGIN
partname = 'tor_'||to_char(NEW.events_date, 'YYYYMM');
startdate = to_char(NEW.events_date, 'YYYY-MM-01');
enddate = to_char(TO_DATE(startdate,'YYYY-MM-DD') + interval '1 month','YYYY-MM-DD');
RAISE NOTICE ' Start date: %',startdate;
RAISE NOTICE ' End date: %',enddate;
IF NOT EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = partname)
THEN
RAISE NOTICE 'Partition created: %',partname;
EXECUTE 'CREATE TABLE '||partname||' partition OF tor FOR VALUES FROM ('''||startdate||''') TO ('''||enddate||''')';
EXECUTE 'INSERT INTO '||partname||' VALUES(NEW.*)';
END IF;
RETURN NEW;
END;
$BODY$;
CREATE TRIGGER tor_create_next_part
BEFORE INSERT
ON tor_part_default
FOR EACH ROW
EXECUTE PROCEDURE create_next_part();
When I try to insert data, as a result I get an error:
ERROR: cannot CREATE TABLE .. PARTITION OF "tor" because it is being used by active queries in this session
CONTEXT: SQL statement "CREATE TABLE tor_201606 partition OF tor FOR VALUES FROM ('2016-06-01') TO ('2016-07-01')"
PL/pgSQL function create_next_part() line 16 at EXECUTE
What am I doing wrong or is there another way to create partitions automatically?
