David Fetter wrote:
I think I haven't communicated clearly what I'm suggesting, which is
that we ship with both an UPSERT and a MERGE, the former being ugly,
crude and simple, and the latter festooned with dire warnings about
isolation levels and locking.
I don't know that I completely
Heikki Linnakangas wrote:
You can of course LOCK TABLE as a work-around, if that's what you want.
What I was trying to suggest upthread is that while there are other
possible ways around this problem, the only one that has any hope of
shipping with 9.1 is to do just that. So from my
Robert Haas wrote:
And even if it isn't, the MERGE syntax is insane if what you really
want to do is insert or update ONE record. If all we have is MERGE,
people will keep doing it with a PL/pgsql stored procedure or some
crummy application logic just so that they don't have to spend several
On Tue, Jan 04, 2011 at 04:44:32AM -0500, Greg Smith wrote:
Heikki Linnakangas wrote:
You can of course LOCK TABLE as a work-around, if that's what you want.
Presuming the code quality issues and other little quirks I've
documented (and new ones yet to be discovered) can get resolved
here,
On 2011-01-04 6:27 PM, David Fetter wrote:
On Tue, Jan 04, 2011 at 04:44:32AM -0500, Greg Smith wrote:
Heikki Linnakangas wrote:
You can of course LOCK TABLE as a work-around, if that's what you want.
Presuming the code quality issues and other little quirks I've
documented (and new ones yet
On Tue, Jan 04, 2011 at 07:02:54PM +0200, Marko Tiikkaja wrote:
On 2011-01-04 6:27 PM, David Fetter wrote:
On Tue, Jan 04, 2011 at 04:44:32AM -0500, Greg Smith wrote:
Heikki Linnakangas wrote:
You can of course LOCK TABLE as a work-around, if that's what you want.
Presuming the code quality
Kevin Grittner wrote:
Greg Smith wrote:
I could see shipping this with the automatic heavy LOCK TABLE in
there.
How would you handle or document behavior in REPEATABLE READ
isolation? The lock doesn't do much good unless you acquire it
before you get your snapshot, right?
David Fetter wrote:
How about implementing an UPSERT command as take the lock, do the
merge? That way, we'd have both the simplicity for the simpler cases
and a way to relax consistency guarantees for those who would like to
do so.
Main argument against is that path leads to a permanent
On Tue, Jan 04, 2011 at 09:27:10PM -0500, Greg Smith wrote:
David Fetter wrote:
How about implementing an UPSERT command as take the lock, do the
merge? That way, we'd have both the simplicity for the simpler cases
and a way to relax consistency guarantees for those who would like to
do so.
On Mon, 2011-01-03 at 01:53 -0500, Greg Smith wrote:
In advance of the planned but not available yet ability to
lock individual index key values, locking the whole table is the only
possible implementation that can work correctly here I'm aware of.
This was discussed here
On 03.01.2011 11:37, Simon Riggs wrote:
On Mon, 2011-01-03 at 01:53 -0500, Greg Smith wrote:
In advance of the planned but not available yet ability to
lock individual index key values, locking the whole table is the only
possible implementation that can work correctly here I'm aware of.
On Mon, 2011-01-03 at 15:12 +0200, Heikki Linnakangas wrote:
This patch has never tried to implement concurrency-safe upsert. It
implements the MERGE command as specified by the SQL standard, nothing
more, nothing less. Let's not move the goalposts. Googling around, at
least MS SQL
On Mon, Jan 3, 2011 at 8:35 AM, Simon Riggs si...@2ndquadrant.com wrote:
On Mon, 2011-01-03 at 15:12 +0200, Heikki Linnakangas wrote:
This patch has never tried to implement concurrency-safe upsert. It
implements the MERGE command as specified by the SQL standard, nothing
more, nothing less.
* Robert Haas (robertmh...@gmail.com) wrote:
Like Heikki, I'd rather have the feature without a workaround for the
concurrency issues than no feature.
I'm still trying to figure out the problem with having the table-level
lock, unless we really think people will be doing concurrent MERGE's
On 03.01.2011 17:56, Stephen Frost wrote:
* Robert Haas (robertmh...@gmail.com) wrote:
Like Heikki, I'd rather have the feature without a workaround for the
concurrency issues than no feature.
I'm still trying to figure out the problem with having the table-level
lock, unless we really think
On Mon, Jan 3, 2011 at 10:58 AM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
On 03.01.2011 17:56, Stephen Frost wrote:
* Robert Haas (robertmh...@gmail.com) wrote:
Like Heikki, I'd rather have the feature without a workaround for the
concurrency issues than no feature.
On 01/03/2011 10:58 AM, Heikki Linnakangas wrote:
In general, I also thought/expected to have some kind of UPSERT type
capability with our initial MERGE support, even if it requires a big
lock and won't operate concurrently, etc.
You can of course LOCK TABLE as a work-around, if that's
On 03.01.2011 18:02, Robert Haas wrote:
On Mon, Jan 3, 2011 at 10:58 AM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
On 03.01.2011 17:56, Stephen Frost wrote:
* Robert Haas (robertmh...@gmail.com) wrote:
Like Heikki, I'd rather have the feature without a workaround for
On Mon, Jan 3, 2011 at 11:08 AM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
On 03.01.2011 18:02, Robert Haas wrote:
On Mon, Jan 3, 2011 at 10:58 AM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
On 03.01.2011 17:56, Stephen Frost wrote:
* Robert Haas
On Mon, 2011-01-03 at 18:08 +0200, Heikki Linnakangas wrote:
It works in read committed mode, because you acquire a new snapshot
after the LOCK TABLE, and anyone else who modified the table must commit
before the lock is granted. In serializable mode you get a serialization
error.
If its
On 03.01.2011 18:29, Simon Riggs wrote:
On Mon, 2011-01-03 at 18:08 +0200, Heikki Linnakangas wrote:
It works in read committed mode, because you acquire a new snapshot
after the LOCK TABLE, and anyone else who modified the table must commit
before the lock is granted. In serializable mode you
On Jan3, 2011, at 17:21 , Robert Haas wrote:
On Mon, Jan 3, 2011 at 11:08 AM, Heikki Linnakangas
In serializable mode you get a serialization error.
I don't think this part is true. You can certainly do this:
CREATE TABLE test (a int);
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
On Mon, 2011-01-03 at 18:35 +0200, Heikki Linnakangas wrote:
On 03.01.2011 18:29, Simon Riggs wrote:
On Mon, 2011-01-03 at 18:08 +0200, Heikki Linnakangas wrote:
It works in read committed mode, because you acquire a new snapshot
after the LOCK TABLE, and anyone else who modified the
On 03.01.2011 18:49, Simon Riggs wrote:
On Mon, 2011-01-03 at 18:35 +0200, Heikki Linnakangas wrote:
On 03.01.2011 18:29, Simon Riggs wrote:
On Mon, 2011-01-03 at 18:08 +0200, Heikki Linnakangas wrote:
It works in read committed mode, because you acquire a new snapshot
after the LOCK TABLE,
On Mon, Jan 3, 2011 at 11:36 AM, Florian Pflug f...@phlo.org wrote:
On Jan3, 2011, at 17:21 , Robert Haas wrote:
On Mon, Jan 3, 2011 at 11:08 AM, Heikki Linnakangas
In serializable mode you get a serialization error.
I don't think this part is true. You can certainly do this:
CREATE TABLE
On Mon, Jan 3, 2011 at 12:01 PM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
If we do that, then we definitely need a catch-all WHEN statement, so
that we can say
WHEN NOT MATCHED
INSERT
WHEN MATCHED
UPDATE
ELSE
{ INSERT into another table so we can try again in a
On Mon, 2011-01-03 at 19:01 +0200, Heikki Linnakangas wrote:
If we do that, then we definitely need a catch-all WHEN statement, so
that we can say
WHEN NOT MATCHED
INSERT
WHEN MATCHED
UPDATE
ELSE
{ INSERT into another table so we can try again in a minute
or RAISE
Robert Haas robertmh...@gmail.com wrote:
Users hate having to do explicit locking (especially users whose
names rhyme with Bevin Bittner)
:-)
Before you decide to taunt me again, I guess I should point out a
few things here.
Should SSI and MERGE both make it into 9.1, there's every
On Mon, Jan 3, 2011 at 1:18 PM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
Robert Haas robertmh...@gmail.com wrote:
Users hate having to do explicit locking (especially users whose
names rhyme with Bevin Bittner)
:-)
Before you decide to taunt me again, I guess I should point out a
Robert Haas robertmh...@gmail.com wrote:
Kevin Grittner kevin.gritt...@wicourts.gov wrote:
Before you decide to taunt me again, I guess I should point out a
few things here.
Sorry, that was intended as good-natured humor, not taunting.
Oh, I took it that way. I guess my attempt at humor
On Mon, Jan 3, 2011 at 2:01 PM, Kevin Grittner
kevin.gritt...@wicourts.gov wrote:
Robert Haas robertmh...@gmail.com wrote:
Kevin Grittner kevin.gritt...@wicourts.gov wrote:
Before you decide to taunt me again, I guess I should point out a
few things here.
Sorry, that was intended as
Marko Tiikkaja wrote:
I'm confused. Are you saying that the patch is supposed to lock the
table against concurrent INSERT/UPDATE/DELETE/MERGE? Because I don't
see it in the patch, and the symptoms you're having are a clear
indication of the fact that it's not happening. I also seem to
On 2010-12-30 9:02 AM +0200, Greg Smith wrote:
Marko Tiikkaja wrote:
I have no idea why it worked in the past, but the patch was never
designed to work for UPSERT. This has been discussed in the past and
some people thought that that's not a huge deal.
It takes an excessively large lock when
On 12/30/2010 02:02 AM, Greg Smith wrote:
Marko Tiikkaja wrote:
I have no idea why it worked in the past, but the patch was never
designed to work for UPSERT. This has been discussed in the past and
some people thought that that's not a huge deal.
It takes an excessively large lock when
On 2010-12-29 2:14 PM, Greg Smith wrote:
MERGE INTO Stock t
USING (VALUES(10,100)) AS s(item_id,balance)
ON s.item_id=t.item_id
WHEN MATCHED THEN UPDATE SET balance=t.balance + s.balance
WHEN NOT MATCHED THEN INSERT VALUES(s.item_id,s.balance)
;
If you can suggest an alternate way to
Marko Tiikkaja wrote:
As far as I can tell, this should work. I played around with the
patch and the problem seems to be the VALUES:
INTO Stock t
USING (SELECT 30, 2000) AS s(item_id,balance)
ON s.item_id=t.item_id
WHEN MATCHED THEN UPDATE SET balance=t.balance + s.balance
WHEN NOT
On 2010-12-30 4:39 AM +0200, Greg Smith wrote:
And that got me back again to concurrent testing.
Moving onto next two problems...the basic MERGE feature seems to have
stepped backwards a bit too. I'm now seeing these quite often:
ERROR: duplicate key value violates unique constraint
On Wed, Dec 29, 2010 at 9:45 PM, Marko Tiikkaja
marko.tiikk...@cs.helsinki.fi wrote:
I have no idea why it worked in the past, but the patch was never designed
to work for UPSERT. This has been discussed in the past and some people
thought that that's not a huge deal.
I think it's expected to
Marko Tiikkaja wrote:
I have no idea why it worked in the past, but the patch was never
designed to work for UPSERT. This has been discussed in the past and
some people thought that that's not a huge deal.
It takes an excessively large lock when doing UPSERT, which means its
performance
39 matches
Mail list logo
