Sample Header Ad - 728x90

Unable create partition by trigger

1 vote
1 answer
767 views
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?
Asked by Maxiko (131 rep)
Oct 4, 2019, 05:12 AM
Last activity: Feb 8, 2025, 09:04 PM