Re: advisory locks and permissions

On 9/22/06, AgentM <agentm@themactionfaction.com> wrote:
> > Except you can put tables (and pretty much all your other objects)
> > in a
> > schema, one that's presumably named after your application. That
> > greatly
> > removes the odds of conficts.
>
> Indeed. In our development environment, we store development,
> integration, and testing schemas in the same database. This makes it
> trivial to move testing data to development, for example.
>
> If I want to use these locks, it seems I will have to hard-code some
> offset into each app or hash the schema name and use that as an
> offset :( In any case, I can't imagine the "wtf?" nightmares an
> accidental collision would induce.

i think you are obsuring something here.  advisory_lock is a mutex
with a numeric name...thats it :) any meaning you impart into that
name is your problem.  listen/notify is a similar construct in that
way.

I ran an erp system, one company per schema, using userlock module for
pessimistic row locking with no problems.  I used bit shifting to
strip off the high bit (out of 48) for special table locks and other
things.  key mechasim was to use a sequence to provide lock id which
was shared by all lockable objects. a domain could be appropriate
here:

create sequence lock_provider;
create domain lockval as bigint default nextval('lock_provider');

and the following becomes standard practice:
create table foo (lv lockval);  <--no need for index here
select pg_advisory_lock(lv) from foo where [..];

for bit shifting or special cases you can wrap the lock function, which i did.

merlin