Atomic triggers on storage
Hey I was wondering if triggers on storage objects are atomic. So let's say I have a trigger that creates a row in the files table when someone uploads a file. But inserting the trigger raises an exception. Will this result in a error for the upload and delete the uploaded file so there are no ghost files?
18 Replies
There are 2 inserts to storage.objects currently for upload. The first is to test insert RLS. I assume if that fails then the file would not be processed further. That insert is backed out either way.
There is a 2nd insert after the file is stored to s3 to add all the metadata.
But these are not documented and could change in the future.
Ah I see, won't this trigger twice then?
Some context: I want to create a file manager which consists of real files in s3 and virtual files such as lexical JSON or external links. These are not on s3 but just a row with data in the data column. One unified files table seems the most robust way to do this. I figured if someone uploads to that bucket I will automatically create a insert in the files table via a trigger. What do you think of this way. Couldn't really figure out s better way
Seems better then searching the storage object and for every virtual file type a seperate table which won't scale well
There will be two inserts for a successful file.
You would need to look at the data to know it is the 2nd one.
And the risk is that insert process could change and you would not be notified, but some have done it.
I see, thanks for your information again! I think I should try it, and maybe have a cron job monitoring if the files and storage object tables are consistent. Do you think this is the best way to achieve my goal
Or would you say just write a RPC that checks both storage object and virtual files table instead of having s unified table
I personally would do the trigger approach, but I'm a one man team and would remember when storage breaks a year or two from now that I have a trigger on it to go check.
You can add a test function and use
You can add a test function and use
raise log 'new = %',new; to observe what you have to work with.
Otherwise I would do the file upload from the API and then update my table with possibly a retry on the DB operation. Chances of a DB operation failing are much less than storage error. Worst case then it would look like the file did not upload at all to whatever step is using the table.Yeah that seems like a good idea thanks! Can also write a unit test for this I guess. Would you insert in the files table on the first insert so it can block the upload. Or on the second insert, so you won't reference to a file that failed to upload
I would do the 2nd insert as then you know the file is on s3. I would think they would back out with an error on the 2nd insert BUT I don't remember from when I looked at their source a year or so ago.
Are you just worried about blocking on error or do you need to block for other reasons?
I guess you have both triggers happening anyway so could do different things on each.
Honestly just trying to ensure integrity, making sure there are no ghost objects or ghost rows in files. But using both triggers seems like a good idea
Thanks a lot!
One more question, normally in s3 there is no native way to do folders. But i noticed the supabase dashboard can make folders
How does it do this, i cant find a hidden .blank file or something
Does supabase have a native way i can use?
Empty folders*
There are no folders in the Storage API, just part of the path. The UI has a concept of a separate folder by creating a placeholder file that it knows to convert to a folder for display.
Oh yeah you are right. I was looking for .blank but they call it .emptyFolderPlaceholder
Thanks!
Also one more thing, in S3 if you want to move a file you normally just copy and delete the old one. Cant i just safely change the name column in storage.objects?
No. That will orphan the file in s3.
And should I also change path_tokens
Ah i see okay thanks
But deleting and creating rows in objects, will that create orphans?
They don't convert it to some other name or "pointer". It is stored on s3 by the pathname.
Yes.
Don't modify storage.objects by hand at all.
Well except the meta field.
Alright thanks!
Hey Gary, the docs specify that you shouldnt delete with a sql query as you told me but cant find a RPC function to delete objects from RPC. does this even exist or must it always be done via api?
You must delete from the Storage REST API.
I've used the pg_net extension to do this in the past.
lol you must really hate edge functions haha
Thanks again for the tip
Currently got 2 edge functions, one to process pending notifications by sending to FCM every minute (whats pending at least) and one to create users via the rest api. Perhaps I should make the create user function also a RPC. Supabase cold boots really suck
This is old and before there was a user metadata column in storage, but shows how to use pg_net and http to call storage. Also uses Vault to store service_role key.
https://github.com/GaryAustin1/supa-file-helper
Ah nice, thanks.