Re: using column as 'mutex' - Mailing list pgsql-general

RE all below: (hand assembled reply)

I looked through all the documentation, and I did not find 'WITH UPDATE' as part of any transaction type, so I didn't
endup using that information. 

For the record, (in the mail lists), I am using symfony/Doctrine ORM. I used something like this:

    try {
      Doctrine query to update a row's timestamp for 5-10 minute window
        where value=value in a unique column

      Find same record and hydrate an object (creates exception if
        object does not exist) and assign to class variable/attribute

    }  catch(Exception $e) {
      return FALSE;
    }
    return FALSE;

The calling code will know if the record was saved fromt he garbage collection routine. However, I may end up writing a
customeSQL queiry (i.e., not using the ORM) when I put this into action against a running garbage collection daemon.  



On Mon, Nov 30, 2009 at 9:02 PM, Dennis Gearon <gearond@sbcglobal.net>

Dennis Gearon

Merlin Moncure <mmoncure@gmail.com> wrote:

Just make sure that when you inspect the record, do so: a. in a
transaction (preferably a brief one) and b. WITH UPDATE until your
operation completes.  That way if two operations collide one will wait
for the other to complete.  If you need to lock an external structure
(like a file) for a period shorter or longer than a single
transaction, use an advisory lock.

merlin

> I want to use a dbase record in Postgresql as a mutex in a garbage collection/file upload system.
>
> If I tranfer an uploaded file to a directory, write all its meta information and file name in a record, the user
shouldhave 5 minutes to finish inputting the required info for the record. (I want to store in file system instead of
databasetable). 
>
> So there will be two processes racing against each other in the database, the web page changing the status of the
recordto 'complete', and the cron job erasing files that have status 'in-process' and are older than ten minutes. 
>
> So there will probably be 6 possible scenarios:
>
> 1/ Web site changes status before the cron job gets to the record and deltes the file, no problem.
> 2/ Cron job changes the record and deletes the file before the website gets to the record, no problem.
>
> 3/ Both access the the record relatively at the same time, the web site having the earlier, lower transaction number
andalso finishes first. 
> 4/ Both access the the record relatively at the same time, the cron job having the earlier, lower transaction number
andalso finishes first. 
>
> 5/ Both access the the record relatively at the same time, the web site having the later, HIGHER transaction number
BUTfinishes first. 
> 6/ Both access the the record relatively at the same time, the cron job having the later, HIGHER transaction number
BUTfinishes first. 
Dennis Gearon