Read Lock For Foreign Key

Jon Swinth wrote:

How come having foreign keys take out a read lock on a parent row rather than
a write lock is not on the todo list? I had someone tell me that this is
difficult because the SQL standard does not include syntax for read lock.
Does the fact that it will be difficult mean that it isn't needed?

The problem is that the row has to be locked against concurrent updates
until the end of the transaction. As of now, the only way to do that
under PostgreSQLs MVCC is to grab a lock FOR UPDATE, which is exclusive.

The fact that it is difficult does not mean it isn't needed. It is
simply the reason why it hasn't been done yet.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #

On Tue, 13 Aug 2002, Jon Swinth wrote:

How come having foreign keys take out a read lock on a parent row rather than
a write lock is not on the todo list? I had someone tell me that this is
difficult because the SQL standard does not include syntax for read lock.
Does the fact that it will be difficult mean that it isn't needed?

Well, dealing with the locking may not be on the official TODO list but
it's on mine but I don't have alot of time to work on stuff so it's in the
eventual future. Just switching to a read lock may not actually fix some
of the deadlock issues that could be solved by switching up some details
of how the triggers work. But, each solution takes a bunch of time to
check possible failure cases either direction (towards deadlock or towards
an unsatisfied constraint).

Thanks for your reply Jan. I fully understand that the difficulty is why is
has not already been done. I was simply trying to take the first step in
getting something done in a software project... getting it on the todo list.

Thanks for your reply Stephan. I am glad that you have placed this on your
own todo list. I am not a fan of using triggers for foreign key constraints.
I think foreign keys are too much an integrel part of the DB to be simple
trigger code. It also make the description of tables rather messy. However,
I am not in a place to complain about how things are implemented because I
cannot help with an alternative.

I do hope we can get this on the official todo list so that everyone agrees
that it is needed.

Jon Swinth <jswinth@atomicpc.com> writes:

How come having foreign keys take out a read lock on a parent row
rather than a write lock is not on the todo list?

It is, although I agree that the language is something other than plain
English:

* Implement dirty reads and use them in RI triggers

regards, tom lane

Tom Lane wrote:

Jon Swinth <jswinth@atomicpc.com> writes:

How come having foreign keys take out a read lock on a parent row
rather than a write lock is not on the todo list?

It is, although I agree that the language is something other than plain
English:

* Implement dirty reads and use them in RI triggers

I was wondering if that is what they were discussing.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thanks for you comment Tom. I don't see how dirty reads have anything to do
with foreign keys. Maybe I don't understand what a dirty read is in this
context.