Cool! :-)

> 
> > Pablo Funes <pablo@cs.brandeis.edu> writes:
> > > Perhaps in a future version will PQrequestCancel be able to terminate
> > > a waiting-for-lock state?
> > 
> > Seems like a reasonable suggestion.  It's too late to consider this for
> > 6.5 (we were supposed to freeze the feature list quite a while back)
> > but I support putting it on the TODO list for a future release.
> > 
> > 			regards, tom lane
> > 
> 
> Added:
> 
> 	* Allow PQrequestCancel() to terminate when in waiting-for-lock state
> 

Mon, 31 May 1999 20:10:52 +2000 (EDT)
Pablo Funes <pablo@cs.brandeis.edu>
Re: [HACKERS] please?
Please take a sec to read this question. I've posted
it several times but got no comments at all. Thanx, Pablo. 

---

Forwarded message:
>From pablo Thu May 27 18:42:11 1999
Subject: nonblocking lock? 
To: pgsql-hackers@postgresql.org
Date: Thu, 27 May 1999 18:42:11 -0400 (EDT)
Content-Type: text
Content-Length: 890       

Is it possible to do a nonblocking lock? That is, 
I want several clients to execute,

    begin
    if table A is locked
    then
        go around doing stuff on other tables
    else
        lock A and do stuff on A that takes a long time
    endif

the problem is, if I use normal lock, then 
after one client has locked and is doing stuff on A
the other one will block and thus it won't be able
to go around doing stuff on other tables. Is it
possible to do a nonblocking lock that will just
fail if the table is locked already? 


NOTE: I tried using PQrequestCancel but it won't
cancel the request. It still blocks for as long
as the lock lasts. The only way around I've found so 
far is to use PQreset. That's crude but works. But 
it leaves a dangling postmaster process that lives
until the orignal lock is freed. Any other ideas? 

Thanks a lot 

Pablo Funes
Brandeis University
pablo@cs.brandeis.edu

Mon, 31 May 1999 13:08:32 -0400 (EDT)
Pablo Funes <pablo@cs.brandeis.edu>
please?
Pablo Funes <pablo@cs.brandeis.edu> writes:
> Is it possible to do a nonblocking lock?

There is no way to do that in 6.4.  I am not sure whether the MVCC
additions in 6.5 provide a way to do it or not (Vadim?).

> NOTE: I tried using PQrequestCancel but it won't
> cancel the request. It still blocks for as long
> as the lock lasts. The only way around I've found so 
> far is to use PQreset. That's crude but works.

Not really --- what PQreset is really doing is disconnecting your
client from its original backend and starting a new backend.  The
old backend is still there trying to get the lock; it won't notice
that you've disconnected from it until after it acquires the lock.
Obviously, this approach doesn't scale up very well... you'll soon
run out of backend processes.

A possible approach is for your clients to maintain more than one
backend connection, and use one of the backends to do the stuff 
that might block while using another one to do the stuff that won't.
This would take a little more bookkeeping in the client but it seems
like a logically cleaner way to think about it.

			regards, tom lane

Mon, 31 May 1999 13:35:53 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] please? 
> A possible approach is for your clients to maintain more than one
> backend connection, and use one of the backends to do the stuff 
> that might block while using another one to do the stuff that won't.
> This would take a little more bookkeeping in the client but it seems
> like a logically cleaner way to think about it.

Or you could do it outside of the database using a Unix filesystem lock
file.  There are symantics for no-blocking lock stuff in flock():

           #define LOCK_SH 0x01    /* shared file lock */
           #define LOCK_EX 0x02    /* exclusive file lock */
           #define LOCK_NB 0x04    /* don't block when locking */
           #define LOCK_UN 0x08    /* unlock file */

I don't know of any SQL databases that allow non-blocking lock requests.


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Mon, 31 May 1999 13:43:56 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] please?
Bruce Momjian wrote:
> 
> I don't know of any SQL databases that allow non-blocking lock requests.
> 

Oracle OCI has oopt() and Informix Online has dirty read that do the trick for
me.
--------
Regards
Theo

Mon, 31 May 1999 20:31:48 +0200
Theo Kramer <theo@flame.co.za>
Re: [HACKERS] please?
First, thanks all for the feedback and good luck with the new
release!

> 
> > A possible approach is for your clients to maintain more than one
> > backend connection, and use one of the backends to do the stuff 
> > that might block while using another one to do the stuff that won't.

Yes. Same effect as PQreset() if the code is to be ran only once, but
a lot better if inside a loop!. 

> Or you could do it outside of the database using a Unix filesystem lock
> file.  There are symantics for no-blocking lock stuff in flock():
> 
>            #define LOCK_SH 0x01    /* shared file lock */
>            #define LOCK_EX 0x02    /* exclusive file lock */
>            #define LOCK_NB 0x04    /* don't block when locking */
>            #define LOCK_UN 0x08    /* unlock file */

