Table def:
x
CREATE SEQUENCE IF NOT EXISTS lazy_product_stock_id_seq;
CREATE TABLE "public"."lazy_product_stock" (
"id" int4 NOT NULL DEFAULT nextval('lazy_product_stock_id_seq'::regclass),
"product_id" int4,
"hold" int4 DEFAULT 0,
"quantity" int4 DEFAULT 0,
"warehouse_id" int4,
PRIMARY KEY ("id")
);
CREATE UNIQUE INDEX lazy_product_stock_pkey ON public.lazy_product_stock USING btree (id)
CREATE INDEX lazy_product_stock_product_id_idx ON public.lazy_product_stock USING btree (product_id)
CREATE INDEX lazy_product_stock_warehouse_id_idx ON public.lazy_product_stock USING btree (warehouse_id)
CREATE UNIQUE INDEX CONCURRENTLY "lazy_product_stock_comb_idx2" ON "public"."lazy_product_stock" USING BTREE ("product_id","warehouse_id");
I have a function that inserts new rows into the database:
CREATE OR REPLACE FUNCTION sp_lazystock_i_f(_product_id int4, site_id int4) RETURNS VOID AS $$
declare
warehouse record;
BEGIN
FOR warehouse IN select id from warehouse where siteid = site_id LOOP
insert into lazy_product_stock (product_id, warehouse_id) VALUES (_product_id, warehouse.id) ON CONFLICT (product_id,warehouse_id) DO NOTHING;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;
It fails with
duplicate key value violates unique constraint "lazy_product_stock_comb_idx2"
the index
CREATE UNIQUE INDEX CONCURRENTLY "lazy_product_stock_comb_idx2" ON "public"."lazy_product_stock" USING BTREE ("product_id","warehouse_id");
Although running the relevant insert on its own with duplicate values it has no problem with it.
insert into lazy_product_stock (product_id, warehouse_id) VALUES (123, 1234) ON CONFLICT (product_id,warehouse_id) DO NOTHING;
Query 1 OK: INSERT 0 0, 0 rows affected
I can’t understand what seems to be the the difference between the function and the single statement?
This also works without a problem:
do $$
declare
warehouse record;
begin
FOR warehouse IN select id from warehouse where siteid = 123 LOOP
insert into lazy_product_stock (product_id, warehouse_id) VALUES (12345, warehouse.id) ON CONFLICT (product_id,warehouse_id) DO NOTHING;
END LOOP;
end; $$ LANGUAGE plpgsql;
same error with
insert into lazy_product_stock (product_id, warehouse_id)
select _product_id, warehouse.id
from warehouse where siteid = site_id
on conflict (product_id, warehouse_id) do nothing;
I am using Postgresql 12.3
Advertisement
Answer
Seems like I had 2 such functions trigerring on the same logic which caused the problem. Thank you all for help.