[Pdx-pm] DBI/DBD::Pg SELECT ... FOR UPDATE
Roderick A. Anderson
raanders at acm.org
Tue Jul 17 14:58:29 PDT 2007
The Cheetah book and searches using Google have not provided an answer.
Well at least one that I understand or can use. The one decent
reference is for DBIx::Class.
I'm writing a script that will be run by a cron job.
It queries a Pg table for records. The data from those records is used
to query and, when ready, access specific data on a remote system (using
LWP).
If the data is "Ready" then do another query and get the meta data for
it and then fetch the data back and process it one of several ways.
The problem comes when the data is not "Ready", the meta data query
fails, or some other situation comes into play and it takes longer than
anticipated as in there are a a lot "Ready." I'd like this script to
continue it's processing for however long but need to prevent the next
cron call of the script from trying to process the same data. I could
check if the script is running already but I've had less than stellar
luck with doing it this way and ... there might be new records in the
table to process.
So finally to the question.
How does/ or does DBI handle row level locks that Postgresql (and I
think Oracle) provides. Do they apply to the database handle ($dbh)?
So if,
###
my $sth = $dbh->prepare(q{SELECT the, info FROM thetable WHERE the = 'R'
FOR UPDATE });
my $uth = $dbh->prepare(q{UPDATE thetable SET the = ? WHERE info = ?}):
$sth->execute();
while(( $the, $info ) = $sth->fetchrow_array ) {
# lots of stuff to do
if ( $test_results ) {
$uth->execute( $the, $inf );
# Will this UPDATE work
# while preventing the next run of the script from getting to the
# currently selected data?
}
}
And if not is it possible? How?
Thanks,
Rod
--
More information about the Pdx-pm-list
mailing list