User locks code

An interesting method would be to allow users to simply avoid locked
rows:

SELECT * FROM queue FOR UPDATE LIMIT 1 UNLOCKED;

Unlocked, return immediately, whatever could be used as a keyword to
avoid rows that are locked (skipping over them).

For update locks the row of course. Currently for the above type of
thing I issue an ORDER BY random() which avoids common rows enough,
the queue agent dies if queries start taking too long (showing it's
waiting for other things) and tosses up new copies if it goes a while
without waiting at all (showing increased load).

--
Rod Taylor

This message represents the official view of the voices in my head

----- Original Message -----
From: "Mikheev, Vadim" <vmikheev@SECTORBASE.COM>
To: <pgsql-hackers@postgresql.org>
Sent: Friday, August 17, 2001 2:48 PM
Subject: [HACKERS] User locks code

1. Just noted this in contrib/userlock/README.user_locks:

User locks, by Massimo Dal Zotto <dz@cs.unitn.it>
Copyright (C) 1999, Massimo Dal Zotto <dz@cs.unitn.it>

This software is distributed under the GNU General Public License
either version 2, or (at your option) any later version.

Well, anyone can put code into contrib with whatever license
he/she want but "user locks" package includes interface
functions in contrib *and* changes in our lock manager, ie
changes in backend code. I wonder if backend' part of package
is covered by the same license above? And is it good if yes?

2. Not good implementation, imho.

It's too complex (separate lock method table, etc). Much cleaner
would be implement this feature the same way as transactions
wait other transaction commit/abort: by locking objects in
pseudo table. We could get rid of offnum and lockmethod from
LOCKTAG and add

struct
{
Oid RelId;
Oid ObjId;
} userObjId;

to objId union of LOCKTAG.

This way user could lock whatever object he/she want in specified
table and note that we would be able to use table access rights to
control if user allowed to lock objects in table - missed in 1.

One could object that 1. is good because user locks never wait.
I argue that "never waiting" for lock is same bad as "always

waiting".

Someday we'll have time-wait etc features for general lock method
and everybody will be happy -:)

Comments?

Vadim
P.S. I could add 2. very fast, no matter if we'll keep 1. or not.

---------------------------(end of

broadcast)---------------------------

Well, ability to lock only unlocked rows in select for update is useful,
of course. But uniq features of user'locks are:

1. They don't interfere with normal locks hold by session/transaction.
2. Share lock is available.
3. User can lock *and unlock objects* inside transaction, which is not
(and will not be) available with locks held by transactions.

They are interesting too and proposed implementation will not impact lock
manager (just additional 4 bytes in LOCKTAG => same size of LOCKTAG
on machines with 8 bytes alignment).

Well, ability to lock only unlocked rows in select for update is useful,
of course. But uniq features of user'locks are:

1. They don't interfere with normal locks hold by session/transaction.
2. Share lock is available.
3. User can lock *and unlock objects* inside transaction, which is not
(and will not be) available with locks held by transactions.

They are interesting too and proposed implementation will not impact lock
manager (just additional 4 bytes in LOCKTAG => same size of LOCKTAG
on machines with 8 bytes alignment).

An interesting method would be to allow users to simply avoid locked
rows:

SELECT * FROM queue FOR UPDATE LIMIT 1 UNLOCKED;

Unlocked, return immediately, whatever could be used as a keyword to
avoid rows that are locked (skipping over them).

For update locks the row of course. Currently for the above type of
thing I issue an ORDER BY random() which avoids common rows enough,
the queue agent dies if queries start taking too long (showing it's
waiting for other things) and tosses up new copies if it goes a while
without waiting at all (showing increased load).

--
Rod Taylor

This message represents the official view of the voices in my head

----- Original Message -----
From: "Mikheev, Vadim" <vmikheev@SECTORBASE.COM>
To: <pgsql-hackers@postgresql.org>
Sent: Friday, August 17, 2001 2:48 PM
Subject: [HACKERS] User locks code

