RLS given no available transactions - how to roll back?

I'm trying to wrap an operation (outside of Supabase) that might sometimes fail.
The (pseudo)code looks something like the following:

const { data, error } = await supabase.from("table").insert({...myData}).select("autogeneratedId");
const id = data?.autogeneratedId; // This is in my case actually generated by an rpc call, but regardless, a value comes from a select after insertion.
// Insertion and Selection are allowed by RLS, but not other operations.

const success = doSomeStuffThatMightFailUnrelatedToSupabase(id);
if (!success) {
  // need to delete the rows that were inserted... hmm
  transaction.rollback() would be nice...
}

const { data, error } = await supabase.from("table").insert({...myData}).select("autogeneratedId");
const id = data?.autogeneratedId; // This is in my case actually generated by an rpc call, but regardless, a value comes from a select after insertion.
// Insertion and Selection are allowed by RLS, but not other operations.

const success = doSomeStuffThatMightFailUnrelatedToSupabase(id);
if (!success) {
  // need to delete the rows that were inserted... hmm
  transaction.rollback() would be nice...
}

It seems like I need to allow the user to delete at least their own inserted rows, if I want to allow a

from('table').delete(id)

from('table').delete(id)

(allowing for correct syntax in the deletion).
I'd prefer not to do this.

https://github.com/orgs/supabase/discussions/526 seems to suggest rpc is a solution for lacking transaction support, but I don't see how to use one here (what would such a function look like? Do I need to use some sort of external function from the db to try and internalize the

doSomeStuffThatMightFail(id)

doSomeStuffThatMightFail(id)

call? (What would this be?)
Are there any other options? (I suppose direct db access and a postgresql library?)
Is there some creative use of a temporary table that one would suggest here? Or perhaps querying the id-generating sequence via RPC and

nextval()

nextval()

but not actually inserting any rows (looks like this will prevent a race condition via the nextval function being atomic), then later (after success) insert the values (can I insert values to a serial field with a value older than the current sequence position? Only the uniqueness of the primary key constraint is required, right?)

Supabase•7mo ago•

1 reply

chrisb2244

RLS given no available transactions - how to roll back?

I'm trying to wrap an operation (outside of Supabase) that might sometimes fail.
The (pseudo)code looks something like the following:

const { data, error } = await supabase.from("table").insert({...myData}).select("autogeneratedId");
const id = data?.autogeneratedId; // This is in my case actually generated by an rpc call, but regardless, a value comes from a select after insertion.
// Insertion and Selection are allowed by RLS, but not other operations.

const success = doSomeStuffThatMightFailUnrelatedToSupabase(id);
if (!success) {
  // need to delete the rows that were inserted... hmm
  transaction.rollback() would be nice...
}

const { data, error } = await supabase.from("table").insert({...myData}).select("autogeneratedId");
const id = data?.autogeneratedId; // This is in my case actually generated by an rpc call, but regardless, a value comes from a select after insertion.
// Insertion and Selection are allowed by RLS, but not other operations.

const success = doSomeStuffThatMightFailUnrelatedToSupabase(id);
if (!success) {
  // need to delete the rows that were inserted... hmm
  transaction.rollback() would be nice...
}

It seems like I need to allow the user to delete at least their own inserted rows, if I want to allow a

from('table').delete(id)

from('table').delete(id)

doSomeStuffThatMightFail(id)

doSomeStuffThatMightFail(id)

nextval()

nextval()

RLS given no available transactions - how to roll back?

RLS given no available transactions - how to roll back?

Similar Threads

Similar Threads

Similar Threads