pg_locks view and user locks

Merlin Moncure wrote:

Second,
Is there a reason why user level locks are completely undocumented?
AFAICT, There is no mention of them in anywhere in the docs,
particularly 12.4, which describes methods for application managed
concurrency. The availability of cooperative long term locks is
(IMO) a really nice feature, particularly for people porting legacy
applications which depend on explicit locking (there is some good
info in the user lock module which is unfortunately under the GPL).

That's the whole problem. I don't think anyone objects to the user lock
principle, but as long as it's GPL, we won't support it. It was
probably a mistake to accept this module in the first place. I believe
some people have been trying to get the module relicensed, but that
evidently never happened.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Mon, Sep 13, 2004 at 01:34:05PM -0400, Merlin Moncure wrote:

First, the pg_locks view lists user level locks but doesn't really say
much about them. For example, for traditional locks we get the xid but
for user locks we don't really get anything useful. I looked in
lockfuncs.c and didn't see that there would be any real penalty to
displaying the offset/block of the user lock, and returning it in
request from pg_locks.

ISTM it would be better to have pg_locks show only system locks, and
another view, say pg_userlocks, to show user locks. This would allow to
show different data; for example, the PID of the process involved.
What's more, user locks and system locks never conflict, so it seems
wrong to show them together.

Another option would be to add another column to pg_locks to say what
lockmethod (1 for system, 2 for user) is used in each lock.

Maybe we want to separate locks even more, and use one table to show
xact locks, another to show table locks, and yet another to show user
locks. But maybe this is a bad idea right from the start.

Is this view frequently queried by system processes?

I don't think there is a lot of server-side stuff (aside from possible
UDFs) that use pg_locks directly. It's there only for user convenience.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Someone said that it is at least an order of magnitude more work to do
production software than a prototype. I think he is wrong by at least
an order of magnitude." (Brian Kernighan)

Merlin Moncure wrote:

Second,
Is there a reason why user level locks are completely undocumented?
AFAICT, There is no mention of them in anywhere in the docs,
particularly 12.4, which describes methods for application managed
concurrency. The availability of cooperative long term locks is
(IMO) a really nice feature, particularly for people porting legacy
applications which depend on explicit locking (there is some good
info in the user lock module which is unfortunately under the GPL).

That's the whole problem. I don't think anyone objects to the user

lock

principle, but as long as it's GPL, we won't support it. It was
probably a mistake to accept this module in the first place. I

believe

some people have been trying to get the module relicensed, but that
evidently never happened.

Well, the lock module itself is nothing more than some wrappers for the
system lock manager functions (LockAcquire,etc) in C and wrappers for
those in SQL, plus a makefile and some basic documentation...in other
words almost nothing.

If the 'userlock' module author can't be found or refuses to open his
module, why not simply rewrite it? It's extremely small. This also
gives an opportunity to add some missing parts, like resolving a lock to
a pid and better error handling.

In any case, IMHO it is worthwhile to flesh out 12.3, adding user locks
(simply listing as a lock type), 12.4 with usage of above, perhaps with
an example. Also a mention of userlocks in 43.32 and possibly a new
entry in this section, depending on how they get handled per my
suggestion wrt pg_locks. Also possibly a note in 16.4.8
(postgresql.conf entry for max_locks_per_transaction) describing the
interaction between this parameter and user locks (aside: is there
any?). Certain aspects of these changes would of course depend on the
feasibility of moving some of the lock interfaces into the main project.

I'd be willing to have a stab at the above, is it worthwhile?

Merlin

Peter Eisentraut <peter_e@gmx.net> writes:

That's the whole problem. I don't think anyone objects to the user lock
principle, but as long as it's GPL, we won't support it. It was
probably a mistake to accept this module in the first place. I believe
some people have been trying to get the module relicensed, but that
evidently never happened.

I said some time ago that I would pester contrib authors about fixing
license issues, but it still hasn't gotten to the top of my to-do list :-(
AFAIK nothing at all has been done on this score.

If Merlin is hot about contrib/userlock right now, he's welcome to try
to contact the original author and get this dealt with.

The point about not importing documentation from a mislicensed contrib
module is valid IMHO.

