From: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
---|---|
To: | AgentM <agentm(at)themactionfaction(dot)com> |
Cc: | "postgres hackers" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: advisory locks and permissions |
Date: | 2006-09-22 19:33:49 |
Message-ID: | b42b73150609221233w17b886bbqc1272491d1bb6f10@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On 9/22/06, AgentM <agentm(at)themactionfaction(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-09-22 19:43:53 | Re: advisory locks and permissions |
Previous Message | Bruce Momjian | 2006-09-22 19:31:21 | Re: advisory locks and permissions |