Re: User Permissions

SHELTON,MICHAEL (Non-HP-Boise,ex1) wrote:
> I don't have an answer for your issue, just a question on your process:
>
> Why do you lock the table instead of using a transaction?  The transaction
> would prevent the data from changing until you either applied your changes
> (insert or update) and commited the transaction or rolled back the
> transaction (due to an error or something).
   He  is  most  probably doing so to achieve the every so often   desired IF_EXISTS_UPDATE_ELSE_INSERT functionality.
   A function in PostgreSQL is allways guaranteed to be  covered   by  a  transaction.  But that doesn't matter. If you
selecta   key for update and based on if you found it decide to  update   or insert, you have a possible race
condition.
   Doing
       SELECT x FROM y WHERE z = 666 FOR UPDATE
   will  succeed in multiple transactions simultaneously as long   as there is no row with z = 666. So the entire  code
section   of  checking  if the key exists until update or insert has to   be covered by a mutex, and there is no other
mutex mechanism   available than a table lock.
 
   Even  if  it's  not  standard,  I  would be for a proprietary   extension that actually implements some  sort  of
mutex for   stored procedures.
 


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com