IO in constraint trigger - Mailing list pgsql-general

Hi.

 

We're planning to move away from Blobs (stored as OID) and use files with path/URI stored in DB. For this to be reliable we're planning to do rename() (which is atomic) in a constraint trigger, which triggers on commit. The plan is to write stuff to a file (with at tmp-filename) in the application and to pass the absolute file-name down to the DB so the trigger can rename it ON COMMIT. This way we can use a naming-convention to figure out what files are OK and which are not (which can be deleted due to rolled back transactions).

 

I have found this extension: https://github.com/csimsek/pgsql-fio/

but it doesn't seem to support renaming.

 

Any advice on how to proceed?

 

Thanks.