1. Just noted this in contrib/userlock/README.user_locks:

User locks, by Massimo Dal Zotto <dz@cs.unitn.it>
Copyright (C) 1999, Massimo Dal Zotto <dz@cs.unitn.it>

This software is distributed under the GNU General Public License
either version 2, or (at your option) any later version.

Well, anyone can put code into contrib with whatever license
he/she want but "user locks" package includes interface
functions in contrib *and* changes in our lock manager, ie
changes in backend code. I wonder if backend' part of package
is covered by the same license above? And is it good if yes?

2. Not good implementation, imho.

It's too complex (separate lock method table, etc). Much cleaner
would be implement this feature the same way as transactions
wait other transaction commit/abort: by locking objects in
pseudo table. We could get rid of offnum and lockmethod from
LOCKTAG and add

struct

Oid RelId;
Oid ObjId;
} userObjId;

to objId union of LOCKTAG.

This way user could lock whatever object he/she want in specified
table and note that we would be able to use table access rights to
control if user allowed to lock objects in table - missed in 1.

One could object that 1. is good because user locks never wait.
I argue that "never waiting" for lock is same bad as "always

waiting".

Someday we'll have time-wait etc features for general lock method
and everybody will be happy -:)

Comments?

Vadim
P.S. I could add 2. very fast, no matter if we'll keep 1. or not.

4. Most important: user locks are retained across transaction, which is
not possible with ordinary locks.

5. User locks semantic is defined entirely by the application and is not
related to rows in the database.

