IO in constraint trigger
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
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general