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