Re: [HACKERS] Promise index tuples for UPSERT

2014-10-08 Thread Anssi Kääriäinen
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-08 Thread Peter Geoghegan
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,

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-08 Thread Heikki Linnakangas
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-08 Thread Peter Geoghegan
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-08 Thread Anssi Kääriäinen
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-08 Thread Anssi Kääriäinen
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-08 Thread Simon Riggs
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-08 Thread Kevin Grittner
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-08 Thread Peter Geoghegan
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-07 Thread Simon Riggs
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-07 Thread Robert Haas
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-07 Thread Simon Riggs
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-07 Thread Peter Geoghegan
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-06 Thread Simon Riggs
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-06 Thread Heikki Linnakangas
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-06 Thread Heikki Linnakangas
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-06 Thread Simon Riggs
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-06 Thread Heikki Linnakangas
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-06 Thread Simon Riggs
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-06 Thread Peter Geoghegan
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,

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-03 Thread Heikki Linnakangas
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-03 Thread Peter Geoghegan
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-03 Thread Simon Riggs
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-03 Thread Peter Geoghegan
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-03 Thread Simon Riggs
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-03 Thread Peter Geoghegan
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. --

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-03 Thread Simon Riggs
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-03 Thread Heikki Linnakangas
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:

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-03 Thread Peter Geoghegan
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,

[HACKERS] Promise index tuples for UPSERT

2014-10-01 Thread Simon Riggs
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-01 Thread Heikki Linnakangas
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

Re: [HACKERS] Promise index tuples for UPSERT

2014-10-01 Thread Peter Geoghegan
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