I wrote the user locks code because I needed a method to mark items as
`busy' for very long time to avoid more users modifying the same object
and overwriting each one's changes. This requires two features:

1. they must survive transaction boundary. The typical use of user
locks is:

transaction 1: select object,user_lock(object);

... work on object for long time

transaction 2: update object,user_unlock(object);

2. they must not block if the object is already locked, so that the
program doesn't freeze and the user simply knows it can't use that
object.

When I wrote the code the only way to do this was to add a separate lock
table and use the same machinery of ordinary locks. I agree that the code
is complex and should probably be rewritten.

If you think there is a better way to implement this feature go ahead,
better code is always welcome.

The only problem I have found with user locks is that if a backend crashes
without releasing a lock there is no way to relase it except restarting
the whole postgres (I don't remember exactly why, I forgot the details).

Regarding the licencing of the code, I always release my code under GPL,
which is the licence I prefer, but my code in the backend is obviously
released under the original postgres licence. Since the module is loaded
dynamically and not linked into the backend I don't see a problem here.
If the licence becomes a problem I can easily change it, but I prefer the
GPL if possible.

--
Massimo Dal Zotto

+----------------------------------------------------------------------+
|  Massimo Dal Zotto               email: dz@cs.unitn.it               |
|  Via Marconi, 141                phone: ++39-0461534251              |
|  38057 Pergine Valsugana (TN)      www: http://www.cs.unitn.it/~dz/  |
|  Italy                             pgp: see my www home page         |
+----------------------------------------------------------------------+

At 11:20 AM 8/19/01 -0700, Vadim Mikheev wrote:

Well, ability to lock only unlocked rows in select for update is useful,
of course. But uniq features of user'locks are:

1. They don't interfere with normal locks hold by session/transaction.
2. Share lock is available.
3. User can lock *and unlock objects* inside transaction, which is not
(and will not be) available with locks held by transactions.

Would your suggested implementation allow locking on an arbitrary string?

If it does then one of the things I'd use it for is to insert unique data
without having to lock the table or rollback on failed insert (unique index
still kept as a guarantee).

Cheerio,
Link.

Would your suggested implementation allow locking on an
arbitrary string?

Well, placing string in LOCKTAG is not good so we could
create auxilary hash table in shmem to keep such strings
and use string' address as part of LOCKTAG. New function
(LockRelationKey?) in lmgr.c would first find/place
key in that table, than construct LOCKTAG and call
LockAcquire.
Possible syntax:

LOCK TABLE relation IN {SHARE | EXCLUSIVE} MODE
ON KEY user-string [FOR TRANSACTION | FOR SESSION];

UNLOCK (RELEASE?) TABLE relation {SHARE | EXCLUSIVE} LOCK
ON KEY user-string;

(or just some built-in functions).

If it does then one of the things I'd use it for is to insert
unique data without having to lock the table or rollback on
failed insert (unique index still kept as a guarantee).

(Classic example how could be used SAVEPOINTs -:))

So, in your application you would first lock a key in excl mode
(for duration of transaction), than try to select and insert unless
found? (Note that this will not work with serializable isolevel.)

Comments?

Vadim

At 09:39 AM 20-08-2001 -0700, Mikheev, Vadim wrote:

If it does then one of the things I'd use it for is to insert
unique data without having to lock the table or rollback on
failed insert (unique index still kept as a guarantee).

(Classic example how could be used SAVEPOINTs -:))

I guess so. But this could be faster.

So, in your application you would first lock a key in excl mode
(for duration of transaction), than try to select and insert unless
found? (Note that this will not work with serializable isolevel.)

yep:
lock "tablename.colname.val=1"
select count(*) from tablename where colname=1
If no rows, insert, else update.
(dunno if the locks would scale to a scenario with hundreds of concurrent
inserts - how many user locks max?).

Why wouldn't it work with serializable isolevel?

Anyway, I believe that isolevel doesn't really serialise things in this
case (inserting a unique row) so it wouldn't matter to me.

Regards,
Link.

Hi all,

I'm currently trying to develop a log analyzer for PostgreSQL logs and at
the first
stage I'm finding a little problem with the postgresql.conf option
log_timestamp.

The problem is that if this option is set to false we have no idea of when
the backend
is started:

DEBUG: database system was shut down at 2001-08-20 21:51:54 CEST
DEBUG: CheckPoint record at (0, 126088776)
DEBUG: Redo record at (0, 126088776); Undo record at (0, 0); Shutdown TRUE
DEBUG: NextTransactionId: 489793; NextOid: 77577
DEBUG: database system is in production state

Is it possible to have it into the last line as we have the information of
the database
shutdown timestamp in the first line ?

Also, an other question is why using timestamp into the other log instead of
the value
of time in seconds since the Epoch like the time() function do ?

I don't know if it is speedest or not but if timestamp is system local
dependant
I think it should be very difficult to me to have a portable log analyzer...

Regards,

Gilles Darold

Gilles DAROLD writes:

Is it possible to have it into the last line as we have the information of
the database
shutdown timestamp in the first line ?

We not just turn time stamping on?

Also, an other question is why using timestamp into the other log instead of
the value
of time in seconds since the Epoch like the time() function do ?

Because humans generally reckon time the former way.

I don't know if it is speedest or not but if timestamp is system local
dependant
I think it should be very difficult to me to have a portable log analyzer...

In the current system, the timestamp is not locale dependent, but that
doesn't mean that it could be in the future. (I wouldn't find it
particularly useful, because the current format is internationally
readable.)

What *is* locale dependent are the messages, though. Not sure how you
plan to deal with that.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

yep:
lock "tablename.colname.val=1"
select count(*) from tablename where colname=1
If no rows, insert, else update.
(dunno if the locks would scale to a scenario with hundreds
of concurrent inserts - how many user locks max?).

I don't see problem here - just a few bytes in shmem for
key. Auxiliary table would keep refcounters for keys.

Why wouldn't it work with serializable isolevel?

Because of selects see old database snapshot and so you
wouldn't see key inserted+committed by concurrent tx.

Vadim

Regarding the licencing of the code, I always release my code
under GPL, which is the licence I prefer, but my code in the
backend is obviously released under the original postgres
licence. Since the module is loaded dynamically and not linked
into the backend I don't see a problem here.

The problem is how to use user-locks in commercial projects.
Some loadable interface functions are required to use in-backend
user lock code, but interface is so simple - if one would write
new functions they would look the same as yours covered by GPL.

If the licence becomes a problem I can easily change it, but
I prefer the GPL if possible.

Actually I don't see why to cover your contrib module by GPL.
Not so much IP (intellectual property) there. Real new things
which make new feature possible are in lock manager.

Vadim

"Mikheev, Vadim" <vmikheev@SECTORBASE.COM> writes:

(dunno if the locks would scale to a scenario with hundreds
of concurrent inserts - how many user locks max?).

I don't see problem here - just a few bytes in shmem for
key. Auxiliary table would keep refcounters for keys.

I think that running out of shmem *would* be a problem for such a
facility. We have a hard enough time now sizing the lock table for
system locks, even though they use fixed-size keys and the system as
a whole is designed to ensure that not too many locks will be held
simultaneously. (For example, SELECT FOR UPDATE doesn't try to use
per-tuple locks.) Earlier in this thread, someone proposed using
user locks as a substitute for SELECT FOR UPDATE. I can guarantee
you that that someone will run out of shared memory before long,
if the userlock table resides in shared memory.

regards, tom lane

I don't see problem here - just a few bytes in shmem for
key. Auxiliary table would keep refcounters for keys.

I think that running out of shmem *would* be a problem for such a
facility. We have a hard enough time now sizing the lock table for

Auxiliary table would have fixed size and so no new keys would be
added if no space. I don't see problem with default 8Kb aux table,
do you?

system locks, even though they use fixed-size keys and the system as
a whole is designed to ensure that not too many locks will be held
simultaneously. (For example, SELECT FOR UPDATE doesn't try to use
per-tuple locks.) Earlier in this thread, someone proposed using
user locks as a substitute for SELECT FOR UPDATE. I can guarantee
you that that someone will run out of shared memory before long,
if the userlock table resides in shared memory.

How is proposed "key locking" is different from user locks we
have right now? Anyone can try to acquire many-many user locks.

Vadim

Hi all,

Here is a first draft generated by a log analyzer for postgres I've wrote today:

http://www.samse.fr/GPL/log_report/

In all this html report there is what I'm able to extract minus the statistics.

I need to know what people want to see reported to have a powerfull log analyzer,

I can spend this week do do that...

Peter, sorry for my poor english that what I mean is that if you don't activated
the
log_timestamp option we have no idea when the postmaster have been started
(or at least doing a ls -la on /tmp/.s.PGSQL.5432). Other things was just
question
and you answer them, thanks.

Regards

Gilles DAROLD wrote:

Hi all,

Here is a first draft generated by a log analyzer for postgres I've wrote today:

http://www.samse.fr/GPL/log_report/

In all this html report there is what I'm able to extract minus the statistics.

I need to know what people want to see reported to have a powerfull log analyzer,

I like what you have there so far.

For my own use I would like to see the ability to turn some of these off,
and also perhaps a summary page that you would click through to the more
detailed reports.

The 'query' page is kind of complicated too. Would it be possible to put
that into a table layout as well?
+-------------------------------+
|select... |
+----+----+----+--------+-------+
|stat|stat|stat|stat ...| |
+----+----+----+--------+-------+

sort of layout.

It would be nice to see an EXPLAIN on the query page, but you would want
this to be an option, I guess. I imagine you could do this by getting the
EXPLAIN at log analysis time if it isn't in the logs.

Cheers,
Andrew.
--
_____________________________________________________________________
Andrew McMillan, e-mail: Andrew @ catalyst . net . nz
Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington
Me: +64(21)635-694, Fax:+64(4)499-5596, Office: +64(4)499-2267xtn709

Hi all,

I have updated the drafts for pg log analyzer especially for EXPLAIN output.
What do you want to see as statistics result. Currently I only output the following:

- scan type
- startup cost
- total cost
- number of rows returned
- and the width

There's certainly other usefull information but I don't know. Please let me know !

Note:

This is a simple draft to show what can be done, as a general purpose it will include:

- A configuration file (to choose what should be reported, paths, etc...)
or/and command line args
- An index page with resume of all reports
- Incremental scan working on full or rotate log

For other good requests it's done...

Let me know any other requests otherwise I will publish the first release at least on
monday
if not tomorow !

http://www.samse.fr/GPL/log_report/

Regards,

Gilles Darold

Andrew McMillan wrote:

Regarding the licencing of the code, I always release my code under GPL,
which is the licence I prefer, but my code in the backend is obviously
released under the original postgres licence. Since the module is loaded
dynamically and not linked into the backend I don't see a problem here.
If the licence becomes a problem I can easily change it, but I prefer the
GPL if possible.

We just wanted to make sure the backend changes were not under the GPL.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

If the licence becomes a problem I can easily change it,
but I prefer the GPL if possible.

We just wanted to make sure the backend changes were not
under the GPL.

No, Bruce - backend part of code is useless without interface
functions and I wonder doesn't GPL-ed interface implementation
prevent using of user-locks in *commercial* applications.
For example, one could use user-locks for processing incoming
orders by multiple operators:
select * from orders where user_lock(orders.oid) = 1 LIMIT 1
- so each operator would lock one order for processing and
operators wouldn't block each other. So, could such
application be commercial with current licence of
user_lock()? (Sorry, I'm not licence guru.)

DISCLAIMER (to avoid ungrounded rumors -:))
I have no plans to use user-locks in applications
of *any* kind (free/commercial). It's just matter of
principle - anything in/from backend code maybe used
for any purposes, - that's nature of our licence.

DISCLAIMER II (to avoid ungrounded rumors in future -:))
I would be interested in using proposed "key-locking"
in some particular commercial application but this
feature is not "must have" for that application -
for my purposes it's enough:

----------------------------------------------------------
LOCKTAG tag;
tag.relId = XactLockTableId;
tag.dbId = _tableId_;
// tag.dbId = InvalidOid is used in XactLockTableInsert
// and no way to use valid OID for XactLock purposes,
// so no problem
tag.objId.xid = _user_key_;
----------------------------------------------------------

- but I like standard solutions more -:)
(BTW, key-locking was requested by others a long ago.)

Vadim

If the licence becomes a problem I can easily change it,
but I prefer the GPL if possible.

We just wanted to make sure the backend changes were not
under the GPL.

No, Bruce - backend part of code is useless without interface
functions and I wonder doesn't GPL-ed interface implementation
prevent using of user-locks in *commercial* applications.
For example, one could use user-locks for processing incoming
orders by multiple operators:
select * from orders where user_lock(orders.oid) = 1 LIMIT 1
- so each operator would lock one order for processing and
operators wouldn't block each other. So, could such
application be commercial with current licence of
user_lock()? (Sorry, I'm not licence guru.)

I assume any code that uses contrib/userlock has to be GPL'ed, meaning
it can be used for commercial purposes but can't be sold as binary-only,
and actually can't be sold for much because you have to make the code
available for near-zero cost.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

For example, one could use user-locks for processing incoming
orders by multiple operators:
select * from orders where user_lock(orders.oid) = 1 LIMIT 1
- so each operator would lock one order for processing and
operators wouldn't block each other. So, could such
application be commercial with current licence of
user_lock()? (Sorry, I'm not licence guru.)

I assume any code that uses contrib/userlock has to be GPL'ed,
meaning it can be used for commercial purposes but can't be sold
as binary-only, and actually can't be sold for much because you
have to make the code available for near-zero cost.

I'm talking not about solding contrib/userlock separately, but
about ability to sold applications which use contrib/userlock.
Sorry, if it was not clear.

Vadim