Exactly what's wanted in this case. The unix flock() locks a file or,
if already locked, either waits or fails depending on what you
requested. The lock is released by either an unlock operation or the
death of the locking process. It would solve my problem, except it 
requires all clients to share a filesystem. 

> I don't know of any SQL databases that allow non-blocking lock requests.

I'm not very familiar with full-scale SQL but seems odd not to have
such things. I guess from the language point of view there ought to be
a way to know when an item is unavailable/undefined (it's been locked 
for writing), if you don't want to wait a long time to get a value.

Imagine I go to the store at 11am and can't buy soap - because
the price of soap is unknown because it's a heavy trading day for soap 
at the ny stock exchange. Even if the shop's definition may not allow for
soap to be sold before the stock market closes and the final price is
known, I shouldn't be forced to wait there doing nothing. I can do 
other shopping around and come back later for my soap!  ;-)

Regards,

Pablo

Mon, 31 May 1999 14:35:24 -0400 (EDT)
Pablo Funes <pablo@cs.brandeis.edu>
Re: [HACKERS] please?
> First, thanks all for the feedback and good luck with the new
> release!
> 
> > 
> > > A possible approach is for your clients to maintain more than one
> > > backend connection, and use one of the backends to do the stuff 
> > > that might block while using another one to do the stuff that won't.
> 
> Yes. Same effect as PQreset() if the code is to be ran only once, but
> a lot better if inside a loop!. 
> 
> > Or you could do it outside of the database using a Unix filesystem lock
> > file.  There are symantics for no-blocking lock stuff in flock():
> > 
> >            #define LOCK_SH 0x01    /* shared file lock */
> >            #define LOCK_EX 0x02    /* exclusive file lock */
> >            #define LOCK_NB 0x04    /* don't block when locking */
> >            #define LOCK_UN 0x08    /* unlock file */
> 
> Exactly what's wanted in this case. The unix flock() locks a file or,
> if already locked, either waits or fails depending on what you
> requested. The lock is released by either an unlock operation or the
> death of the locking process. It would solve my problem, except it 
> requires all clients to share a filesystem. 

Sharing file systems.  Good point.  You could have a table you use to
lock.  Lock the table, view the value, possibly modify, and unlock. 
This does not handle the case where someone died and did not remove
their entry from the lock table.  

> 
> > I don't know of any SQL databases that allow non-blocking lock requests.
> 
> I'm not very familiar with full-scale SQL but seems odd not to have
> such things. I guess from the language point of view there ought to be
> a way to know when an item is unavailable/undefined (it's been locked 
> for writing), if you don't want to wait a long time to get a value.
> 
> Imagine I go to the store at 11am and can't buy soap - because
> the price of soap is unknown because it's a heavy trading day for soap 
> at the ny stock exchange. Even if the shop's definition may not allow for
> soap to be sold before the stock market closes and the final price is
> known, I shouldn't be forced to wait there doing nothing. I can do 
> other shopping around and come back later for my soap!  ;-)

Yes, I can see why having such a facility would be nice.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Mon, 31 May 1999 14:43:15 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] please?
> Bruce Momjian wrote:
> > 
> > I don't know of any SQL databases that allow non-blocking lock requests.
> > 
> 
> Oracle OCI has oopt() and Informix Online has dirty read that do the trick for
> me.
> --------

Please give me more information.  How does dirty read fix the problem?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Mon, 31 May 1999 14:45:42 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] please?
Bruce Momjian wrote:
> 
> > Bruce Momjian wrote:
> > >
> > > I don't know of any SQL databases that allow non-blocking lock requests.
> > >
> >
> > Oracle OCI has oopt() and Informix Online has dirty read that do the trick for
> > me.
> > --------
> 
> Please give me more information.  How does dirty read fix the problem?

It allows me to read uncommited records without blocking.

--------
Regards
Theo

Mon, 31 May 1999 20:58:08 +0200
Theo Kramer <theo@flame.co.za>
Re: [HACKERS] please?
> > > Oracle OCI has oopt() and Informix Online has dirty read that do the trick for
> > > me.
> > > --------
> > 
> > Please give me more information.  How does dirty read fix the problem?
> 
> It allows me to read uncommited records without blocking.

I suppose it somehow lets you know whether the read was dirty or
clean...

Mon, 31 May 1999 15:05:53 -0400 (EDT)
Pablo Funes <pablo@cs.brandeis.edu>
Re: [HACKERS] please?
> Bruce Momjian wrote:
> > 
> > > Bruce Momjian wrote:
> > > >
> > > > I don't know of any SQL databases that allow non-blocking lock requests.
> > > >
> > >
> > > Oracle OCI has oopt() and Informix Online has dirty read that do the trick for
> > > me.
> > > --------
> > 
> > Please give me more information.  How does dirty read fix the problem?
> 
> It allows me to read uncommited records without blocking.

