On Thu, 2009-12-17 at 12:16 -0600, Kevin Grittner wrote:
> Tom Lane wrote:
>
> > After thinking a bit, I'd be inclined to add a new paragraph.
> > In particular, now that FOR UPDATE actually works in subqueries,
> > it'd be worth pointing out that you can add that to guard against
> > this type
On Thu, 2009-12-17 at 13:13 -0500, Tom Lane wrote:
> "Joshua D. Drake" writes:
> > What is needed here is a layman's context of what isolation modes are
> > good for what type of operation. Neither your explanation or Tom's is
> > particularly useful except to say, "Crap, I might be screwed but I
Tom Lane wrote:
> [a transaction] might have also changed some other row so that it
> now *does* satisfy WHERE, but we won't ever find that other row
> because in the query snapshot it doesn't pass the WHERE.
OK; got it. No way to fix that, really, without getting a fresh
snapshot and re-star
Robert Haas wrote:
> Don't get me wrong, I don't love the current behavior. (I don't
> have a competing proposal either.) But I think we want to
> describe it with precision, because there are also many cases
> where _it works fine_. Telling people when it works and when it
> doesn't work is a
"Kevin Grittner" writes:
> Some quick testing seems to show that for the rows on which we were
> blocking, all columns reflect all updates from the concurrent
> transaction on which we were waiting, including columns used in the
> WHERE clause. I'm not sure exactly what other tests might be
> nec
"Kevin Grittner" wrote:
> Tom Lane wrote:
>
>> Are we sure that's a precise and complete description? I don't
>> have a problem with putting a description just like that in the
>> docs, but I'm not yet convinced it's right.
>
> Well, I thought it was when I typed it. You mentioned referenci
On Thu, Dec 17, 2009 at 1:12 PM, Kevin Grittner
wrote:
> Robert Haas wrote:
>
>> I don't think that's any clearer, though it is more disparaging.
>> :-)
>
> It's certainly not my goal to knock PostgreSQL. The precise
> conditions in which an UPDATE or DELETE can view an inconsistent
> database s
Tom Lane wrote:
> Are we sure that's a precise and complete description? I don't
> have a problem with putting a description just like that in the
> docs, but I'm not yet convinced it's right.
Well, I thought it was when I typed it. You mentioned referencing
other columns in the updated rows
"Kevin Grittner" writes:
> ... The precise
> conditions in which an UPDATE or DELETE can view an inconsistent
> database state (and therefore potentially persist something based on
> that inconsistent state) are that it has a FROM clause and/or
> subqueries which reference data changed by a concu
Tom Lane wrote:
> After thinking a bit, I'd be inclined to add a new paragraph.
> In particular, now that FOR UPDATE actually works in subqueries,
> it'd be worth pointing out that you can add that to guard against
> this type of issue. Perhaps, after the "DELETE FROM website"
> example, we cou
"Joshua D. Drake" writes:
> What is needed here is a layman's context of what isolation modes are
> good for what type of operation. Neither your explanation or Tom's is
> particularly useful except to say, "Crap, I might be screwed but I don't
> know if I am... how do I find out?"
If we had a si
Robert Haas wrote:
> I don't think that's any clearer, though it is more disparaging.
> :-)
It's certainly not my goal to knock PostgreSQL. The precise
conditions in which an UPDATE or DELETE can view an inconsistent
database state (and therefore potentially persist something based on
that i
On Thu, Dec 17, 2009 at 1:05 PM, Tom Lane wrote:
> "Kevin Grittner" writes:
>> Tom Lane wrote:
>>> I'm not very sure what a clearer explanation would look like
>
>> As a stab at it, how about?:
>
>> This behavior makes Read Committed mode unsuitable for many UPDATE
>> or DELETE commands with joi
"Kevin Grittner" writes:
> Tom Lane wrote:
>> I'm not very sure what a clearer explanation would look like
> As a stab at it, how about?:
> This behavior makes Read Committed mode unsuitable for many UPDATE
> or DELETE commands with joins or subqueries
After thinking a bit, I'd be inclined t
On Thu, 2009-12-17 at 12:58 -0500, Robert Haas wrote:
> On Thu, Dec 17, 2009 at 12:51 PM, Kevin Grittner
> wrote:
> > Tom Lane wrote:
> >
> >> I'm not very sure what a clearer explanation would look like
> >
> > As a stab at it, how about?:
> >
> > This behavior makes Read Committed mode unsuitab
On Thu, Dec 17, 2009 at 12:51 PM, Kevin Grittner
wrote:
> Tom Lane wrote:
>
>> I'm not very sure what a clearer explanation would look like
>
> As a stab at it, how about?:
>
> This behavior makes Read Committed mode unsuitable for many UPDATE
> or DELETE commands with joins or subqueries
I don'
Greg Stark writes:
> I wonder if RETURNING hasn't created a whole new set of cases where
> our READ COMMITTED behaviour is bogus.
I don't see how. It just gives you access to the same values that were
actually used by the UPDATE.
regards, tom lane
--
Sent via pgsql-hac
Tom Lane wrote:
> I'm not very sure what a clearer explanation would look like
As a stab at it, how about?:
This behavior makes Read Committed mode unsuitable for many UPDATE
or DELETE commands with joins or subqueries
-Kevin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgres
On Thu, Dec 17, 2009 at 5:39 PM, Tom Lane wrote:
> Well, it would all depend on what you're trying to do. Typical
> single-row UPDATE commands aren't really affected by this problem,
> and in fact the behavior is pretty much exactly what they want as
> long as the WHERE conditions don't involve c
"Kevin Grittner" writes:
> Tom Lane wrote:
>> http://developer.postgresql.org/pgdocs/postgres/transaction-iso.html#XACT-READ-COMMITTED
> I don't know how many times I've read that page (many), yet I never
> properly comprehended the impact of that part. I think the last bit
> I quoted above is
Tom Lane wrote:
> "Kevin Grittner" writes:
>> the behavior under READ COMMITTED could be astonishing in certain
>> circumstances as it breaks atomicity:
>
> Yup. That is stated fairly clearly already in the description of
> READ COMMITTED mode, no?
>
http://developer.postgresql.org/pgdocs/postg
"Kevin Grittner" writes:
> Thanks for the clarification. That does not work for SERIALIZABLE
> at all, because other tables or rows referenced in that first
> statement would be using the original snapshot. Indeed, the
> behavior under READ COMMITTED could be astonishing in certain
> circumstanc
Tom Lane wrote:
> "Kevin Grittner" writes:
>> we would instead get a fresh snapshot and retry -- which is what
>> we do in a READ COMMITTED transaction.
> I think you misunderstand how READ COMMITTED works; it does not
> change the snapshot for the entire statement, it only follows the
> update
On Thu, Dec 17, 2009 at 3:39 PM, Kevin Grittner
wrote:
> Basically, in a SERIALIZABLE transaction, if the first statement
> which would require a snapshot would currently fail with "ERROR:
> could not serialize access due to concurrent update" we would
> instead get a fresh snapshot and retry -- w
"Kevin Grittner" writes:
> Basically, in a SERIALIZABLE transaction, if the first statement
> which would require a snapshot would currently fail with "ERROR:
> could not serialize access due to concurrent update" we would
> instead get a fresh snapshot and retry -- which is what we do in a
> REA
Robert Haas wrote:
> Kevin Grittner wrote:
>> "Markus Wanner" wrote:
>>
>>> Another line of thought: isn't this like READ COMMITTED for just
>>> the first operation in a SERIALIZABLE transaction?
>>
>> I've mulled it over and I have two different logical proofs that
>> this is safe; if anyone is
On Thu, Dec 17, 2009 at 10:05 AM, Kevin Grittner
wrote:
> "Markus Wanner" wrote:
>
>> Another line of thought: isn't this like READ COMMITTED for just
>> the first operation in a SERIALIZABLE transaction?
>
> I've mulled it over and I have two different logical proofs that
> this is safe; if anyo
"Markus Wanner" wrote:
> Another line of thought: isn't this like READ COMMITTED for just
> the first operation in a SERIALIZABLE transaction?
I've mulled it over and I have two different logical proofs that
this is safe; if anyone is dubious I'd be happy to share.
This seems likely to be of
"Markus Wanner" wrote:
> Another line of thought: isn't this like READ COMMITTED for just
> the first operation in a SERIALIZABLE transaction?
Hmmm... You have a point.
-Kevin
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://
Tom Lane wrote:
> "Kevin Grittner" writes:
>> Tom Lane wrote:
>>> (Besides which the lock acquired by UPDATE isn't exclusive and
>>> wouldn't block anyway...)
>> It blocks other UPDATEs.
> Not at the table level.
The question was about whether we could change the timing of when
we get the
Hi,
Quoting "Tom Lane" :
Not at the table level. If you could lock only at the tuple level
maybe you'd have something
AFAIUI this is about the tuple level lock, yes.
but it seems like you can't find the
target tuples without having acquired a snapshot.
Maybe not *the* target tuple, but we
"Kevin Grittner" writes:
> Tom Lane wrote:
>> (Besides which the lock acquired by UPDATE isn't exclusive and
>> wouldn't block anyway...)
> It blocks other UPDATEs.
Not at the table level. If you could lock only at the tuple level
maybe you'd have something, but it seems like you can't find t
Tom Lane wrote:
> With the code you give, the snapshot is acquired at the beginning
> of processing the UPDATE command, before it finds out that the
> target is t2a and acquires a lock on it.
Right. The optimization would be to wait to determine the snapshot
for the UPDATE statement until we
"Kevin Grittner" writes:
> Tom Lane wrote:
>> Yes --- it's not an "optimization", it's necessary for basic
>> functionality to work correctly.
> Hmmm... Testing seems to indicate that this doesn't work per the
> described optimization:
You'd need an explicit LOCK TABLE t2a after starting the
Tom Lane wrote:
> "Kevin Grittner" writes:
>> The Cahill thesis mentions an interesting optimization -- they
>> defer determination of the snapshot until after any locks
>> required for the first statement have been acquired. Where the
>> first statement was, for example, an UPDATE, this reduced
"Kevin Grittner" writes:
> The Cahill thesis mentions an interesting optimization -- they defer
> determination of the snapshot until after any locks required for the
> first statement have been acquired. Where the first statement was,
> for example, an UPDATE, this reduced re-reads or rollbacks
The Cahill thesis mentions an interesting optimization -- they defer
determination of the snapshot until after any locks required for the
first statement have been acquired. Where the first statement was,
for example, an UPDATE, this reduced re-reads or rollbacks in the
face of concurrent modifica
37 matches
Mail list logo
