On Wed, Oct 8, 2014 at 6:29 AM, Simon Riggs wrote:
> Lets see the results of running a COPY please.
Not exactly sure what you mean here. A concurrent COPY?
--
Peter Geoghegan
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://ww
On Wed, Oct 8, 2014 at 2:50 PM, Kevin Grittner 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 take the UPDATE pat
Peter Geoghegan wrote:
On Wed, Oct 8, 2014 at 1:25 AM, Heikki Linnakangas
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 consistently requesting instead of index
na
On 8 October 2014 00:34, Peter Geoghegan 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 page for the du
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
> 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 d
On Wed, 2014-10-08 at 02:22 -0700, Peter Geoghegan wrote:
> On Wed, Oct 8, 2014 at 1:25 AM, Heikki Linnakangas
> 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 ne
On Wed, Oct 8, 2014 at 1:25 AM, Heikki Linnakangas
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 it. It was something
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 obvi
On Wed, Oct 8, 2014 at 12:41 AM, Anssi Kääriäinen
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,
> the feature defaul
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 do
On Tue, Oct 7, 2014 at 11:25 AM, Peter Geoghegan wrote:
> Now, we're not talking about a huge advantage here (I should re-test
> that).
I attach raw output when running the bash scripts insert.sh and
update.sh. These are benchmarks that concern performance in terms of
total system throughput (TPS
On Tue, Oct 7, 2014 at 6:06 AM, Robert Haas 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 possible conflicts
On 7 October 2014 14:06, Robert Haas 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 withdraw any
On Tue, Oct 7, 2014 at 8:33 AM, Simon Riggs wrote:
> On 7 October 2014 03:31, Peter Geoghegan 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. :-)
On 7 October 2014 03:31, Peter Geoghegan 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 heavyweight l
On Mon, Oct 6, 2014 at 5:33 PM, Simon Riggs 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, 'si...@2ndquadrant.com',
> 1000.
On 6 October 2014 15:04, Heikki Linnakangas wrote:
> On 10/06/2014 04:44 PM, Simon Riggs wrote:
>>
>> On 6 October 2014 13:21, Heikki Linnakangas
>> 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
On 10/06/2014 04:44 PM, Simon Riggs wrote:
On 6 October 2014 13:21, Heikki Linnakangas 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 co
On 6 October 2014 13:21, Heikki Linnakangas 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 get occaiso
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 colum
On 10/06/2014 03:05 PM, Simon Riggs wrote:
On 3 October 2014 11:54, Heikki Linnakangas 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, h
On 3 October 2014 11:54, Heikki Linnakangas 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 explain what you mean by
On Fri, Oct 3, 2014 at 3:54 AM, Heikki Linnakangas
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, I see. Still, I don't
On 10/03/2014 01:05 PM, Peter Geoghegan wrote:
On Fri, Oct 3, 2014 at 3:04 AM, Simon Riggs 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
Te
On 3 October 2014 10:57, Peter Geoghegan wrote:
> On Fri, Oct 3, 2014 at 2:50 AM, Simon Riggs 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 th
On Fri, Oct 3, 2014 at 3:04 AM, Simon Riggs 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.
--
Peter Geoghegan
--
S
On 3 October 2014 10:32, Peter Geoghegan wrote:
> On Fri, Oct 3, 2014 at 2:03 AM, Heikki Linnakangas
> 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,
On Fri, Oct 3, 2014 at 2:50 AM, Simon Riggs 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 real world. So to
On 3 October 2014 10:03, Heikki Linnakangas 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 to
> throw an error
On Fri, Oct 3, 2014 at 2:03 AM, Heikki Linnakangas
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 to
> throw an
On 10/03/2014 11:07 AM, Simon Riggs wrote:
On 1 October 2014 20:54, Heikki Linnakangas 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 the
key. If they find a p
On 1 October 2014 20:54, Heikki Linnakangas 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 the
>> key. If they find a promise tuple with an aborted xid the
On Wed, Oct 1, 2014 at 12:59 PM, Peter Geoghegan wrote:
> On Wed, Oct 1, 2014 at 12:54 PM, Heikki Linnakangas
> wrote:
>> XactLockTableWait() waits until the end of transaction, that's not you want
>> here. If the backend that inserted the promise tuple decides to not proceed
>> with the insertio
On Wed, Oct 1, 2014 at 12:54 PM, Heikki Linnakangas
wrote:
> XactLockTableWait() waits until the end of transaction, that's not you want
> here. If the backend that inserted the promise tuple decides to not proceed
> with the insertion, and removes the promise tuple, the backend waiting on it
> ne
On Wed, Oct 1, 2014 at 4:34 AM, Simon Riggs 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
> at a time can
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
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 uni
37 matches
Mail list logo
