On Tue, 2014-10-07 at 13:33 +0100, Simon Riggs wrote:
Is there a way of detecting that we are updating a unique constraint
column and then applying the HW locking only in that case? Or can we
only apply locking when we have multiple unique constraints on a
table?
What is the use case of doing
On Wed, Oct 8, 2014 at 12:41 AM, Anssi Kääriäinen
anssi.kaariai...@thl.fi wrote:
The MySQL documentation says that you should try to avoid using an ON
DUPLICATE KEY UPDATE clause on tables with multiple unique indexes[1].
The proposed feature's documentation has the same suggestion[2]. Still,
On 10/08/2014 11:10 AM, Peter Geoghegan wrote:
The reasoning behind making the unique index specification optional is:
We cannot easily cover corner cases with another syntax - unique
indexes must be named directly to cover every case, and make the
user's intent absolutely clear. That's not
On Wed, Oct 8, 2014 at 1:25 AM, Heikki Linnakangas
hlinnakan...@vmware.com wrote:
Instead of naming the index, you should name the columns, and the system can
look up the index or indexes that match those columns.
It's not totally clear that we need *any* WITHIN clause, BTW. I'm not
dead set on
On Wed, 2014-10-08 at 02:22 -0700, Peter Geoghegan wrote:
On Wed, Oct 8, 2014 at 1:25 AM, Heikki Linnakangas
hlinnakan...@vmware.com wrote:
Instead of naming the index, you should name the columns, and the system can
look up the index or indexes that match those columns.
It's not totally
On Wed, 2014-10-08 at 01:10 -0700, Peter Geoghegan wrote:
On Wed, Oct 8, 2014 at 12:41 AM, Anssi Kääriäinen
anssi.kaariai...@thl.fi wrote:
The MySQL documentation says that you should try to avoid using an ON
DUPLICATE KEY UPDATE clause on tables with multiple unique indexes[1].
The
On 8 October 2014 00:34, Peter Geoghegan p...@heroku.com wrote:
INSERTs see #2 win, and by a wider margin than #1 beat #2 with
UPDATEs. However, insert.sh is by design very unsympathetic towards
#1. It uses a serial primary key, so every INSERT uselessly obtains a
HW lock on the same leaf
Peter Geoghegan p...@heroku.com wrote:
On Wed, Oct 8, 2014 at 1:25 AM, Heikki Linnakangas hlinnakan...@vmware.com
wrote:
Instead of naming the index, you should name the columns, and
the system can look up the index or indexes that match those
columns.
+1
That is what I have been
On Wed, Oct 8, 2014 at 2:50 PM, Kevin Grittner kgri...@ymail.com wrote:
What I said was in response to your assertion that your technique
would *never* generate a duplicate key error.
That is strictly true: the INSERT cannot raise a unique violation
error, because to do so would cause it to
On 7 October 2014 03:31, Peter Geoghegan p...@heroku.com wrote:
It may be that people on reading this now believe Peter's HW locking
approach is the best. I'm happy to go with consensus.
I bet you didn't think that you'd say that a week ago. :-)
You're right, because last week I thought
On Tue, Oct 7, 2014 at 8:33 AM, Simon Riggs si...@2ndquadrant.com wrote:
On 7 October 2014 03:31, Peter Geoghegan p...@heroku.com wrote:
It may be that people on reading this now believe Peter's HW locking
approach is the best. I'm happy to go with consensus.
I bet you didn't think that you'd
On 7 October 2014 14:06, Robert Haas robertmh...@gmail.com wrote:
Is there a way of detecting that we are updating a unique constraint
column and then applying the HW locking only in that case? Or can we
only apply locking when we have multiple unique constraints on a
table?
If so, I would
On Tue, Oct 7, 2014 at 6:06 AM, Robert Haas robertmh...@gmail.com wrote:
I'm not up on the details of what Peter's patch does with heavyweight
locking, but I'd say it this way: if the patch uses heavyweight
locking routinely, that's probably not going to scale well[1]. If
the patch detects
On 3 October 2014 11:54, Heikki Linnakangas hlinnakan...@vmware.com wrote:
Simon's approach would actually pass that test case just fine. It inserts
the (promise) index tuple first, and heap tuple only after that. It will
fail the test case with more than one unique index, however.
Please
On 10/06/2014 03:05 PM, Simon Riggs wrote:
On 3 October 2014 11:54, Heikki Linnakangas hlinnakan...@vmware.com wrote:
Simon's approach would actually pass that test case just fine. It inserts
the (promise) index tuple first, and heap tuple only after that. It will
fail the test case with more
On 10/06/2014 03:21 PM, Heikki Linnakangas wrote:
On 10/06/2014 03:05 PM, Simon Riggs wrote:
My understanding of what you're saying is that if
* we have a table with 1 unique index
* and we update the values of the uniquely index columns (e.g. PK update)
* on both of the uniquely indexed
On 6 October 2014 13:21, Heikki Linnakangas hlinnakan...@vmware.com wrote:
My understanding of what you're saying is that if
* we have a table with 1 unique index
* and we update the values of the uniquely index columns (e.g. PK update)
* on both of the uniquely indexed column sets
then we
On 10/06/2014 04:44 PM, Simon Riggs wrote:
On 6 October 2014 13:21, Heikki Linnakangas hlinnakan...@vmware.com wrote:
My understanding of what you're saying is that if
* we have a table with 1 unique index
* and we update the values of the uniquely index columns (e.g. PK update)
* on both of
On 6 October 2014 15:04, Heikki Linnakangas hlinnakan...@vmware.com wrote:
On 10/06/2014 04:44 PM, Simon Riggs wrote:
On 6 October 2014 13:21, Heikki Linnakangas hlinnakan...@vmware.com
wrote:
My understanding of what you're saying is that if
* we have a table with 1 unique index
* and we
On Mon, Oct 6, 2014 at 5:33 PM, Simon Riggs si...@2ndquadrant.com wrote:
Lets look at a real world example
CREATE TABLE citizen
(ssninteger not null primary key
,email text not null unique
,tax_amount decimal);
Transaction 1:
INSERT INTO citizen VALUES (555123456,
On 10/03/2014 11:07 AM, Simon Riggs wrote:
On 1 October 2014 20:54, Heikki Linnakangas hlinnakan...@vmware.com wrote:
On 10/01/2014 02:34 PM, Simon Riggs wrote:
...
When later insert scans see the promise tuple they perform
XactLockTableWait() and when they get control they look again for
On Fri, Oct 3, 2014 at 2:03 AM, Heikki Linnakangas
hlinnakan...@vmware.com wrote:
That lowers the bar from what I thought everyone agreed on. Namely, if two
backends run a similar UPSERT command concurrently on a table that has more
than one unique constraint, they might deadlock, causing one
On 3 October 2014 10:03, Heikki Linnakangas hlinnakan...@vmware.com wrote:
That lowers the bar from what I thought everyone agreed on. Namely, if two
backends run a similar UPSERT command concurrently on a table that has more
than one unique constraint, they might deadlock, causing one of them
On Fri, Oct 3, 2014 at 2:50 AM, Simon Riggs si...@2ndquadrant.com wrote:
My view is that I can't see the above use case from happening in real
situations, except by infrequent mistake. In most cases, unique
indexes represent some form of object identity and those don't change
frequently in the
On 3 October 2014 10:32, Peter Geoghegan p...@heroku.com wrote:
On Fri, Oct 3, 2014 at 2:03 AM, Heikki Linnakangas
hlinnakan...@vmware.com wrote:
That lowers the bar from what I thought everyone agreed on. Namely, if two
backends run a similar UPSERT command concurrently on a table that has
On Fri, Oct 3, 2014 at 3:04 AM, Simon Riggs si...@2ndquadrant.com wrote:
Is there a test case that demonstrates the problem?
Yes. See my e-mail to Heikki here:
http://www.postgresql.org/message-id/cam3swzshbe29kpod44cvc3vpzjgmder6k_6fghiszeozgmt...@mail.gmail.com
Testcase is attached.
--
On 3 October 2014 10:57, Peter Geoghegan p...@heroku.com wrote:
On Fri, Oct 3, 2014 at 2:50 AM, Simon Riggs si...@2ndquadrant.com wrote:
My view is that I can't see the above use case from happening in real
situations, except by infrequent mistake. In most cases, unique
indexes represent some
On 10/03/2014 01:05 PM, Peter Geoghegan wrote:
On Fri, Oct 3, 2014 at 3:04 AM, Simon Riggs si...@2ndquadrant.com wrote:
Is there a test case that demonstrates the problem?
Yes. See my e-mail to Heikki here:
On Fri, Oct 3, 2014 at 3:54 AM, Heikki Linnakangas
hlinnakan...@vmware.com wrote:
Simon's approach would actually pass that test case just fine. It inserts
the (promise) index tuple first, and heap tuple only after that. It will
fail the test case with more than one unique index, however.
Oh,
Summary of algorithm to use promise tuples for concurrency control
during UPSERT
1. Perform btree search to location of key, if it exists.
a) If an unkilled index tuple exists, we decide this is an UPDATE and
drop straight thru to step 2
b) If it does not exist, insert a promise tuple into unique
On 10/01/2014 02:34 PM, Simon Riggs wrote:
Summary of algorithm to use promise tuples for concurrency control
during UPSERT
1. Perform btree search to location of key, if it exists.
a) If an unkilled index tuple exists, we decide this is an UPDATE and
drop straight thru to step 2
b) If it does
On Wed, Oct 1, 2014 at 4:34 AM, Simon Riggs si...@2ndquadrant.com wrote:
Summary of algorithm to use promise tuples for concurrency control
during UPSERT
Index bloat is less of a problem than with normal inserts since there
are additional ways of removing promise tuples. Only one index tuple
32 matches
Mail list logo
