Automatic partition creation?
От | Sbob |
---|---|
Тема | Automatic partition creation? |
Дата | |
Msg-id | 001805b4-c039-49b1-344b-9c52016c6f3b@quadratum-braccas.com обсуждение исходный текст |
Ответы |
Re: Automatic partition creation?
Re: Automatic partition creation? |
Список | pgsql-admin |
All; In the past I have setup inheritance based partition using something like the function snippet below, so the creation of new partitions is automatic Is it possible to auto create new partitions as needed with declarative partitioning as well? BEGIN CODE SNIPPET customer_name := NEW.customer_name; year := date_part('year', NEW.updated_at); month := date_part('month', NEW.updated_at); day := date_part('day', NEW.updated_at); current__date := date_trunc('day', NEW.updated_at); next__date := date_trunc('day', NEW.updated_at) + interval '1 day'; namespace := 'partitions_cst_ymd'; suffix := 'c_' || customer_name || '_' || year || '_' || month || '_' || day; clean_table_name := 'process_states_' || suffix; table_name := namespace || '.' || clean_table_name; IF NOT EXISTS (SELECT relname FROM pg_class WHERE relname = clean_table_name) THEN index_name_on_pkey := 'idx_' || suffix || '_on_pkey'; index_name_on_process_native_id := 'idx_' || suffix || '_on_process_native_id'; EXECUTE format('CREATE TABLE IF NOT EXISTS %s ( CHECK ( customer_name = ''%s'' AND updated_at >= DATE ''%s'' AND updated_at < DATE ''%s'') ) INHERITS (public.process_states)', table_name, customer_name, current__date, next__date); EXECUTE format('CREATE INDEX %s ON %s %s', index_name_on_pkey, table_name, '(customer_name, process_native_id)'); EXECUTE format('CREATE INDEX %s ON %s %s', index_name_on_process_native_id, table_name, '(process_native_id)'); END IF; EXECUTE format('INSERT INTO %s SELECT $1.*', table_name) USING NEW; set client_min_messages to NOTICE; RETURN NULL; END CODE SNIPPET
В списке pgsql-admin по дате отправления: