pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jan Wieck <janwieck@yahoo.com>
To: SHELTON,MICHAEL (Non-HP-Boise,ex1) <michael_shelton@non.hp.com>
Cc: 'Braum Meakes' <braum@telus.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: User Permissions
Date: Thu, 17 Jan 2002 23:15:03 -0500 (EST)
Message-ID: <200201180415.g0I4F4o04967@saturn.janwieck.net> (raw)
In-Reply-To: <FB60DFB2C0E24449AC0C21F743B935410150F990@xboi02.boi.hp.com>

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 select a
    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




view thread (8+ messages)

Message-ID: <200201180415.g0I4F4o04967@saturn.janwieck.net>
Permalink:  ../200201180415.g0I4F4o04967@saturn.janwieck.net/
Also on:    postgresql.org/message-id/200201180415.g0I4F4o04967@saturn.janwieck.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: janwieck@yahoo.com, michael_shelton@non.hp.com, braum@telus.net
  Subject: Re: User Permissions
  In-Reply-To: <200201180415.g0I4F4o04967@saturn.janwieck.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox