Re: IO in constraint trigger

Andreas Joseph Krogh <andreas(at)visena(dot)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