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

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

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

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

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

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

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