I've got this new notify code almost working, but...

What exactly is the protocol for locking a table that you intend to
modify?  The old notify code just did RelationSetLockForWrite(),
but it's not clear to me that that works correctly --- for one thing,
it doesn't seem to offer any way of detecting failure to acquire the
lock.  (RelationSetLockForWrite calls MultiLockReln, which *does*
return a boolean, but RelationSetLockForWrite ignores it...)  Also,
it's not at all clear whether I should call RelationUnsetLockForWrite
at the end of the routine or not; some existing code does, some doesn't.

I'm concerned because interlocking of the specialized NOTIFY-related
statements seems to work fine, but they seem not to be interlocked
against user operations on the pg_listener table.

For example, this works as I'd expect:

	psql#1				psql#2

	begin;
	listen z;

					notify z;
					(hangs up until #1 commits)

	end;

because "listen" acquires a write lock on the pg_listener table, which
the notify has to wait for.

But this doesn't work as I'd expect:

	psql#1				psql#2

	begin;
	select * from pg_listener;

					notify z;
					(completes immediately)

	end;

Seems to me the "select" should acquire a read lock that would prevent
the #2 backend from writing pg_listener until the end of #1's transaction.

Is there a bug here, or is there some special definition of user access
to a system table that means the select isn't acquiring a read lock?
Selects and updates on ordinary user tables seem to interlock fine...

			regards, tom lane

Sun, 27 Sep 1998 17:34:33 -0400
Tom Lane <tgl@sss.pgh.pa.us>
It sorta works, but I'm confused about locking
> 
> I've got this new notify code almost working, but...

Could you plase send it to me ?

> What exactly is the protocol for locking a table that you intend to
> modify?  The old notify code just did RelationSetLockForWrite(),
> but it's not clear to me that that works correctly --- for one thing,
> it doesn't seem to offer any way of detecting failure to acquire the
> lock.  (RelationSetLockForWrite calls MultiLockReln, which *does*
> return a boolean, but RelationSetLockForWrite ignores it...)  Also,
> it's not at all clear whether I should call RelationUnsetLockForWrite
> at the end of the routine or not; some existing code does, some doesn't.

It is not done where there is an immediate CommitTransactionCommand which
already releases the locks.

> I'm concerned because interlocking of the specialized NOTIFY-related
> statements seems to work fine, but they seem not to be interlocked
> against user operations on the pg_listener table.
> 
> For example, this works as I'd expect:
> 
> 	psql#1				psql#2
> 
> 	begin;
> 	listen z;
> 
> 					notify z;
> 					(hangs up until #1 commits)
> 
> 	end;
> 
> because "listen" acquires a write lock on the pg_listener table, which
> the notify has to wait for.
> 
> But this doesn't work as I'd expect:
> 
> 	psql#1				psql#2
> 
> 	begin;
> 	select * from pg_listener;
> 
> 					notify z;
> 					(completes immediately)
> 
> 	end;
> 
> Seems to me the "select" should acquire a read lock that would prevent
> the #2 backend from writing pg_listener until the end of #1's transaction.
> 
> Is there a bug here, or is there some special definition of user access
> to a system table that means the select isn't acquiring a read lock?
> Selects and updates on ordinary user tables seem to interlock fine...

Very strange, it seems a bug. But users should not know about pg_listener.

-- 
Massimo Dal Zotto

+----------------------------------------------------------------------+
|  Massimo Dal Zotto                email:  dz@cs.unitn.it             |
|  Via Marconi, 141                 phone:  ++39-461-534251            |
|  38057 Pergine Valsugana (TN)     www:  http://www.cs.unitn.it/~dz/  |
|  Italy                            pgp:  finger dz@tango.cs.unitn.it  |
+----------------------------------------------------------------------+

Mon, 28 Sep 1998 18:46:58 +0200 (MET DST)
Massimo Dal Zotto <dz@cs.unitn.it>
Re: [HACKERS] It sorta works, but I'm confused about locking
Massimo Dal Zotto <dz@cs.unitn.it> writes:
>> it's not at all clear whether I should call RelationUnsetLockForWrite
>> at the end of the routine or not; some existing code does, some doesn't.

> It is not done where there is an immediate CommitTransactionCommand which
> already releases the locks.

Hmm.  OK, but I guess I don't really understand why it's ever valid to
release a lock before the end of the transaction --- and
CommitTransactionCommand isn't necessarily the end of the transaction
anyway, if you're inside a transaction block.

Basically: it seems to me it's a bug to call UnsetLock (any flavor)
from *anywhere* except transaction commit.  If this is not so, why not?

			regards, tom lane

Mon, 28 Sep 1998 14:09:06 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] It sorta works, but I'm confused about locking 
> I've got this new notify code almost working, but...
> 
> What exactly is the protocol for locking a table that you intend to
> modify?  The old notify code just did RelationSetLockForWrite(),
> but it's not clear to me that that works correctly --- for one thing,
> it doesn't seem to offer any way of detecting failure to acquire the
> lock.  (RelationSetLockForWrite calls MultiLockReln, which *does*
> return a boolean, but RelationSetLockForWrite ignores it...)  Also,
> it's not at all clear whether I should call RelationUnsetLockForWrite
> at the end of the routine or not; some existing code does, some doesn't.

Welcome to PostgreSQL.  This is the type of thing I saw when doing the
mega-patch, were people were not doing things consistently, because some
coders do not understand what is happening inside the code, and just
write something that works, sort of.

I recommend you check out what is currently done properly, and fix the
ones that are incorrect.

I can imagine some cases where you would want to get a lock and keep it
until the end of the transaction, and other times when you would want to
release it before transaction end.


> 
> I'm concerned because interlocking of the specialized NOTIFY-related
> statements seems to work fine, but they seem not to be interlocked
> against user operations on the pg_listener table.
> 
> For example, this works as I'd expect:
> 
> 	psql#1				psql#2
> 
> 	begin;
> 	listen z;
> 
> 					notify z;
> 					(hangs up until #1 commits)
> 
> 	end;
> 
> because "listen" acquires a write lock on the pg_listener table, which
> the notify has to wait for.
> 
> But this doesn't work as I'd expect:
> 
> 	psql#1				psql#2
> 
> 	begin;
> 	select * from pg_listener;
> 
> 					notify z;
> 					(completes immediately)
> 
> 	end;
> 
> Seems to me the "select" should acquire a read lock that would prevent
> the #2 backend from writing pg_listener until the end of #1's transaction.
> 
> Is there a bug here, or is there some special definition of user access
> to a system table that means the select isn't acquiring a read lock?
> Selects and updates on ordinary user tables seem to interlock fine...

Select certainly should be locking.  Something is wrong, but I am not
sure what.  If you want me to check into it, let me know.


-- 
  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


Thu, 1 Oct 1998 17:29:42 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] It sorta works, but I'm confused about locking
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> I recommend you check out what is currently done properly, and fix the
> ones that are incorrect.

Well, yes.  The question was how to tell which is which :-)

