Postgres Pro
Downloads
Home   >   mailing lists

Thread: IO in constraint trigger

IO in constraint trigger

From
Andreas Joseph Krogh
Date:
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.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Attachment

Re: IO in constraint trigger

From
Tom Lane
Date:
Andreas Joseph Krogh <andreas@visena.com> writes:
> 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.

That seems broken on its face to me.  There is *no* certainty that the
transaction will commit just because it's gotten to the point of running
commit triggers.  You will end up with files that are not in sync with
the database.

> 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).

Safer to design it like VACUUM, ie periodically remove old files that
don't have database entries.

            regards, tom lane