0

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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.