S
Supabase•4mo ago
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) (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) 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() 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?)
1 Reply
silentworks
silentworks•4mo ago
The options are as you stated, you can use an direct db access postgres library (only in server side code) and use their transaction feature. You can also do this inside of an edge function with a postgres library. There is no way to do this on the client side without calling .delete manually as there is no transaction built into the library. Note that if you use UUIDs you can generate these on the client and make your call to your external service first and if it fails then don't bother with the insert. This is a huge benefit of using UUIDs.

Did you find this page helpful?