Utilize deferred constraints in bulk updates GUI?

Howdy! I have a bulk update that I want to do that references a column that has a deferred unique constraint on it - meaning it only checks the uniqueness at the end of the transaction.
The postgres description looks like:

"queue_slot_rank_key" UNIQUE CONSTRAINT, btree ("rank") DEFERRABLE

It's usable by doing something like:

BEGIN;
SET CONSTRAINTS "queue_slot_rank_key" DEFERRED;
UPDATE queue_slot SET rank = 2 WHERE id = 1;
UPDATE queue_slot SET rank = 1 WHERE id = 2;
COMMIT;

(assume the "rank" column matched the "id" column before)
This allows me to modify a custom ordering of the rows while maintaining uniqueness on the "rank" column. BTW it also works to do SET CONSTRAINTS ALL DEFERRED. I can't get the bulk update GUI to work. Is there a way to add transaction directives other than doing a custom SQL query?

If not, is there a safe way to do bulk updates in the sql page? There would be a variable number of them, so I couldn't just format a set number of single value update commands.

When I tried to do it in the raw SQL mode, I was thinking something like this:

BEGIN;
SET CONSTRAINTS ALL DEFERRED;

UPDATE queue_slot
SET rank = updated.rank
FROM (
SELECT 
  UNNEST({{ [ list of ids... ] }}) AS "id",
  UNNEST({{ [ list of updated rank... ] }}) AS "rank"
) as updated
WHERE queue_slot.id = updated.id;

COMMIT;

But I get "cannot insert multiple commands into a prepared statement" - so then I thought to try to pass them into a prepared query. So on psql I defined:

prepare reorder (text[], int[]) as
update queue_slot set rank = updated.rank from (select unnest($1) as "id", unnest($2) as "rank") as updated where queue_slot.id = updated.id;

and in retool changed the SQL to be:

EXECUTE reorder({{ [ list of ids...] }}, {{ [ list of rank... ] }});

But that errors with bind message supplies 2 parameters, but prepared statement "" requires 0.

So I'm guessing it isn't supported to do this, but I'd like it to be possible. If it is possible, can someone point me in the right direction? Thanks!

I have gotten it to work by changing the column config to DEFERRABLE INITIALLY DEFERRED so it happens without a per-transaction SET CONSTRAINTS. So I guess this isn't needed for my case anymore, but it'd be nice to know if this sort of thing could be supported in a way I didn't figure out!

1 Like