Ben wrote:
I'm not very familiar with explicitly locking things in SQL, so this may
be a dumb question, but.....
I've got a stored proc that takes a text key and returns an ID. If the
key isn't in a lookup table, it adds it and will return the
auto-generated serial number. If it's already there, it simply returns
the existing serial number.
I'm concerned about multiple sessions calling this at the same time with
the same key. While I *could* just put in a unique constraint and let
things fail, that's hardly graceful. It seems I should be able to lock
the lookup table, but I don't really know what mode to lock that table
in. Will ROW EXCLUSIVE keep the same key from being entered twice?
You are probably looking for select for update:
http://www.postgresql.org/docs/current/static/sql-select.html#SQL-FOR-UPDATE
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
