Возникает ошибка в импорте данных в pgsql в скрипте ruby
Есть такая таблица
CREATE TABLE public.partitioned_purchases (
id bigint NOT NULL,
project character varying NOT NULL,
title character varying NOT NULL,
purchased_at timestamp without time zone NOT NULL,
quantity integer NOT NULL,
price double precision NOT NULL,
sum double precision NOT NULL,
auto_category character varying,
manual_category character varying,
link_item_id character varying,
item_id character varying,
age integer,
city character varying,
shop character varying,
address character varying,
user_id character varying,
user_date_of_birth date,
user_gender character varying,
user_phone character varying,
user_email character varying,
user_city character varying,
user_vk_uid character varying,
user_ok_uid character varying,
user_fb_uid character varying,
receipt_id character varying NOT NULL,
receipt_total double precision NOT NULL,
receipt_fn character varying NOT NULL,
receipt_fd character varying NOT NULL,
receipt_fpd character varying NOT NULL,
receipt_inn character varying,
receipt_qr_string character varying,
created_at timestamp without time zone NOT NULL,
updated_at timestamp without time zone NOT NULL,
promotion_title character varying,
receipt_created_at timestamp without time zone,
cashback_currency public.citext,
cashback_value integer
) PARTITION BY RANGE (receipt_created_at);
-- CREATE TABLE partitioned_purchases_y2022m03 PARTITION OF partitioned_purchases
-- FOR VALUES FROM ('2022-03-01') TO ('2022-04-01');
-- CREATE TABLE partitioned_purchases_y2022m04 PARTITION OF partitioned_purchases
-- FOR VALUES FROM ('2022-04-01') TO ('2022-05-01');
CREATE SEQUENCE public.partitioned_purchases_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER SEQUENCE public.partitioned_purchases_id_seq OWNED BY public.partitioned_purchases.id;
ALTER TABLE public.partitioned_purchases ADD CONSTRAINT partitioned_purchases_pkey PRIMARY KEY (id, receipt_created_at);
ALTER TABLE ONLY public.partitioned_purchases ALTER COLUMN id SET DEFAULT nextval('public.partitioned_purchases_id_seq'::regclass);
CREATE INDEX index_partitioned_purchases_on_receipt_created_at ON public.partitioned_purchases USING btree (receipt_created_at);
CREATE INDEX index_partitioned_purchases_on_created_at ON public.partitioned_purchases USING btree (created_at);
CREATE INDEX index_partitioned_purchases_on_link_item_id ON public.partitioned_purchases USING btree (link_item_id);
CREATE INDEX index_partitioned_purchases_on_manual_category ON public.partitioned_purchases USING btree (manual_category);
CREATE INDEX index_partitioned_purchases_on_manual_category_and_receipt_id ON public.partitioned_purchases USING btree (manual_category, receipt_id);
CREATE INDEX index_partitioned_purchases_on_project ON public.partitioned_purchases USING btree (project);
CREATE UNIQUE INDEX index_partitioned_purchases_on_project_and_link_item_id ON public.partitioned_purchases USING btree (project, link_item_id, receipt_created_at);
CREATE INDEX index_partitioned_purchases_on_receipt_id ON public.partitioned_purchases USING btree (receipt_id);
CREATE INDEX index_partitioned_purchases_on_updated_at ON public.partitioned_purchases USING btree (updated_at);
и есть такой код импорта
require 'csv'
class ImportPurchases
def run(file_path = nil)
values = []
batch_size = 1_000
batch_number = 0
start = Time.current.to_i
file_path = Rails.root.join('purchase_data', 'purchases.csv') if file_path.blank?
headers = CSV.open(file_path, 'r', &:first)
columns = PartitionedPurchase.column_names - %w[id created_at updated_at]
conflict_target = %w[project link_item_id]
key_update = { conflict_target: conflict_target, columns: columns }
CSV.foreach(file_path).each_with_index do |row, idx|
next if idx.zero?
values.push(row)
next unless values.count % batch_size == 0
batch_number += 1
PartitionedPurchase.import(headers, values, validate: true, on_duplicate_key_update: key_update)
values = []
logger.info("Batch number #{batch_number} | seconds spended #{Time.current.to_i - start}")
end
return if values.blank?
PartitionedPurchase.import(headers, values, validate: true, on_duplicate_key_update: key_update)
end
def logger
@logger ||= Logger.new('log/import_purchases.log')
end
end
эти воркеры выполняются в saidkiq
и при импорте возникает такая ошибка
PG::CheckViolation: ERROR: no partition of relation "partitioned_purchases" found for row
DETAIL: Partition key of the failing row contains (receipt_created_at) = (2023-05-03 17:11:34).
еще такая раньше выскакивала, я немного индексы подправил
PG::InvalidColumnReference: ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification : INSERT INTO "partitioned_purchases"
Хотел спросить совета у экспертов по pg, в чем может быть дело?
Источник: Stack Overflow на русском