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 fr
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, y
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 t
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
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 th
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
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
> > >
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
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 mean
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
"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
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 blocki
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 p
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 u
"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 othe
"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 concurren
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 ta
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 co
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 stateme
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 AccessEx
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
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 ta
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?
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
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, ev
"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
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 appea
"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
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
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
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 referenc
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 ta
> 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
> pr
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 ro
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 UP
36 matches
Mail list logo