Yes, but that does not solve his problem.  He wants a single lock, and
wants to test the lock, and immediately return if the lock fails.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Mon, 31 May 1999 15:10:55 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] please?
> > > > Oracle OCI has oopt() and Informix Online has dirty read that do the trick for
> > > > me.
> > > > --------
> > > 
> > > Please give me more information.  How does dirty read fix the problem?
> > 
> > It allows me to read uncommited records without blocking.
> 
> I suppose it somehow lets you know whether the read was dirty or
> clean...

Not that I am aware of.  Never heard that of Informix.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Mon, 31 May 1999 15:27:25 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] please?
> > It allows me to read uncommited records without blocking.
> 
> Yes, but that does not solve his problem.  He wants a single lock, and
> wants to test the lock, and immediately return if the lock fails.
> 

If you know the read was dirty, you know there was somebody else
locking/writing the table or record, it's locked, you failed to lock. 

Of course you should be able to aquire the lock in the same atomic
operation... 


...Pablo


Mon, 31 May 1999 15:27:58 -0400 (EDT)
Pablo Funes <pablo@cs.brandeis.edu>
Re: [HACKERS] please?
Bruce Momjian wrote:
> Sharing file systems.  Good point.  You could have a table you use to
> lock.  Lock the table, view the value, possibly modify, and unlock.
> This does not handle the case where someone died and did not remove
> their entry from the lock table.

You can always write the modification time to the table as well and if 
it's "too old", then try to override it.

-------
 Hannu

Mon, 31 May 1999 22:51:06 +0300
Hannu Krosing <hannu@trust.ee>
Re: [HACKERS] please?
Bruce Momjian wrote:
> 
> > > > > Oracle OCI has oopt() and Informix Online has dirty read that do the trick for
> > > > > me.
> > > > > --------
> > > >
> > > > Please give me more information.  How does dirty read fix the problem?
> > >
> > > It allows me to read uncommited records without blocking.
> >
> > I suppose it somehow lets you know whether the read was dirty or
> > clean...
> 
> Not that I am aware of.  Never heard that of Informix.

I also cheat. I use a 3 buffer approach, compare fields and see if a record
has
changed before I do an update.
--------
Regards
Theo

Mon, 31 May 1999 22:01:30 +0200
Theo Kramer <theo@flame.co.za>
Re: [HACKERS] please?
> Bruce Momjian wrote:
> > 
> > > > > > Oracle OCI has oopt() and Informix Online has dirty read that do the trick for
> > > > > > me.
> > > > > > --------
> > > > >
> > > > > Please give me more information.  How does dirty read fix the problem?
> > > >
> > > > It allows me to read uncommited records without blocking.
> > >
> > > I suppose it somehow lets you know whether the read was dirty or
> > > clean...
> > 
> > Not that I am aware of.  Never heard that of Informix.
> 
> I also cheat. I use a 3 buffer approach, compare fields and see if a record
> has
> changed before I do an update.

Oh, now we get the full picture.  :-)

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Mon, 31 May 1999 16:12:44 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] please?
> Bruce Momjian wrote:
> > Sharing file systems.  Good point.  You could have a table you use to
> > lock.  Lock the table, view the value, possibly modify, and unlock.
> > This does not handle the case where someone died and did not remove
> > their entry from the lock table.
> 
> You can always write the modification time to the table as well and if 
> it's "too old", then try to override it.
> 

Assuming you can set a reasonable "too old" time. 

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Mon, 31 May 1999 19:28:22 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] please?
> 
> > Bruce Momjian wrote:
> > > Sharing file systems.  Good point.  You could have a table you use to
> > > lock.  Lock the table, view the value, possibly modify, and unlock.
> > > This does not handle the case where someone died and did not remove
> > > their entry from the lock table.
> > 
> > You can always write the modification time to the table as well and if 
> > it's "too old", then try to override it.
> > 
> 
> Assuming you can set a reasonable "too old" time. 
> 

There may be many partial workarounds, depending on the
application, but there seems to be no robust way to have 
a failed lock right now. Perhaps in a future version will 
PQrequestCancel be able to terminate a waiting-for-lock 
state? 

Pablo


Mon, 31 May 1999 19:40:53 -0400 (EDT)
Pablo Funes <pablo@cs.brandeis.edu>
Re: [HACKERS] please?
Pablo Funes <pablo@cs.brandeis.edu> writes:
> Perhaps in a future version will PQrequestCancel be able to terminate
> a waiting-for-lock state?

Seems like a reasonable suggestion.  It's too late to consider this for
6.5 (we were supposed to freeze the feature list quite a while back)
but I support putting it on the TODO list for a future release.

			regards, tom lane

