On Sat, Sep 27, 2014 at 11:21 PM, Simon Riggs <si...@2ndquadrant.com> wrote: > My request was for the following... > > Agree command semantics by producing these things > * Explanatory documentation (Ch6.4 Data Manipulation - Upsert)
Do you really think I could get an entire chapter out of this? > * SQL Reference Documentation (INSERT) > * Test cases for feature > * Test cases for concurrency All of these were added. There are two new sets of isolation tests, one per variant of the new clause (IGNORE/UPDATE). > * Test cases for pgbench They're not part of the patch proper, but as I've already mentioned I have pgbench based stress-tests on Github. There is a variety of test-cases that test the feature under high concurrency: https://github.com/petergeoghegan/upsert > Examples of the following are needed > > "ON CONFLICT UPDATE optionally accepts a WHERE clause condition. Yes, I realized I missed an example of that one the second I hit "send". The MVCC interactions of this are discussed within transaction-iso.html, FWIW. > Question arising: do you need to specify location criteria, or is this > an additional filter? When/why would we want that? It is an additional way to specify a predicate/condition to UPDATE on. There might be a kind of redundancy, if you decided to repeat the constrained values in the predicate too, but if you're using the WHERE clause sensibly there shouldn't be. So your UPDATE's "full predicate" is sort of the union of the constrained values that the conflict path was taken for, plus whatever you put in the WHERE clause, but not quite because they're evaluated at different times (as explained within transaction-iso.html). > How would you do "if colA = 3 then ignore else update"? Technically, you can't do that exact thing. IGNORE is just for quickly dealing with ETL-type problems (and it is reasonable to use it without one particular unique index in mind, unlike ON CONFLICT UPDATE) - think pgloader. But if you did this: INSERT INTO tab(colB) values('foo') ON CONFLICT UPDATE set colB = CONFLICTING(colB) WHERE colA != 3 Then you would achieve almost the same thing. You wouldn't have inserted or updated anything if the only rows considered had a colA of 3, but any such rows considered would be locked, which isn't the same as IGNOREing them. > No explanation of why the CONFLICTING() syntax differs from OLD./NEW. > syntax used in triggers Why should it be the same? > The page makes no mention of the upsert problem, nor is any previous > code mentioned. What's the upsert problem? I mean, apart from the fact that we don't have it. Note that it is documented that one of the two outcomes is guaranteed. I should have updated the plpgsql looping subxact example, though. -- Peter Geoghegan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
