Too many clients and remaining connection slots

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.
image

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.

1 Like