MERGE Specification
in [PgSql]
|
Prev: [HACKERS] shared_preload_libraries is ignored in single user mode
Next: Earn more for a more satisfactory life. agonizing adelocodonic affrighted
From: David Fetter on 5 Aug 2010 10:20 On Thu, Aug 05, 2010 at 09:55:29PM +0800, Boxuan Zhai wrote: > On Thu, Aug 5, 2010 at 7:25 PM, Simon Riggs <simon(a)2ndquadrant.com> wrote: > > > On Thu, 2010-08-05 at 12:29 +0300, Heikki Linnakangas wrote: > > > On 05/08/10 10:46, Simon Riggs wrote: > > > > On Mon, 2008-04-21 at 21:08 +0100, Simon Riggs wrote: > > > >> The following two files specify the behaviour of the MERGE statement > > and > > > >> how it will work in the world of PostgreSQL. > > > > > > > >> The HTML file was generated from SGML source, though the latter is not > > > >> included here for clarity. > > > > > > > > Enclose merge.sgml docs for forthcoming MERGE command, as originally > > > > written. > > > > > > Oh, cool, I wasn't aware you had written that already. Boxuan, please > > > include this in your patch, after reviewing and removing/editing > > > anything that doesn't apply to your patch. > > > Thanks a lot for the instruction file of MERGE command. I have read through > it carefully. It is really a great work. I have to admit that I am not > familiar with the sgml language, and I cannot write the instruction by > myself. It's really not super complicated. It's quite like (and ancestral to) HTML. > All features of MERGE demonstrated in this file are consistent with my > implementation, EXCEPT the DO NOTHING option. In current edition, we don't > have the DO NOTHING action type. That is, during the execution of MERGE > commands, if one tuple is not caught by any of the merge actions, it will be > ignored. In another word, DO NOTING (although cannot be specified explicitly > by user) is the DEFAULT action for tuples. > > In the contrary, Simon's instruction says that the DEFAULT action for the > tuple caught by no actions is > WHEN NOT MATCHED THEN INSERT DEFAULT VALUES I believe that the SQL standard specifies this behavior, and I don't think we have a compelling reason to do something different from what the SQL standard specifies. > Well, if people want the DO NOTHING action, I will add it in the system. That'd be great :) > Now, I have changed the RULE strategy of MERGE to the better logic. And I > am working on triggers for MERGE, which is also mentioned in the instruction > file. I will build a new patch with no long comment and blank line around > functions, and possibly contain the regress test file and this sgml > instructions in it. > > I wish we can reach a agreement on the DO NOTHING thing before my next > submission, so I can make necessary modification on my code for it. (the new > patch may be finished in one or two days, I think) > > Thanks! > > PS: I have an embarrassing question: how to view the sgml instructions of > postgres in web page form, rather than read the source code of them? After you've built postgresql, do this: cd doc/src/sgml make Then you can point a web browser at the doc/src/sgml/html/index.html (and similar) http://www.postgresql.org/docs/current/static/docguide.html has information about the tools you will need for the above to work. Cheers, David. -- David Fetter <david(a)fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter(a)gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Heikki Linnakangas on 5 Aug 2010 11:17 On 05/08/10 17:22, Simon Riggs wrote: > On Thu, 2010-08-05 at 21:55 +0800, Boxuan Zhai wrote: > >> In the contrary, Simon's instruction says that the DEFAULT action for >> the tuple caught by no actions is >> WHEN NOT MATCHED THEN INSERT DEFAULT VALUES >> >> From the user's point of view, these two kinds of MERGE command may >> have not much differences. But, as the coder, I prefer current >> setting, because we can save the implementation for a new type >> of MERGE actions (DO NOTHING is a special merge action type). And, >> thus, no checks and special process for it. (For example, we need to >> make sure that DO NOTHING is the last WHEN clause, and it has no >> additional qual. And we have to generate a INSERT DEFAULT VALUES >> action for the MERGE command if we don't find the DO NOTHING action) >> >> Well, if people want the DO NOTHING action, I will add it in the >> system. > > This is only important when using AND<search condition>, so its not > important for the common UPSERT case of unconditional UPDATE/INSERT. Assuming the default action if no other action matches is to do nothing, then an explicit DO NOTHING is just a convenience. You can have the same effect by having an "AND NOT <condition>" to all the actions following the DO NOTHING action. I admit it's quite handy, but let's avoid PostgreSQL extensions at this point. > Personally, I would prefer the default action to be RAISE ERROR or > similar. Otherwise its just too easy to get complex logic wrong and lose > a few rows without noticing. If that was the case then you would > definitely need DO NOTHING when you explicitly wanted to lose a few > rows. > > You may think that's a bit strong, but consider that PostgreSQL uses > default => ERROR in vast majority of switch() statements. I think its a > safe coding practice and the annoyance of having run-time errors is much > better than losing rows. > > The INSERT DEFAULT VALUES was behaviour taken from another DBMS, its not > part of the standard AFAICS. What does the standard say about this? We should follow the standard, I don't see enough reason to deviate here. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Robert Haas on 5 Aug 2010 11:40 On Thu, Aug 5, 2010 at 11:35 AM, Simon Riggs <simon(a)2ndquadrant.com> wrote: > * It appears we would be in violation of the standard on > 14.12 General Rule 6 a) i) 2) B), p.890 > (Oh, I wish I was joking, there really is such a paragraph number) Just shoot me. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Merlin Moncure on 5 Aug 2010 11:47 On Thu, Aug 5, 2010 at 7:25 AM, Simon Riggs <simon(a)2ndquadrant.com> wrote: > Also had these fragments as well, if they're still useful. Probably just > useful as pointers as to what else to change to include the docs. > > > The tests and docs were written from SQL standard, so any deviations > would need to be flagged. The idea of writing the tests first was that > they provide an objective test of whether the implementation works > according to spec. > > I'd quite like a commentary on anything that needs changing. Not saying > I will necessarily object to differences, but knowing the differences > sounds important for us. I think this is a wonderful feature. A couple of thoughts: *) Would however very much like to see RETURNING support if it's not there. Our other DML statements support it, and this one should too. wCTE if/when we get it will make the lack of it especially glaring. (OTOH, no issue if there is no rule support...they should be deprecated) *) The decision to stay on the standard and not do a 'race free' version was IMO a good one. I am starting to come around to the point of view that the *only* safe way to guarantee race free merge with the current locking model is to take an appropriate table lock. BTW, our pl/pgsql upsert example we've been encouraging people to use has a horrible bug (see: http://postgresql.1045698.n5.nabble.com/Danger-of-idiomatic-plpgsql-loop-for-merging-data-td2257700.html). If we want to rework the locking model to support anticipatory locks then fine (but that has nothing to do with MERGE specifically). merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Simon Riggs on 5 Aug 2010 11:35
On Thu, 2010-08-05 at 18:17 +0300, Heikki Linnakangas wrote: > On 05/08/10 17:22, Simon Riggs wrote: > > On Thu, 2010-08-05 at 21:55 +0800, Boxuan Zhai wrote: > > > >> In the contrary, Simon's instruction says that the DEFAULT action for > >> the tuple caught by no actions is > >> WHEN NOT MATCHED THEN INSERT DEFAULT VALUES > >> > >> From the user's point of view, these two kinds of MERGE command may > >> have not much differences. But, as the coder, I prefer current > >> setting, because we can save the implementation for a new type > >> of MERGE actions (DO NOTHING is a special merge action type). And, > >> thus, no checks and special process for it. (For example, we need to > >> make sure that DO NOTHING is the last WHEN clause, and it has no > >> additional qual. And we have to generate a INSERT DEFAULT VALUES > >> action for the MERGE command if we don't find the DO NOTHING action) > >> > >> Well, if people want the DO NOTHING action, I will add it in the > >> system. > > > > This is only important when using AND<search condition>, so its not > > important for the common UPSERT case of unconditional UPDATE/INSERT. > > Assuming the default action if no other action matches is to do nothing, > then an explicit DO NOTHING is just a convenience. You can have the same > effect by having an "AND NOT <condition>" to all the actions following > the DO NOTHING action. I admit it's quite handy, but let's avoid > PostgreSQL extensions at this point. err... * DELETE is an extension to the standard, though supported by Oracle, DB2 and SQLServer and damn useful * INSERT DEFAULT VALUES is an extension to the standard, though matches options on the normal INSERT clause * rule support is an extension to the standard * It appears we would be in violation of the standard on 14.12 General Rule 6 a) i) 2) B), p.890 (Oh, I wish I was joking, there really is such a paragraph number) which specifies that the join between source and target table must not return multiple rows or must return "cardinality violation". That's pretty difficult thing to check and not very useful if it does do that. anyway, that list isn't an argument in favour of change. The argument in favour of a fail-safe default is that it is a safe coding practice that the PostgreSQL project already uses itself. The only way to write a safe MERGE SQL statement is with an extension to the standard... Principle of minimal extension would mean we only need to support RAISE ERROR, to allow people to specify they actively want statement to fail if the list of WHEN clauses does not produce a match. > > Personally, I would prefer the default action to be RAISE ERROR or > > similar. Otherwise its just too easy to get complex logic wrong and lose > > a few rows without noticing. If that was the case then you would > > definitely need DO NOTHING when you explicitly wanted to lose a few > > rows. > > > > You may think that's a bit strong, but consider that PostgreSQL uses > > default => ERROR in vast majority of switch() statements. I think its a > > safe coding practice and the annoyance of having run-time errors is much > > better than losing rows. > > > > The INSERT DEFAULT VALUES was behaviour taken from another DBMS, its not > > part of the standard AFAICS. > > What does the standard say about this? We should follow the standard, I > don't see enough reason to deviate here. I checked the standard before commenting previously and have done so again here. I can't see anything that refers to this (in SQL:2008), either way. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |