Jeff Davis wrote:
On Mon, 2009-01-12 at 13:35 -0500, Tom Lane wrote:
I think the behavior Lee is expecting is only implementable with a
full-table write lock, which is exactly what FOR UPDATE is designed
to avoid. There are certain properties you don't get with a partial
lock, and in the
Tom Lane wrote:
Kevin Grittner kevin.gritt...@wicourts.gov writes:
Tom Lane t...@sss.pgh.pa.us wrote:
A re-sort after locking doesn't really make things all nice and
intuitive either.
Would it make any sense to roll back and generate a
SERIALIZATION_FAILURE?
If that's what you want then
Kevin Grittner wrote:
Well, that's a PostgreSQL-specific point of view, although I
understand the point of maintaining that guarantee. (In Microsoft SQL
Server and Sybase ASE we actually had to run our read-only web
application at the READ UNCOMMITTED transaction isolation level
because so many
Peter Eisentraut pete...@gmx.net wrote:
Kevin Grittner wrote:
(In Microsoft SQL Server and Sybase ASE we actually had to run our
read-only web application at the READ UNCOMMITTED transaction
isolation level because so many SELECT queries were rolled back
when they deadlocked with the
Tom Lane t...@sss.pgh.pa.us wrote:
Kevin Grittner kevin.gritt...@wicourts.gov writes:
Would it make any sense to roll back and generate a
SERIALIZATION_FAILURE?
If that's what you want then you run the transaction in serializable
mode. The point of doing it in READ COMMITTED mode is that
Kevin Grittner kevin.gritt...@wicourts.gov writes:
Tom Lane t...@sss.pgh.pa.us wrote:
If that's what you want then you run the transaction in serializable
mode. The point of doing it in READ COMMITTED mode is that you
don't want such a failure.
Wait a minute -- there is not such guarantee
Tom Lane t...@sss.pgh.pa.us wrote:
Huh? Deadlocks were not the issue here. What you asked for was a
failure if someone else had updated the rows you're selecting for
update.
Logically, these are both forms of serialization failure when doing
SELECT FOR UPDATE in READ COMMITTED mode. One
Kevin,
So, wouldn't it be better, if it's actually feasible to detect the
problem situation, to make this another situation where SELECT FOR
UPDATE can cause serialization failures? That would allow
applications to count on getting the rows in the requested order if
the query completes
Josh Berkus j...@agliodbs.com wrote:
That's not how SELECT FOR UPDATE works. SFU is pessimistic manual
locking, which is supposed to *wait* for the rows to be exclusively
available. The deadlock timeout you encountered is the correct
behaviour, not serialization failure, which is what
Deadlocks like this are the only kind of serialization error possible
under traditional (non-MVCC) databases. These are much more rare in
MVCC than update conflicts, but that doesn't mean they aren't
serialization failures there, too. I think it is a violation of the
standard for PostgreSQL
Josh Berkus j...@agliodbs.com wrote:
we'd break 100,000 existing Java applications if we changed the
error.
In what way would an application want to treat deadlocks and update
conflicts differently? Both result from conflicts with concurrent
transactions and can be retried automatically.
Kevin Grittner kevin.gritt...@wicourts.gov writes:
Josh Berkus j...@agliodbs.com wrote:
we'd break 100,000 existing Java applications if we changed the
error.
In what way would an application want to treat deadlocks and update
conflicts differently? Both result from conflicts with
Gregory Stark st...@enterprisedb.com wrote:
Kevin Grittner kevin.gritt...@wicourts.gov writes:
In what way would an application want to treat deadlocks and update
conflicts differently? Both result from conflicts with concurrent
transactions and can be retried automatically. It seems like
Peter Eisentraut wrote:
On Tuesday 06 January 2009 02:03:14 Tom Lane wrote:
I don't think there's a bug here, at least not in the sense that it
isn't Operating As Designed. But it does seem like we could do with
some more/better documentation about exactly how FOR UPDATE works.
The sequence of
Peter Eisentraut pete...@gmx.net writes:
I can see two ways forward:
1) We document bluntly that ORDER BY + FOR UPDATE can return unordered
results, or
2) We prohibit ORDER BY + FOR UPDATE, like we do with a number of other
clauses. (There would be no loss of functionality, because you
Tom Lane t...@sss.pgh.pa.us writes:
Peter Eisentraut pete...@gmx.net writes:
I can see two ways forward:
1) We document bluntly that ORDER BY + FOR UPDATE can return unordered
results, or
2) We prohibit ORDER BY + FOR UPDATE, like we do with a number of other
clauses. (There would be
While this behavior may be very old, I would still contend that it is
incorrect (or at least inconsistent with one's expectations). If it will
not be changed, some additional documentation might be helpful. Perhaps
a WARNING could be raised (unconditionally, as it might be a bit
intensive to
On Mon, 2009-01-12 at 15:26 +0200, Peter Eisentraut wrote:
1) We document bluntly that ORDER BY + FOR UPDATE can return unordered
results, or
2) We prohibit ORDER BY + FOR UPDATE, like we do with a number of other
clauses. (There would be no loss of functionality, because you can run
On Mon, Jan 12, 2009 at 8:32 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Peter Eisentraut pete...@gmx.net writes:
I can see two ways forward:
1) We document bluntly that ORDER BY + FOR UPDATE can return unordered
results, or
2) We prohibit ORDER BY + FOR UPDATE, like we do with a number of other
On Mon, 2009-01-12 at 08:32 -0500, Tom Lane wrote:
That code has been working like this for eight or ten years now and this
is the first complaint, so taking away functionality on the grounds that
someone might happen to update the ordering column doesn't seem like the
answer to me.
If they
On Mon, 2009-01-12 at 12:47 -0500, Robert Haas wrote:
If the only case where ORDER BY + FOR UPDATE are not strictly
compatible is when the columns being updated are the same as the
columns of the sort, a blanket prohibition against using the two
together seems like it prohibits an awful lot of
Robert Haas robertmh...@gmail.com writes:
On Mon, Jan 12, 2009 at 8:32 AM, Tom Lane t...@sss.pgh.pa.us wrote:
That code has been working like this for eight or ten years now and this
is the first complaint, so taking away functionality on the grounds that
someone might happen to update the
Tom Lane t...@sss.pgh.pa.us wrote:
A re-sort after locking doesn't really make things all nice and
intuitive either. Suppose that the values of X are 10,20,30,40,50
and we do SELECT ... ORDER BY x LIMIT 3 FOR UPDATE. Concurrently
someone updates the 20 to 100. The existing code locks the
Kevin Grittner kevin.gritt...@wicourts.gov writes:
Tom Lane t...@sss.pgh.pa.us wrote:
A re-sort after locking doesn't really make things all nice and
intuitive either.
Would it make any sense to roll back and generate a
SERIALIZATION_FAILURE?
If that's what you want then you run the
On Mon, 2009-01-12 at 14:35 -0400, Jeff Davis wrote:
ate: Mon, 12 Jan 2009 09:52:00 -0800
On Mon, 2009-01-12 at 08:32 -0500, Tom Lane wrote:
That code has been working like this for eight or ten years now and
this
is the first complaint, so taking away functionality on the grounds
that
Tom Lane t...@sss.pgh.pa.us wrote:
Kevin Grittner kevin.gritt...@wicourts.gov writes:
Tom Lane t...@sss.pgh.pa.us wrote:
A re-sort after locking doesn't really make things all nice and
intuitive either.
Would it make any sense to roll back and generate a
SERIALIZATION_FAILURE?
If
On Mon, 2009-01-12 at 13:35 -0500, Tom Lane wrote:
I think the behavior Lee is expecting is only implementable with a
full-table write lock, which is exactly what FOR UPDATE is designed
to avoid. There are certain properties you don't get with a partial
lock, and in the end I think we can't
Kevin Grittner kevin.gritt...@wicourts.gov writes:
Tom Lane t...@sss.pgh.pa.us wrote:
If that's what you want then you run the transaction in serializable
mode.
If you run this at SERIALIZABLE transaction isolation level, would
PostgreSQL currently roll something back before returning rows
28 matches
Mail list logo
