I'm testing workflow but I get this in the log:
"remaining connection slots are reserved for non-replication superuser connections"
and "sorry, too many clients already".
in the retool db I get the same.
1 Like
Hi BobandBill!
That error message usually pops up when inserting/updating multiple records with a loop, is that your case as well?
If so solution would be to bulk insert records with 1 call rather than going through a loop.
It’s an upsert in a loop. There’s no bulk insert-or-update I think.
But what are the current limits for loops and queries?
So if you are using upsert in a loop it might be best that you switch to bulk upsert instead.
As for the limits worth reaching out to Retool support team.
1 Like
What are the max amount of connections?
I've had a normal upsert like this. With a composite primary key. Now I wonder how to change this a Bulk upsert. I also want to add or deduct the quantity column and not just replace it. Any chance you know where I can find an example?
INSERT INTO
STOCK (product_id, variant_id, location, quantity)
VALUES
(
{{ product_id_arg }},
{{ variant_id_arg }},
{{ location_arg }},
{{ quantity_arg }}
) ON CONFLICT (product_id, variant_id, location)
DO
UPDATE
SET
quantity = stock.quantity + excluded.quantity;
I found a solution with chat GPT:
WITH upsert_values AS (
SELECT *
FROM (
SELECT
(entry->>'product_id')::bigint AS product_id,
(entry->>'variant_id')::bigint AS variant_id,
(entry->>'register') AS location,
(entry->>'stock_correction')::integer AS quantity
FROM jsonb_array_elements({{stock_entries.data}}) AS entry
) AS t (product_id, variant_id, location, quantity)
)
INSERT INTO STOCK (product_id, variant_id, location, quantity)
SELECT uv.product_id, uv.variant_id, uv.location, uv.quantity
FROM upsert_values AS uv
LEFT JOIN STOCK ON (STOCK.product_id = uv.product_id AND STOCK.variant_id = uv.variant_id AND STOCK.location = uv.location)
ON CONFLICT (product_id, variant_id, location)
DO UPDATE SET
quantity = STOCK.quantity + EXCLUDED.quantity;
2 Likes
Same issue. Would have rather kept the Loop one.
Hey @Georges_El_Khoury! How many rows do you need to upsert? And is there a particular reason you prefer the loop of doing a bulk upsert?
Hey So I figured the solution as the bulk upsert did'nt work for my case -
Basically change the loop module from gui to code and add await there -
Its happening because before the promise finishes one query it starts another thus creating maybe 10/100/1000(depends on the data) connections on the db at once.
2 Likes