Added to TODO:
* Allow UPDATEs on only non-referential integrity columns not to
conflict
with referential integrity locks
http://archives.postgresql.org/pgsql-hackers/2007-02/msg00073.php
On 2/8/2007 2:46 PM, Marc Munro wrote:
On Thu, 2007-08-02 at 14:33 -0500, Tom Lane wrote:
Marc Munro [EMAIL PROTECTED] writes:
Yes in this case, T1 must abort because the record it was going to
update has disappeared from underneath it. I don't see how this is
significantly different from
Oops, forgot to include pgsql-hackers when I responded to this the first
time.
On Tue, 2007-06-02 at 20:53 -0500, Tom Lane wrote:
Marc Munro [EMAIL PROTECTED] writes:
The RI triggers currently fire when a record is updated. Under my
proposal they would fire in the same way but before the
On Thu, 8 Feb 2007, Marc Munro wrote:
Oops, forgot to include pgsql-hackers when I responded to this the first
time.
On Tue, 2007-06-02 at 20:53 -0500, Tom Lane wrote:
Marc Munro [EMAIL PROTECTED] writes:
The RI triggers currently fire when a record is updated. Under my
proposal
On Thu, 2007-08-02 at 10:06 -0800, Stephan Szabo wrote:
On Thu, 8 Feb 2007, Marc Munro wrote:
. . .
That other transaction, T1, would have run the same RI triggers and so
would have the same parent records locked.
That's not true in the case of delete, since the referencing table
Marc Munro [EMAIL PROTECTED] writes:
Yes in this case, T1 must abort because the record it was going to
update has disappeared from underneath it. I don't see how this is
significantly different from the same race for the record if the table
had no RI constraints. The only difference that I
On Thu, 2007-08-02 at 14:33 -0500, Tom Lane wrote:
Marc Munro [EMAIL PROTECTED] writes:
Yes in this case, T1 must abort because the record it was going to
update has disappeared from underneath it. I don't see how this is
significantly different from the same race for the record if the
On Thu, 8 Feb 2007, Marc Munro wrote:
On Thu, 2007-08-02 at 10:06 -0800, Stephan Szabo wrote:
On Thu, 8 Feb 2007, Marc Munro wrote:
. . .
That other transaction, T1, would have run the same RI triggers and so
would have the same parent records locked.
That's not true in the case
On Thu, 2007-08-02 at 12:24 -0800, Stephan Szabo wrote:
On Thu, 8 Feb 2007, Marc Munro wrote:
I don't think this does stop the second from continuing before the
first. What will stop it, is the eventual lock that is taken on the
child (triggering) record.
But at that point, you've
On Mon, 2007-02-05 at 23:25 +, Gregory Stark wrote:
Gregory Stark [EMAIL PROTECTED] writes:
Bruce Momjian [EMAIL PROTECTED] writes:
OK, please propose some wording so at least we can get agreement on
that.
How about something open-ended like arrange for updates that do not
Simon Riggs wrote:
On Sat, 2007-02-03 at 09:43 -0800, Stephan Szabo wrote:
On Sat, 3 Feb 2007, Simon Riggs wrote:
On Fri, 2007-02-02 at 16:50 -0500, Tom Lane wrote:
No, I don't. I think knowledge of which columns are in a PK is quite a
few levels away from the semantics of row locking. To
Simon Riggs started this thread with the question:
. . .
Why do we need a SHARE lock at all, on the **referenc(ed)** table?
. . .
The root problem addressed by this thread seems to be that using share
locks in this way increases the likelihood of deadlock, and causes
blocking when no
Marc Munro [EMAIL PROTECTED] writes:
Proposal 1: Alter the way RI triggers fire, so that they complete before
locking the row against which they fire.
It's kind of hard to know what records the user will choose to update before
he actually does the update...
Proposal 2: Lock the index
On Tue, 2007-06-02 at 23:47 +, Gregory Stark wrote:
Marc Munro [EMAIL PROTECTED] writes:
Proposal 1: Alter the way RI triggers fire, so that they complete before
locking the row against which they fire.
It's kind of hard to know what records the user will choose to update before
he
Marc Munro [EMAIL PROTECTED] writes:
The RI triggers currently fire when a record is updated. Under my
proposal they would fire in the same way but before the record is locked
rather than after. Or am I missing your point?
IOW, some other transaction could update or delete the tuple
On Sat, 3 Feb 2007, Simon Riggs wrote:
There are issues, yes. Dropping PKs is a very irregular occurrence nor
is it likely to be part of a complex transaction. It wouldn't bother me
to say that if a transaction already holds a RowExclusiveLock or a
RowShareLock it cannot upgrade to an
On Sun, 2007-02-04 at 09:38 +, Simon Riggs wrote:
The TODO I was requesting you consider was this:
Develop non-conflicting locking scheme to allow RI checks to co-exist
peacefully with non-PK UPDATEs on the referenced table.
That is, IMHO, a general statement of an important
Simon Riggs wrote:
It occurs to me that if we had visibility in unique indexes, this would
allow the index rows to be separately lockable to the main row. That's
exactly what we need here.
I've implemented a work-around using this principle, utilising RULEs and
a duplicated PK
On Mon, 5 Feb 2007, Simon Riggs wrote:
On Sun, 2007-02-04 at 09:38 +, Simon Riggs wrote:
The TODO I was requesting you consider was this:
Develop non-conflicting locking scheme to allow RI checks to co-exist
peacefully with non-PK UPDATEs on the referenced table.
That
Bruce Momjian [EMAIL PROTECTED] writes:
OK, please propose some wording so at least we can get agreement on
that.
How about something open-ended like arrange for updates that do not update
columns referenced by foreign keys from other tables to avoid being blocked by
locks from concurrent RI
Gregory Stark [EMAIL PROTECTED] writes:
Bruce Momjian [EMAIL PROTECTED] writes:
OK, please propose some wording so at least we can get agreement on
that.
How about something open-ended like arrange for updates that do not update
columns referenced by foreign keys from other tables to avoid
On Sat, 2007-02-03 at 09:43 -0800, Stephan Szabo wrote:
On Sat, 3 Feb 2007, Simon Riggs wrote:
On Fri, 2007-02-02 at 16:50 -0500, Tom Lane wrote:
No, I don't. I think knowledge of which columns are in a PK is quite a
few levels away from the semantics of row locking. To point out just
On Fri, 2007-02-02 at 16:50 -0500, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
On Fri, 2007-02-02 at 15:57 -0500, Tom Lane wrote:
, and it doesn't scale to
more than two holders, and I don't think it works for combinations of
share and exclusive lock. Also, what happened to the
On Sat, 3 Feb 2007, Simon Riggs wrote:
On Fri, 2007-02-02 at 16:50 -0500, Tom Lane wrote:
No, I don't. I think knowledge of which columns are in a PK is quite a
few levels away from the semantics of row locking. To point out just
one problem, what happens when you add or drop a PK? Or
On 2/2/2007 4:51 AM, Simon Riggs wrote:
It sounds like if we don't put a SHARE lock on the referenced table then
we can end the transaction in an inconsistent state if the referenced
table has concurrent UPDATEs or DELETEs. BUT those operations do impose
locking rules back onto the referencing
I'm reading the SQL Standard and I can't find anywhere that says that we
need to place SHARE locks on rows in the referenced table.
RI_FKey_check() clearly does that.
What I do see is this:
4. For each row of the referenced table, its matching rows, unique
matching rows, and non-unique matching
On Fri, 2007-02-02 at 10:51, Simon Riggs wrote:
[snip]
Why do we need a SHARE lock at all, on the **referenc(ed)** table?
It sounds like if we don't put a SHARE lock on the referenced table then
we can end the transaction in an inconsistent state if the referenced
table has concurrent
Csaba Nagy wrote:
On Fri, 2007-02-02 at 10:51, Simon Riggs wrote:
[snip]
Why do we need a SHARE lock at all, on the **referenc(ed)** table?
Well, here we do have a patch (deployed on production servers) which
does not put the shared lock on the referenced table, and it lets in
occasionally
You say below the cut that you're not updating keys, so presumably it's
other columns. Which leads me to something I've wondered for a while -
why do we lock the whole row? Is it just a matter of not optimised that
yet or is there a good reason why locking just some columns isn't
Csaba Nagy wrote:
The reason of the occasional orphan rows is not completely clear to me,
but it must be some kind of race condition while
inserting/deleting/?updating concurrently the parent/child tables.
I guess the following sequence would generate a orphaned row.
A: executes insert into
On Fri, 2 Feb 2007, Simon Riggs wrote:
It sounds like if we don't put a SHARE lock on the referenced table then
we can end the transaction in an inconsistent state if the referenced
table has concurrent UPDATEs or DELETEs. BUT those operations do impose
locking rules back onto the referencing
On Fri, 2007-02-02 at 12:01 +0100, Csaba Nagy wrote:
You say below the cut that you're not updating keys, so presumably it's
other columns. Which leads me to something I've wondered for a while -
why do we lock the whole row? Is it just a matter of not optimised that
yet or is there a
On Fri, 2007-02-02 at 10:35 -0800, Stephan Szabo wrote:
On Fri, 2 Feb 2007, Simon Riggs wrote:
It sounds like if we don't put a SHARE lock on the referenced table then
we can end the transaction in an inconsistent state if the referenced
table has concurrent UPDATEs or DELETEs. BUT those
Simon Riggs [EMAIL PROTECTED] writes:
Thus we have three types of write lock:
1. full row write lock
as well as two mutually exclusive groups of columns:
2.a) PK cols
2.b) all columns apart from the PK cols
This appears to require that we add enough fields to row headers to
represent *three*
On Fri, 2007-02-02 at 15:57 -0500, Tom Lane wrote:
Simon Riggs [EMAIL PROTECTED] writes:
Thus we have three types of write lock:
1. full row write lock
as well as two mutually exclusive groups of columns:
2.a) PK cols
2.b) all columns apart from the PK cols
This appears to require
Simon Riggs [EMAIL PROTECTED] writes:
On Fri, 2007-02-02 at 15:57 -0500, Tom Lane wrote:
, and it doesn't scale to
more than two holders, and I don't think it works for combinations of
share and exclusive lock. Also, what happened to the third type of lock?
Well, we just need to record the
Simon Riggs wrote:
My earlier thinking was that Oracle appears to be able to avoid locking
and my thought was that this was simply a rather dodgy interpretation of
the SQL Standard. Anyway, I'm not happy with simply forgetting the SHARE
lock; that clearly leads to invalid states in some cases,
37 matches
Mail list logo
