Re: Proposal: String key space for advisory locks

On Mon, Oct 26, 2009 at 1:54 AM, Christophe Pettus <xof(at)thebuild(dot)com> wrote:
> Greetings,
>
> I'd like to propose a potential patch, and wanted to get preliminary
> feedback on it before I started looking into the design.
>
> Summary:    Add a string key space to the advisory lock functionality.
>
> Rationale:
>
> Right now, the key spaces (the range of unique values that can be used as
> identity) for advisory locks are either a bigint or two ints.  This is, of
> course, technically more than one could imaginably need in any application.
>  The difficulty arises when the number of potential advisory locks is
> related to rows in one or more tables.
>
> For example, suppose one wanted to use advisory locks to signal that a queue
> entry is being processed, and entries in that queue have a primary key
> that's also a bigint.  There's no problem; the advisory lock id is the
> primary key for the row.
>
> And, then, one wants to use an advisory lock to signal that a particular
> record in another table is being processed in a long-term process.  One has
> a series of unappealing alternatives at that point, mostly involving
> encoding a table ID and the primary key of a record into the 64 bit number,
> or just hoping that the primary key doesn't overflow an int, and using the 2
> x int form.

If you want to lock records from multiple tables, probably the best
approach is to use a single sequence and pull IDs from it for each
table you want to use advisory locks with. It doesn't even have to be
the primary key (although it can be)...you can even use a domain:

create sequence lock_seq;
create domain lock_val not null default nextval('lock_seq');
create table a_table(lock_val lock_val, ...);
create table b_table(lock_val lock_val, ...);

Regarding your proposal...the lock system is highly optimized and any
suggestion that incurs performance issues is probably not going to
make it...

merlin