> I can imagine some cases where you would want to get a lock and keep it
> until the end of the transaction, and other times when you would want to
> release it before transaction end.

I guess I'm not understanding something.  How can it ever be correct
practice to release a lock before transaction end?  For example, if I
write some changes in a table, and then release the lock, wouldn't that
allow other backends to see the not-yet-committed changes?  What if I
then abort my transaction?  Now the other backends have acted on
information they should never have seen at all.

Releasing a read lock strikes me as just as dangerous but for more
subtle reasons --- once you have read a table, what you have read
ought to look the same until the end of your transaction.

Since there is an unset-write-lock function, I assume it must have
valid uses, but I don't see what they are.

>> Is there a bug here, or is there some special definition of user access
>> to a system table that means the select isn't acquiring a read lock?
>> Selects and updates on ordinary user tables seem to interlock fine...

> Select certainly should be locking.  Something is wrong, but I am not
> sure what.  If you want me to check into it, let me know.

Please.  (Note that I saw this with my revised version of async.c;
I believe you will see the same behavior with the currently-checked-in
code, but do not have the time to rebuild that version to make sure.)

			regards, tom lane

Thu, 01 Oct 1998 20:25:15 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] It sorta works, but I'm confused about locking 
Tom Lane wrote:
> 
> > I can imagine some cases where you would want to get a lock and keep it
> > until the end of the transaction, and other times when you would want to
> > release it before transaction end.
> 
> I guess I'm not understanding something.  How can it ever be correct
> practice to release a lock before transaction end?  For example, if I
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Postgres does this for system tables only.

> write some changes in a table, and then release the lock, wouldn't that
> allow other backends to see the not-yet-committed changes?  What if I
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
No. Backend uses xmin/xmax to know is tuple visible or not.

> then abort my transaction?  Now the other backends have acted on
> information they should never have seen at all.

Backend will see that xmin(xmax) is not committed.

Vadim

Fri, 02 Oct 1998 09:20:25 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] It sorta works, but I'm confused about locking
Vadim Mikheev <vadim@krs.ru> writes:
> Tom Lane wrote:
>> I guess I'm not understanding something.  How can it ever be correct
>> practice to release a lock before transaction end?  For example, if I
>> write some changes in a table, and then release the lock, wouldn't that
>> allow other backends to see the not-yet-committed changes?  What if I
>                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
> No. Backend uses xmin/xmax to know is tuple visible or not.

Ah, some light dawns.  Thanks for the clue!

> Postgres does this for system tables only.

OK, so what is good coding practice?  Always release write lock after
operating on a system table?  Or is that only good some of the time,
and if so what's the consideration?

			regards, tom lane

Thu, 01 Oct 1998 21:47:13 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] It sorta works, but I'm confused about locking 
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > I recommend you check out what is currently done properly, and fix the
> > ones that are incorrect.
> 
> Well, yes.  The question was how to tell which is which :-)

I didn't say it was easy.  :-)

> 
> > I can imagine some cases where you would want to get a lock and keep it
> > until the end of the transaction, and other times when you would want to
> > release it before transaction end.
> 
> I guess I'm not understanding something.  How can it ever be correct
> practice to release a lock before transaction end?  For example, if I
> write some changes in a table, and then release the lock, wouldn't that
> allow other backends to see the not-yet-committed changes?  What if I
> then abort my transaction?  Now the other backends have acted on
> information they should never have seen at all.
> 
> Releasing a read lock strikes me as just as dangerous but for more
> subtle reasons --- once you have read a table, what you have read
> ought to look the same until the end of your transaction.
> 
> Since there is an unset-write-lock function, I assume it must have
> valid uses, but I don't see what they are.

Suppose you want to update a table as part of internal query processing.
You lock it, update it, and unlock it.  You just need the lock while
you are updating it.

> 
> >> Is there a bug here, or is there some special definition of user access
> >> to a system table that means the select isn't acquiring a read lock?
> >> Selects and updates on ordinary user tables seem to interlock fine...
> 
> > Select certainly should be locking.  Something is wrong, but I am not
> > sure what.  If you want me to check into it, let me know.
> 
> Please.  (Note that I saw this with my revised version of async.c;
> I believe you will see the same behavior with the currently-checked-in
> code, but do not have the time to rebuild that version to make sure.)


-- 
  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


Thu, 1 Oct 1998 22:24:20 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] It sorta works, but I'm confused about locking