Roxanne Reid-Bennett wrote: > When you have a sequence of steps that need to be serialized across > processes, choose or even create a table to use for locking This can also be done with an advisory lock, presumably faster: http://www.postgresql.org/docs/current/static/explicit-locking.html DECLARE lock_key int := hashtext(hometown_name); BEGIN SELECT pg_advisory_xact_lock(lock_key,0); // check for existence and insert if it doesn't exist END; When several sessions try to insert the same town (or strictly speaking, with the same hash), only one of them will be allowed to proceed, the others being put to wait until the first one commits or rollbacks, and so on until every session gets through. The lock is automatically released at the end of the transaction. It makes no difference if the "check and insert" is crammed into a single SQL statement or several statements in procedural style. This technique is intended to work with the default "Read Committed" isolation level, which allows the concurrent transactions to "see" the new row inserted by the single other transaction that got the "it does not yet exist" result in the check for existence, and proceeded to insert and eventually commit. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
