go ahead... be a heretic | |
PerlMonks |
Atomic use of unlink() and SQL DELETE? How?by robins (Acolyte) |
on Feb 09, 2006 at 11:48 UTC ( [id://529061]=perlquestion: print w/replies, xml ) | Need Help?? |
robins has asked for the wisdom of the Perl Monks concerning the following question:
Okay, this is the scenario: I have a database (DBD::Pg) with metadata about some files. The files themselves are stored in a folder with the primary key from the database as the filename. I'm trying to find the correct way to delete the record in the database and then deleting the file from the filesystem (with full atomic exception handling). But be aware that a DELETE is cascading and will either block or cascade depending on the foreign key references. If one of these commands fail I must make sure both the database and the file are rolled back to the previous state (that is, NOT deleted). But how can I simulate an unlink without actually deleting the file? And what to do if the storage filesystem (SAN) or database server goes haywire in the middle of the transaction and drops out? The code below works at the moment, but if you setup a bunch of objects, and iterate through them, you would want this entire batch job to be one unit of work. As far as I can tell, this function isn't good enough in that scenario. The PF->transaction(1) call starts a new transaction (and disables DBI AutoCommit). PF->transaction(0) tries to commit the current transaction, and issues a rollback if any errors happened. RaiseError is on, and PrintEror is off. The PF->sql() call is just a wrapper to call prepare and execute in one go with automatic statement caching. The entire call to this function is wrapped in eval, using a generic error handler which calls rollback if RaiseError is triggered. If anyone has a completely different approach to this problem, please enlighten me with your wisdom. Are there any CPAN modules available that can help in some way with this kind of problem? This is the code I currently use:
Back to
Seekers of Perl Wisdom
|
|