[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