Mon, 31 May 1999 20:05:56 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] please? 
> Pablo Funes <pablo@cs.brandeis.edu> writes:
> > Perhaps in a future version will PQrequestCancel be able to terminate
> > a waiting-for-lock state?
> 
> Seems like a reasonable suggestion.  It's too late to consider this for
> 6.5 (we were supposed to freeze the feature list quite a while back)
> but I support putting it on the TODO list for a future release.
> 
> 			regards, tom lane
> 

Added:

	* Allow PQrequestCancel() to terminate when in waiting-for-lock state

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Mon, 31 May 1999 20:07:43 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] please?
Tom Lane wrote:
> 
> Pablo Funes <pablo@cs.brandeis.edu> writes:
> > Is it possible to do a nonblocking lock?
> 
> There is no way to do that in 6.4.  I am not sure whether the MVCC
> additions in 6.5 provide a way to do it or not (Vadim?).

I want to have it in later versions.

At the moment try to use contrib/userlock/

> 
> > NOTE: I tried using PQrequestCancel but it won't
> > cancel the request. It still blocks for as long

And this is bug that should be fixed... after 6.5

Vadim

Tue, 01 Jun 1999 09:56:32 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] please?
Theo Kramer wrote:
> 
> Bruce Momjian wrote:
> >
> > > Bruce Momjian wrote:
> > > >
> > > > I don't know of any SQL databases that allow non-blocking lock requests.
> > > >
> > >
> > > Oracle OCI has oopt() and Informix Online has dirty read that do the trick for
> > > me.
> > > --------
> >
> > Please give me more information.  How does dirty read fix the problem?
> 
> It allows me to read uncommited records without blocking.

I plan to implement it in 6.6

Vadim

Tue, 01 Jun 1999 10:10:51 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] please?
Vadim Mikheev wrote:
> > It allows me to read uncommited records without blocking.
> 
> I plan to implement it in 6.6

That's the best thing I've heard so far. I will then be able to use
postgres for my interactive applications :-).
--------
Regards
Theo

Tue, 01 Jun 1999 08:41:53 +0200
Theo Kramer <theo@flame.co.za>
Re: [HACKERS] please?
Theo Kramer wrote:
> 
> Vadim Mikheev wrote:
> > > It allows me to read uncommited records without blocking.
> >
> > I plan to implement it in 6.6
> 
> That's the best thing I've heard so far. I will then be able to use
> postgres for my interactive applications :-).

How about savepoints? -:)
And implicit savepoint before executing a query, like one in Oracle?

Vadim

Tue, 01 Jun 1999 16:03:35 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] please?
Vadim Mikheev wrote:
> 
> How about savepoints? -:)
> And implicit savepoint before executing a query, like one in Oracle?

That would be the cherry on the top.
--------
Regards
Theo

Tue, 01 Jun 1999 10:13:44 +0200
Theo Kramer <theo@flame.co.za>
Re: [HACKERS] please?
> > There is no way to do that in 6.4.  I am not sure whether the MVCC
> > additions in 6.5 provide a way to do it or not (Vadim?).
> 
> I want to have it in later versions.
> 
> At the moment try to use contrib/userlock/
> 

AHA! It looks like this solves my problem, at least for now,
until an official way to do nonblocking locs shows up on a 
future release.  
Here's what contrib/userlock/user_locks.doc says: 

  select some_fields, user_write_lock_oid(oid) from table where id='key';

  Now if the returned user_write_lock_oid field is 1 you have acquired an
  user lock on the oid of the selected tuple and can now do some long operation
  on it, like let the data being edited by the user.

  If it is 0 it means that the lock has been already acquired by some other
  process and you should not use that item until the other has finished.

  [...]

  update table set some_fields where id='key';
  select user_write_unlock_oid(oid) from table where id='key';

  [...]

  This could also be done by setting a flag in the record itself but in
  this case you have the overhead of the updates to the records and there
  could be some locks not released if the backend or the application crashes
  before resetting the lock flag.

  It could also be done with a begin/end block but in this case the entire
  table would be locked by postgres and it is not acceptable to do this for
  a long period because other transactions would block completely.


Tue, 1 Jun 1999 12:09:36 -0400 (EDT)
Pablo Funes <pablo@cs.brandeis.edu>
Re: [HACKERS] please?

Added to TODO:

	* PQrequestCancel() be able to terminate backend waiting for lock

> Pablo Funes <pablo@cs.brandeis.edu> writes:
> > Perhaps in a future version will PQrequestCancel be able to terminate
> > a waiting-for-lock state?
> 
> Seems like a reasonable suggestion.  It's too late to consider this for
> 6.5 (we were supposed to freeze the feature list quite a while back)
> but I support putting it on the TODO list for a future release.
> 
> 			regards, tom lane
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

Wed, 7 Jul 1999 19:22:02 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] please?