Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-04-17 Thread Bruce Momjian
On Mon, Feb 10, 2014 at 06:40:30PM +, Peter Geoghegan wrote: On Sun, Jan 19, 2014 at 2:17 AM, Peter Geoghegan p...@heroku.com wrote: I'm just throwing an error when locking the tuple returns HeapTupleInvisible, and the xmin of the tuple is our xid. I would like some feedback on this

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-04-17 Thread Peter Geoghegan
On Thu, Apr 17, 2014 at 9:52 AM, Bruce Momjian br...@momjian.us wrote: Where are we on this? My hope is that I can get agreement on a way forward during pgCon. Or, at the very least, explain the issues as I see them in a relatively accessible and succinct way to those interested. -- Peter

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-02-10 Thread Heikki Linnakangas
On 02/07/2014 01:27 PM, Peter Geoghegan wrote: On Thu, Jan 16, 2014 at 12:35 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: I think you should consider breaking off the relcache parts of my patch and committing them, because they're independently useful. Makes sense. Can you extract

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-02-10 Thread Peter Geoghegan
On Mon, Feb 10, 2014 at 11:57 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: The relcache parts? I don't think a separate patch ever appeared that could be reviewed. I posted the patch on January 18th:

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-02-10 Thread Peter Geoghegan
On Sun, Jan 19, 2014 at 2:17 AM, Peter Geoghegan p...@heroku.com wrote: I'm just throwing an error when locking the tuple returns HeapTupleInvisible, and the xmin of the tuple is our xid. I would like some feedback on this point. We need to consider how exactly to avoid updating the same tuple

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-02-07 Thread Peter Geoghegan
On Thu, Jan 16, 2014 at 12:35 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: I think you should consider breaking off the relcache parts of my patch and committing them, because they're independently useful. Makes sense. Can you extract that into a separate patch, please? Perhaps you

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-18 Thread Robert Haas
On Thu, Jan 16, 2014 at 3:35 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Makes sense. Can you extract that into a separate patch, please? I was wondering if that might cause deadlocks if an existing index is changed from unique to non-unique, or vice versa, as the ordering would

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-18 Thread Peter Geoghegan
On Sat, Jan 18, 2014 at 5:28 AM, Robert Haas robertmh...@gmail.com wrote: I was wondering if that might cause deadlocks if an existing index is changed from unique to non-unique, or vice versa, as the ordering would change. But we don't have a DDL command to change that, so the question is

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-18 Thread Peter Geoghegan
On Thu, Jan 16, 2014 at 6:31 PM, Peter Geoghegan p...@heroku.com wrote: I think we need to give this some more thought. I have not addressed the implications for MVCC snapshots here. So I gave this some more thought, and this is what I came up with: + static bool +

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-18 Thread Peter Geoghegan
On Sat, Jan 18, 2014 at 6:17 PM, Peter Geoghegan p...@heroku.com wrote: MySQL users are not notified that this happened, and are probably blissfully unaware that there has been a limited form of data loss. So it's The Right Thing to say to Postgres users: if you inserted these rows into the

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-18 Thread Peter Geoghegan
On Sat, Jan 18, 2014 at 7:49 PM, Peter Geoghegan p...@heroku.com wrote: Personally, I favor just making case HeapTupleSelfUpdated: within the patch's ExecLockHeapTupleForUpdateSpec() function complain when hufd.cmax == estate-es_output_cid) (currently there is a separate complaint, but only

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-16 Thread Heikki Linnakangas
On 01/16/2014 03:25 AM, Peter Geoghegan wrote: I think you should consider breaking off the relcache parts of my patch and committing them, because they're independently useful. If we are going to have a lot of conflicts that need to be handled by a heap_delete(), there is no point in inserting

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-16 Thread Peter Geoghegan
On Thu, Jan 16, 2014 at 12:35 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Makes sense. Can you extract that into a separate patch, please? Okay. On an unrelated note, here are results for a benchmark that compares the two patches for an insert heavy workload:

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-16 Thread Peter Geoghegan
On Wed, Jan 15, 2014 at 11:02 PM, Peter Geoghegan p...@heroku.com wrote: It might just be a matter of: @@ -186,6 +186,13 @@ ExecLockHeapTupleForUpdateSpec(EState *estate, switch (test) { case HeapTupleInvisible: + /* +

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-15 Thread Peter Geoghegan
On Tue, Jan 14, 2014 at 3:25 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Attached is a patch doing that, to again demonstrate what I mean. I'm not sure if setting xmin to invalid is really the best way to mark the tuple dead; I don't think a tuple's xmin can currently be

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-15 Thread Peter Geoghegan
On Tue, Jan 14, 2014 at 3:07 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Right, but with your approach, can you really be sure that you have the right rejecting tuple ctid (not reject)? In other words, as you wait for the exclusion constraint to conclusively indicate that there is a

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-15 Thread Peter Geoghegan
On Wed, Jan 15, 2014 at 8:23 PM, Peter Geoghegan p...@heroku.com wrote: I have an idea of what I could do to fix this, but I don't have time to make sure that my hunch is correct. It might just be a matter of: @@ -186,6 +186,13 @@ ExecLockHeapTupleForUpdateSpec(EState *estate, switch

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-14 Thread Peter Geoghegan
On Mon, Jan 13, 2014 at 6:45 PM, Peter Geoghegan p...@heroku.com wrote: + uint32 + SpeculativeInsertionIsInProgress(TransactionId xid, RelFileNode rel, ItemPointer tid) + { For the purposes of preventing unprincipled deadlocking, commenting out the following (the only caller of the above) has

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-14 Thread Heikki Linnakangas
On 01/14/2014 12:20 PM, Peter Geoghegan wrote: I think that the prevention of unprincipled deadlocking is all down to this immediately prior piece of code, at least in those test cases: ! /* !* in insertion by other. !* !

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-14 Thread Heikki Linnakangas
On 01/14/2014 12:44 AM, Peter Geoghegan wrote: On Mon, Jan 13, 2014 at 12:58 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Well, even if you don't agree that locking all the conflicting rows for update is sensible, it's still perfectly sensible to return the rejected rows to the user.

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-14 Thread Peter Geoghegan
On Tue, Jan 14, 2014 at 2:43 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Hmm. So the scenario would be that a process inserts a tuple, but kills it again later in the transaction, and then re-inserts the same value. The expectation is that because it inserted the value once already,

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-14 Thread Heikki Linnakangas
On 01/14/2014 11:22 PM, Peter Geoghegan wrote: On Tue, Jan 14, 2014 at 2:43 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: You have suspected that many times throughout this thread, and every time there's been a relatively simple solutions to the issues you've raised. I suspect that's

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-14 Thread Heikki Linnakangas
On 01/14/2014 11:22 PM, Peter Geoghegan wrote: The problem was that inserted-then-deleted-in-same-xact tuples (both regular and promise) were invisible to all xacts' dirty snapshots, when they should have only been invisible to the deleting xact's dirty snapshot. Right. So it isn't obvious

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-14 Thread Peter Geoghegan
On Tue, Jan 14, 2014 at 2:16 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: I don't think it's a mundane issue. But in any case, you haven't addressed why you think your proposal is more or less better than my proposal, which is the pertinent question. 1. It's simpler. 2. Works for

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-13 Thread Heikki Linnakangas
On 01/11/2014 12:40 AM, Peter Geoghegan wrote: My problem is that in general I'm not sold on the actual utility of making this kind of row locking work with exclusion constraints. I'm sincerely having a hard time thinking of a practical use-case (although, as I've said, I want to make it work

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-13 Thread Heikki Linnakangas
On 01/11/2014 12:39 PM, Peter Geoghegan wrote: In any case, my patch is bound to win decisively for the other extreme, the insert-only case, because the overhead of doing an index scan first is always wasted there with your approach, and the overhead of extended btree leaf page locking has been

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-13 Thread Peter Geoghegan
On Mon, Jan 13, 2014 at 12:23 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Exclusion constraints can be used to implement uniqueness checks with SP-GiST or GiST indexes. For example, if you want to enforce that there are no two tuples with the same x and y coordinates, ie. use a point

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-13 Thread Robert Haas
On Mon, Jan 13, 2014 at 1:53 PM, Peter Geoghegan p...@heroku.com wrote: On Mon, Jan 13, 2014 at 12:23 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Exclusion constraints can be used to implement uniqueness checks with SP-GiST or GiST indexes. For example, if you want to enforce that

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-13 Thread Peter Geoghegan
On Mon, Jan 13, 2014 at 12:17 PM, Robert Haas robertmh...@gmail.com wrote: For what it's worth, I agree with Heikki. There's probably nothing sensible an upsert can do if it conflicts with more than one tuple, but if it conflicts with just exactly one, it oughta be OK. If there is exactly

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-13 Thread Heikki Linnakangas
On 01/13/2014 10:53 PM, Peter Geoghegan wrote: On Mon, Jan 13, 2014 at 12:17 PM, Robert Haas robertmh...@gmail.com wrote: For what it's worth, I agree with Heikki. There's probably nothing sensible an upsert can do if it conflicts with more than one tuple, but if it conflicts with just exactly

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-13 Thread Peter Geoghegan
On Mon, Jan 13, 2014 at 12:58 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Well, even if you don't agree that locking all the conflicting rows for update is sensible, it's still perfectly sensible to return the rejected rows to the user. For example, you're inserting N rows, and if

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-13 Thread Peter Geoghegan
On Mon, Jan 13, 2014 at 12:49 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: In any case, my patch is bound to win decisively for the other extreme, the insert-only case, because the overhead of doing an index scan first is always wasted there with your approach, and the overhead of

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-11 Thread Peter Geoghegan
On Fri, Jan 10, 2014 at 7:59 PM, Peter Geoghegan p...@heroku.com wrote: *shrug*. I'm not too concerned about performance during contention. But let's see how this fixed version performs. Could you repeat the tests you did with this? Why would you not be too concerned about the performance

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-11 Thread Peter Geoghegan
On Sat, Jan 11, 2014 at 2:39 AM, Peter Geoghegan p...@heroku.com wrote: So I re-ran the same old benchmark, where we're almost exclusively updating. Results for your latest revision were very similar to my patch:

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-10 Thread Heikki Linnakangas
On 01/10/2014 05:36 AM, Peter Geoghegan wrote: While I realize that everyone is busy, I'm concerned about the lack of discussing here. It's been 6 full days since I posted my benchmark, which I expected to quickly clear some things up, or at least garner interest, and yet no one has commented

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-10 Thread Heikki Linnakangas
On 01/08/2014 06:46 AM, Peter Geoghegan wrote: A new revision of my patch is attached. I'm getting deadlocks with this patch, using the test script you posted earlier in http://www.postgresql.org/message-id/CAM3SWZQh=8xnvgbbzyhjexujbhwznjutjez9t-dbo9t_mx_...@mail.gmail.com. Am doing

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-10 Thread Peter Geoghegan
On Fri, Jan 10, 2014 at 7:12 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: I'm getting deadlocks with this patch, using the test script you posted earlier in http://www.postgresql.org/message-id/CAM3SWZQh=8xnvgbbzyhjexujbhwznjutjez9t-dbo9t_mx_...@mail.gmail.com. Am doing something

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-10 Thread Heikki Linnakangas
On 01/10/2014 08:37 PM, Peter Geoghegan wrote: On Fri, Jan 10, 2014 at 7:12 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: I'm getting deadlocks with this patch, using the test script you posted earlier in

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-10 Thread Peter Geoghegan
On Fri, Jan 10, 2014 at 11:28 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Why does it deadlock with the btreelock patch? I don't see why it should. If you have two backends inserting a single tuple, and they conflict, one of them should succeed to insert, and the other one should

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-10 Thread Heikki Linnakangas
On 01/10/2014 10:00 PM, Peter Geoghegan wrote: On Fri, Jan 10, 2014 at 11:28 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Why does it deadlock with the btreelock patch? I don't see why it should. If you have two backends inserting a single tuple, and they conflict, one of them should

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-10 Thread Peter Geoghegan
On Fri, Jan 10, 2014 at 1:25 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: This is pretty much the same issue we discussed wrt. exclusion contraints. If the tuple being inserted conflicts with several existing tuples, what to do? I think the best answer would be to return and lock them

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-10 Thread Jim Nasby
On 1/10/14, 4:40 PM, Peter Geoghegan wrote: My problem is that in general I'm not sold on the actual utility of making this kind of row locking work with exclusion constraints. I'm sincerely having a hard time thinking of a practical use-case (although, as I've said, I want to make it work with

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-10 Thread Peter Geoghegan
On Fri, Jan 10, 2014 at 4:09 PM, Jim Nasby j...@nasby.net wrote: Well, the usual example for exclusion constraints is resource scheduling (ie: scheduling what room a class will be held in). In that context is it hard to believe that you might want to MERGE a set of new classroom assignments

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-10 Thread Jim Nasby
On 1/10/14, 6:51 PM, Peter Geoghegan wrote: On Fri, Jan 10, 2014 at 4:09 PM, Jim Nasbyj...@nasby.net wrote: Well, the usual example for exclusion constraints is resource scheduling (ie: scheduling what room a class will be held in). In that context is it hard to believe that you might want to

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-10 Thread Peter Geoghegan
On Fri, Jan 10, 2014 at 7:09 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Nah, that's nothing. I have a patch in the January commitfest that was already posted for the previous commitfest. It received zero review back then, and still has no reviewer signed up, let alone anyone actually

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-10 Thread Greg Stark
On Sat, Jan 11, 2014 at 12:51 AM, Peter Geoghegan p...@heroku.com wrote: On Fri, Jan 10, 2014 at 4:09 PM, Jim Nasby j...@nasby.net wrote: Well, the usual example for exclusion constraints is resource scheduling (ie: scheduling what room a class will be held in). In that context is it hard to

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-10 Thread Peter Geoghegan
On Fri, Jan 10, 2014 at 10:01 PM, Greg Stark st...@mit.edu wrote: So you schedule a class that clashes with 3 other classes, and you want to update all 3 rows/classes with details from your one row proposed for insertion? Well, perhaps you want to mark the events as conflicting with your new

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-09 Thread Peter Geoghegan
On Tue, Jan 7, 2014 at 8:46 PM, Peter Geoghegan p...@heroku.com wrote: I've worked on a simple set of tests, written quickly in bash, that I think exercise interesting cases: https://github.com/petergeoghegan/upsert Perhaps most notably, these tests make comparisons between the performance

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-03 Thread Peter Eisentraut
This patch doesn't apply anymore. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-03 Thread Peter Geoghegan
On Fri, Jan 3, 2014 at 7:39 AM, Peter Eisentraut pete...@gmx.net wrote: This patch doesn't apply anymore. Yes, there was some bit-rot. I previous deferred dealing with a shift/reduce conflict implied by commit 1b4f7f93b4693858cb983af3cd557f6097dab67b. I've fixed that problem now using non

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-03 Thread Peter Geoghegan
On Fri, Dec 13, 2013 at 4:06 PM, Tom Lane t...@sss.pgh.pa.us wrote: BTW, so far as the syntax goes, I'm quite distressed by having to make REJECTS into a fully-reserved word. It's not reserved according to the standard, and it seems pretty likely to be something that apps might be using as a

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-02 Thread Andres Freund
On 2013-12-27 14:11:44 -0800, Peter Geoghegan wrote: On Fri, Dec 27, 2013 at 12:57 AM, Andres Freund and...@2ndquadrant.com wrote: I don't think the current syntax the feature implements can be used as the sole argument what the feature should be able to support. If you think from the

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-02 Thread Peter Geoghegan
On Thu, Jan 2, 2014 at 1:49 AM, Andres Freund and...@2ndquadrant.com wrote: Well, you're not totally on your own for something like that with this feature. You can project the conflicter's tid, and possibly do a more sophisticated recovery, like inspecting the locked row and iterating. Yea,

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-02 Thread Andres Freund
On 2014-01-02 02:20:02 -0800, Peter Geoghegan wrote: On Thu, Jan 2, 2014 at 1:49 AM, Andres Freund and...@2ndquadrant.com wrote: Well, you're not totally on your own for something like that with this feature. You can project the conflicter's tid, and possibly do a more sophisticated

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-02 Thread Robert Haas
On Tue, Dec 31, 2013 at 4:12 AM, Peter Geoghegan p...@heroku.com wrote: On Tue, Dec 31, 2013 at 12:52 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: 1. PromiseTupleInsertionLockAcquire(my xid) 2. Insert heap tuple 3. Insert index tuples 4. Check if conflict happened. Kill the

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-02 Thread Heikki Linnakangas
On 01/02/2014 02:53 PM, Robert Haas wrote: On Tue, Dec 31, 2013 at 4:12 AM, Peter Geoghegan p...@heroku.com wrote: On Tue, Dec 31, 2013 at 12:52 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: 1. PromiseTupleInsertionLockAcquire(my xid) 2. Insert heap tuple 3. Insert index tuples 4.

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-02 Thread Robert Haas
On Thu, Jan 2, 2014 at 11:08 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 01/02/2014 02:53 PM, Robert Haas wrote: On Tue, Dec 31, 2013 at 4:12 AM, Peter Geoghegan p...@heroku.com wrote: On Tue, Dec 31, 2013 at 12:52 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: 1.

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-02 Thread Peter Geoghegan
I decided to make at least a cursory attempt to measure or characterize the performance of each of our approaches to value locking. Being fair here is a non-trivial matter, because of the fact that upserts can behave quite differently based on the need to insert or update, lock contention and so

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-02 Thread Peter Geoghegan
On Thu, Jan 2, 2014 at 8:08 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Yeah, it seems like PromiseTupleInsertionLockAcquire should be locking the tuple, rather than the XID. Well, that would be ideal, because we already have tuple locks. It would be nice to use the same concept for

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-02 Thread Peter Geoghegan
On Thu, Jan 2, 2014 at 2:37 AM, Andres Freund and...@2ndquadrant.com wrote: Locking the definitely visible row only works if there's a row matching the index's columns. If the values of the new row don't have corresponding values in all the indexes you have the same old race conditions again.

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2014-01-02 Thread Peter Geoghegan
On Thu, Jan 2, 2014 at 11:58 AM, Peter Geoghegan p...@heroku.com wrote: My executive summary is that the exclusion patch performs about the same on lower client counts, presumably due to not having the additional window of btree lock contention. By 8 clients, the exclusion patch does

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-31 Thread Heikki Linnakangas
On 12/31/2013 09:18 AM, Peter Geoghegan wrote: On Sun, Dec 29, 2013 at 9:09 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: While mulling this over further, I had an idea about this: suppose we marked the tuple in some fashion that indicates that it's a promise tuple. I imagine an

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-31 Thread Peter Geoghegan
On Tue, Dec 31, 2013 at 12:52 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: 1. PromiseTupleInsertionLockAcquire(my xid) 2. Insert heap tuple 3. Insert index tuples 4. Check if conflict happened. Kill the already-inserted tuple on conflict. 5. PromiseTupleInsertionLockRelease(my xid)

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-31 Thread Peter Geoghegan
On Tue, Dec 31, 2013 at 12:52 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Are you suggesting that I lock the tuple only (say, through a special LockPromiseTuple() call), or lock the tuple *and* call XactLockTableWait() afterwards? You and Robert don't seem to be in agreement about

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-30 Thread Heikki Linnakangas
On 12/30/2013 05:57 AM, Peter Geoghegan wrote: Now, when you actually posted the prototype, I realized that it was the same basic design that I'd cited in my very first e-mail on the IGNORE patch (the one that didn't have row locking at all) - nobody else wanted to do heap insertion first for

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-30 Thread Andres Freund
On 2013-12-29 19:57:31 -0800, Peter Geoghegan wrote: On Sun, Dec 29, 2013 at 8:18 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: My position is not based on a gut feeling. It is based on carefully considering the interactions of the constituent parts, plus the experience of actually

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-30 Thread Peter Geoghegan
On Mon, Dec 30, 2013 at 8:19 AM, Andres Freund and...@2ndquadrant.com wrote: Maybe you should describe what you mean with unprincipled. Sure, the current patch deadlocks, but I don't see anything fundamental, unresolvable there. So I don't understand what the word unprincipled means in that

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-30 Thread Andres Freund
On 2013-12-30 12:29:22 -0800, Peter Geoghegan wrote: But even if that wasn't true, I don't know why you feel the need to go on and on about buffer locking like this months later. Are you trying to be provocative? Can you *please* stop? ERR? Peter? *You* quoted a statement of mine that only

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-30 Thread Adrian Klaver
On 12/30/2013 12:45 PM, Andres Freund wrote: On 2013-12-30 12:29:22 -0800, Peter Geoghegan wrote: But even if that wasn't true, I don't know why you feel the need to go on and on about buffer locking like this months later. Are you trying to be provocative? Can you *please* stop? ERR? Peter?

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-30 Thread Peter Geoghegan
On Mon, Dec 30, 2013 at 7:22 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Ah, I didn't remember that thread. Yeah, apparently I proposed the exact same design back then. Simon complained about the dead tuples being left behind, but I don't think that's a big issue with the design we've

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-30 Thread Peter Geoghegan
On Sun, Dec 29, 2013 at 9:09 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: While mulling this over further, I had an idea about this: suppose we marked the tuple in some fashion that indicates that it's a promise tuple. I imagine an infomask bit, although the concept makes me wince a

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-29 Thread Heikki Linnakangas
On 12/26/2013 01:27 AM, Peter Geoghegan wrote: On Wed, Dec 25, 2013 at 6:25 AM, Andres Freund and...@2ndquadrant.com wrote: And yes, I still think that promise tuples might be a better solution regardless of the issues you mentioned, but you know what? That doesn't matter. Me thinking it's the

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-29 Thread Heikki Linnakangas
On 12/27/2013 07:11 AM, Peter Geoghegan wrote: On Thu, Dec 26, 2013 at 5:58 PM, Robert Haas robertmh...@gmail.com wrote: While mulling this over further, I had an idea about this: suppose we marked the tuple in some fashion that indicates that it's a promise tuple. I imagine an infomask bit,

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-29 Thread Peter Geoghegan
On Sun, Dec 29, 2013 at 8:18 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: My position is not based on a gut feeling. It is based on carefully considering the interactions of the constituent parts, plus the experience of actually building a working prototype. I also carefully

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-28 Thread Peter Geoghegan
Attached revision only uses heavyweight page locks across complex operations. I haven't benchmarked it, but it appears to perform reasonably well. I haven't attempted to measure a regression for regular insertion, but offhand it seems likely that any regression would be well within the noise -

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-27 Thread Andres Freund
Hi, On 2013-12-25 15:27:36 -0800, Peter Geoghegan wrote: Uh, I knew that it was a problem all along. While I explored ways of ameliorating the problem, I specifically stated that we should discuss the subsystems interactions/design, which you were far too quick to dismiss. Aha? The overall

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-27 Thread Peter Geoghegan
On Fri, Dec 27, 2013 at 12:57 AM, Andres Freund and...@2ndquadrant.com wrote: You know what. I don't particularly feel the need to be a reviewer of this patch. I comment because there didn't seem enough comments on some parts and because I see some things as problematic. If you don't want

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-26 Thread Robert Haas
On Fri, Dec 20, 2013 at 12:39 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Hmm. If I understand the problem correctly, it's that as soon as another backend sees the tuple you've inserted and calls XactLockTableWait(), it will not stop waiting even if we later decide to kill the

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-26 Thread Peter Geoghegan
On Thu, Dec 26, 2013 at 5:58 PM, Robert Haas robertmh...@gmail.com wrote: While mulling this over further, I had an idea about this: suppose we marked the tuple in some fashion that indicates that it's a promise tuple. I imagine an infomask bit, although the concept makes me wince a bit since

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-26 Thread Andres Freund
On 2013-12-26 21:11:27 -0800, Peter Geoghegan wrote: I'm generally opposed to making value locks of any stripe be held for more than an instant (so we should not hold them indefinitely pending another conflicting xact finishing). It's not just that it's convenient to my implementation; I also

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-25 Thread Andres Freund
Hi, On 2013-12-24 13:18:36 -0800, Peter Geoghegan wrote: On Tue, Dec 24, 2013 at 4:09 AM, Andres Freund and...@2ndquadrant.com wrote: I don't really see the lack of review as being crucial at this point. At least I have quite some doubts about the approach you've chosen and I have voiced

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-25 Thread Peter Geoghegan
On Wed, Dec 25, 2013 at 6:25 AM, Andres Freund and...@2ndquadrant.com wrote: So? I still have the fear that you approach will end up being way too complicated and full of layering violations. I didn't say it's a no-go (not that I have veto powers, even if I'd consider it one). Apart from not

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-24 Thread Andres Freund
On 2013-12-23 14:59:31 -0800, Peter Geoghegan wrote: On Mon, Dec 23, 2013 at 7:49 AM, Robert Haas robertmh...@gmail.com wrote: I don't think this is a project to rush through. We've lived without MERGE/UPSERT for several years now, and we can live without it for another release cycle while

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-24 Thread Peter Geoghegan
On Tue, Dec 24, 2013 at 4:09 AM, Andres Freund and...@2ndquadrant.com wrote: I don't really see the lack of review as being crucial at this point. At least I have quite some doubts about the approach you've chosen and I have voiced it - so have others. Apparently you haven't been keeping up

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-23 Thread Robert Haas
On Sun, Dec 22, 2013 at 6:42 PM, Peter Geoghegan p...@heroku.com wrote: On Fri, Dec 20, 2013 at 11:59 PM, Peter Geoghegan p...@heroku.com wrote: I think that the way forward is to refine my design in order to upgrade locks from exclusive buffer locks to something else, managed by the lock

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-23 Thread Peter Geoghegan
On Mon, Dec 23, 2013 at 7:49 AM, Robert Haas robertmh...@gmail.com wrote: I don't think this is a project to rush through. We've lived without MERGE/UPSERT for several years now, and we can live without it for another release cycle while we try to reach agreement on the way forward. I can

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-23 Thread Robert Haas
On Mon, Dec 23, 2013 at 5:59 PM, Peter Geoghegan p...@heroku.com wrote: On Mon, Dec 23, 2013 at 7:49 AM, Robert Haas robertmh...@gmail.com wrote: I don't think this is a project to rush through. We've lived without MERGE/UPSERT for several years now, and we can live without it for another

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-22 Thread Peter Geoghegan
On Fri, Dec 20, 2013 at 11:59 PM, Peter Geoghegan p...@heroku.com wrote: I think that the way forward is to refine my design in order to upgrade locks from exclusive buffer locks to something else, managed by the lock manager but perhaps through an additional layer of indirection. As

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-21 Thread Peter Geoghegan
On Fri, Dec 20, 2013 at 1:12 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: There are probably other ways to make that general idea work though. I didn't follow this thread carefully, but is the idea that there would be many promise tuples live at any one time, or only one? Because if

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-20 Thread Heikki Linnakangas
On 12/20/2013 06:06 AM, Peter Geoghegan wrote: On Wed, Dec 18, 2013 at 8:39 PM, Peter Geoghegan p...@heroku.com wrote: Empirically, retrying because ExecInsertIndexTuples() returns some recheckIndexes occurs infrequently, so maybe that makes all of this okay. Or maybe it happens infrequently

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-20 Thread Robert Haas
On Fri, Dec 20, 2013 at 3:39 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Hmm. If I understand the problem correctly, it's that as soon as another backend sees the tuple you've inserted and calls XactLockTableWait(), it will not stop waiting even if we later decide to kill the

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-20 Thread Alvaro Herrera
Robert Haas escribió: On Fri, Dec 20, 2013 at 3:39 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Hmm. If I understand the problem correctly, it's that as soon as another backend sees the tuple you've inserted and calls XactLockTableWait(), it will not stop waiting even if we later

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-20 Thread Heikki Linnakangas
On 12/20/2013 10:56 PM, Alvaro Herrera wrote: Robert Haas escribió: On Fri, Dec 20, 2013 at 3:39 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Hmm. If I understand the problem correctly, it's that as soon as another backend sees the tuple you've inserted and calls XactLockTableWait(),

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-20 Thread Peter Geoghegan
On Fri, Dec 20, 2013 at 12:39 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Hmm. If I understand the problem correctly, it's that as soon as another backend sees the tuple you've inserted and calls XactLockTableWait(), it will not stop waiting even if we later decide to kill the

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-18 Thread Peter Geoghegan
On Thu, Dec 12, 2013 at 4:18 PM, Peter Geoghegan p...@heroku.com wrote: Both of these revisions have identical ad-hoc test cases included as new files - see testcase.sh and upsert.sql. My patch doesn't have any unique constraint violations, and has pretty consistent performance, while yours

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-14 Thread Peter Geoghegan
On Fri, Dec 13, 2013 at 4:06 PM, Tom Lane t...@sss.pgh.pa.us wrote: I spent a little bit of time looking at btreelock_insert_on_dup. AFAICT it executes FormIndexDatum() for later indexes while holding assorted buffer locks in earlier indexes. That really ain't gonna do, because in the case

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-13 Thread Tom Lane
Peter Geoghegan p...@heroku.com writes: I attached two revisions - one of my patch (btreelock_insert_on_dup) and one of your alternative design (exclusion_insert_on_dup). I spent a little bit of time looking at btreelock_insert_on_dup. AFAICT it executes FormIndexDatum() for later indexes

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-12 Thread Heikki Linnakangas
What's the status of this patch? I posted my version using a quite different approach than your original patch. You did some testing of that, and ran into unrelated bugs. Have they been fixed now? Where do we go from here? Are you planning to continue based on my proof-of-concept patch,

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-12-12 Thread Peter Geoghegan
On Thu, Dec 12, 2013 at 1:23 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: What's the status of this patch? I posted my version using a quite different approach than your original patch. You did some testing of that, and ran into unrelated bugs. Have they been fixed now? Sorry, I

Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE

2013-11-27 Thread Peter Geoghegan
On Tue, Nov 26, 2013 at 8:19 PM, Peter Geoghegan p...@heroku.com wrote: There are some visibility-related race conditions even still I also see this, sandwiched between the very many deadlock detected errors recorded over 6 or so hours (this is in chronological order, with no ERRORs omitted

  1   2   3   >