Received: from smtp013.mail.yahoo.com (smtp013.mail.yahoo.com [216.136.173.57]) by postgresql.org (8.11.3/8.11.4) with SMTP id g0I4IZV98537 for ; Thu, 17 Jan 2002 23:18:35 -0500 (EST) (envelope-from janwieck@yahoo.com) Received: from h00045a2e4e7c.ne.mediaone.net (HELO saturn.janwieck.net) (24.128.23.100) by smtp.mail.vip.sc5.yahoo.com with SMTP; 18 Jan 2002 04:18:40 -0000 Received: (from wieck@localhost) by saturn.janwieck.net (8.11.2/8.11.2) id g0I4F4o04967; Thu, 17 Jan 2002 23:15:04 -0500 From: Jan Wieck Message-Id: <200201180415.g0I4F4o04967@saturn.janwieck.net> Subject: Re: User Permissions In-Reply-To: from "SHELTON, MICHAEL (Non-HP-Boise, ex1)" at "Jan 17, 2002 03:00:55 pm" To: "SHELTON,MICHAEL (Non-HP-Boise,ex1)" Date: Thu, 17 Jan 2002 23:15:03 -0500 (EST) CC: "'Braum Meakes'" , pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL68 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Archive-Number: 200201/213 X-Sequence-Number: 6298 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