Trigger on insert, update and delete of storage objects

[Monu18765]

I could not find way to add trigger on storage objects.

[saltcod]

hey @Monu18765 — what have you tried? What are you trying to do?

(Going to convert this to a Discussion)

[Monu18765]

AIM

I am trying to compress images in the background.

APROACH

I have added trigger on storage.objects and check if new object is added or updated then add a row in custom media_record table. Once a new record is added in media_record it trigger an edge function.

Problem

Trigger on storage.object is inconsistent. Multiple Triggers and "After Insert" getting trigger on Deleting object.

So far I am able to work with make shift solution. But since this is not an official solution then sudden change in how storage work internally can break my solution.

[GaryAustin1]

I've now tested the new storage server that is on all instances now.

I see using a trigger with tg_op and new logged out:

2 inserts for upload. The first has metadata = null the 2nd has metadata. The 2nd insert I believe to be an insert with ON CONFLICT (name,bucket_id) so is really an update but shows as an insert trigger. WARNING: the object id changes between the two. This would potentially break existing code that did an insert trigger both because there are two inserts and the object id is different on each and seems to be finally set to the 2nd insert value.

3 updates for update (!!). Did not investigate details.

1 delete for a remove.

[Monu18765]

Yeah I got thing working using metadata and metadata --> etag.

[hugotox]

@Monu18765 how are you calling the edge function from a trigger? I want to try that approach but could not find an example

[Monu18765]

Hi @hugotox I actually have a media_records Table. When a new object is added to the storage bucket. I add a new row in this table and used webhooks to trigger edge functions.

You can also use the pg_net extension to trigger edge functions. I use it to trigger Slack webhooks. I guess this one becomes a bit messy especially if you have a different environment like development. staging and production. So, the first option is advised.

I hope this helps, let me know if you need further help.

[bitnom]

I've now tested the new storage server that is on all instances now.

I see using a trigger with tg_op and new logged out:

2 inserts for upload. The first has metadata = null the 2nd has metadata. The 2nd insert I believe to be an insert with ON CONFLICT (name,bucket_id) so is really an update but shows as an insert trigger. WARNING: the object id changes between the two. This would potentially break existing code that did an insert trigger both because there are two inserts and the object id is different on each and seems to be finally set to the 2nd insert value.

3 updates for update (!!). Did not investigate details.

1 delete for a remove.

I'm not experiencing this behavior at all. I see 1 insert when a file is uploaded. Logging via:

CREATE OR REPLACE FUNCTION public.log_object_changes()
RETURNS TRIGGER AS $$
BEGIN

  INSERT INTO public.debug_logs (function_name, message, request_id)
  VALUES (TG_NAME, -- Name of the trigger
          'Operation: ' || TG_OP || '; ID: ' || NEW.id || '; Bucket ID: ' || COALESCE(NEW.bucket_id, '') || '; Name: ' || COALESCE(NEW.name, ''), -- Message with operation details
          NEW.id); -- Assuming the request_id is the same as the object's ID for logging purposes

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER log_inserts_on_objects
AFTER INSERT ON storage.objects
FOR EACH ROW
EXECUTE FUNCTION public.log_object_changes();

CREATE TRIGGER log_updates_on_objects
AFTER UPDATE ON storage.objects
FOR EACH ROW
EXECUTE FUNCTION public.log_object_changes();

[bitnom]

correction: my trigger doesn't log the second one for some reason.

[GaryAustin1]

Not sure. I have a storage.objects trigger and it logs to the Postgres logs the OP and data.

First one:

Second one:

You can see they are to the same file and the 2nd one is setting the metadata after the s3 call is complete.

[cohlar]

+1
I think this should be labeled as a Feature Request, it'd be nice to have a documented (i.e. "official" and reliable) approach for triggers on storage objects operations.

[MentalGear]

Second that! Official guide would be highly appreciated, especially in regard of the volume of new discussions around this.

[varun508]

Wouldn't it be wise for now to only trigger if the insert operation has metadata field?