regards, tom lane

I said some time ago that I would pester contrib authors about fixing
license issues, but it still hasn't gotten to the top of my to-do list

:-(

AFAIK nothing at all has been done on this score.

If Merlin is hot about contrib/userlock right now, he's welcome to try
to contact the original author and get this dealt with.

The point about not importing documentation from a mislicensed contrib
module is valid IMHO.

I'll try to contact the author and see what comes up. Supposing the
userlock module is re-licensed (or a suitable replacement is contributed
and accepted), is there any merit to:

Promoting the user lock wrappers out of contrib (plus fleshing them out
a bit)?
Adjusting the pg_locks view to return the user lock specific information
(or, as Alvaro suggested, to add a new view)?
A general documentation update wrt user locks?

Merlin

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:

... is there any merit to promoting the user lock wrappers out of contrib

Dunno. Yours is the first message I can recall in quite a long time
indicating that anyone was using userlocks. I thought the code was kind
of dying on the vine. Of course it's hard to tell whether that's not
just *because* it's in contrib and not mainstream. But personally I'd
like to see some more evidence of use before we promote it. (And yeah,
the API could probably use some cleanup first.)

regards, tom lane

Alvaro Herrera <alvherre@dcc.uchile.cl> writes:

ISTM it would be better to have pg_locks show only system locks, and
another view, say pg_userlocks, to show user locks. This would allow to
show different data; for example, the PID of the process involved.

I think this is a bad idea, at least for the base-level view, because it
would imply that it's impossible to get a truly simultaneous view of the
state of all the locks. It would certainly be a bad idea to so separate
xact and table locks. I'd prefer to extend the present approach and add
columns that are NULL when the type of lock isn't relevant to them.

Another option would be to add another column to pg_locks to say what
lockmethod (1 for system, 2 for user) is used in each lock.

How about a text column with values "TABLE", "XACT", "USER"?

regards, tom lane

This is the first I have ever heard "user locks," but I have more than
once wanted a lock that would persist beyond the end of a transaction.
Do these do that?

Paul

On Mon, Sep 13, 2004 at 08:06:08PM -0400, Paul Tillotson wrote:

This is the first I have ever heard "user locks," but I have more than
once wanted a lock that would persist beyond the end of a transaction.
Do these do that?

Yes.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"El destino baraja y nosotros jugamos" (A. Schopenhauer)

"Merlin Moncure" <merlin.moncure@rcsonline.com> writes:

... is there any merit to promoting the user lock wrappers out of

contrib

Dunno. Yours is the first message I can recall in quite a long time
indicating that anyone was using userlocks. I thought the code was

kind

of dying on the vine. Of course it's hard to tell whether that's not
just *because* it's in contrib and not mainstream. But personally I'd
like to see some more evidence of use before we promote it. (And

yeah,

the API could probably use some cleanup first.)

Well, what's interesting about user locks is that all the real
substantive stuff is already built into the lock manager. The userlock
contrib module, depending on how you count it, consists of about 10
lines of actual code and only (minimally) exposes the lock manager.
It's like some GPL code has parked itself on top of an existing server
feature (and the copyright on the code in this module is dubious
anyways, it being defined strictly by function). Plus, there are some
important missing parts lock being able to resolve a locktag back to a
pid and being able to query a lock without acquiring it.

Anyways, there is a reasonable argument to be made for keeping user
locks a loadable module because of the potential for misuse...it's just
too easy to bring down a server with them in place. A remedy might
involve some combination of the following:

1. rename max_locks_per_transaction to something more reasonable...this
leads one to believe persistent locks are not managed here (but they
are).

2. consider bumping out of memory condition in lock table to 'fatal' to
better guard against a process in runaway lock acquirement...at least
there is a chance for recovery now.

3. enforce a limit to max#locks for each process that is less than the
total maximum size (for example, no single process can acquire more than
half of the entire lock table).

IMO, with some safety and usabilty aspects rolled in, documentation
changes, and re-packaging, plus throw out the gpl stuff, this makes a
reasonable (if somewhat esoteric) bullet point feature for 8.1.

Merlin