Re: Advisory transaction lock for 128-bit space

On Thu, Mar 8, 2012 at 2:05 AM, Kiriakos Georgiou
<kg(dot)postgresql(at)olympiakos(dot)com> wrote:
> Indeed, if there is not some sort of implementation limitation, it would be
> cool to be able to lock two big integers like so:
>
>     pg_try_advisory_xact_lock(key1 bigint, key2 bigint)

Well, this would require expanding the structure that holds the
in-memory lock. This is not free, since it's also used by the
database for internal lock tables. I would advise trying to work
under the constraints of the current system.

If you want a database-wide advisory lock for rows, probably the best
bet is to make a sequence that is shared by all tables that want to
participate in advisory locking. This is simple and works very well
regardless on how your keys are defined (uuid, natural, etc). It's a
good use for a domain:

create sequence lockid_seq;
create domain lockid_t bigint default nextval('lockid_seq');
alter table foo add column lockid lockid_t;

etc. You'll never exhaust a 64 bit sequence. In fact, you can
reserve a few bits off the top in case you want to do some other
advisory locking for different reasons. A bit hacky maybe, but it
works quite well.

merlin