On Wed, Feb 7, 2018 at 7:51 PM, Pavan Deolasee <pavan.deola...@gmail.com> wrote: > I understand getting EPQ semantics right is very important. Can you please > (once again) summarise your thoughts on what you think is the *most* > appropriate behaviour? I can then think how much efforts might be involved > in that. If the efforts are disproportionately high, we can discuss if > settling for some not-so-nice semantics, like we apparently did for > partition key updates.
I personally believe that the existing EPQ semantics are already not-so-nice. They're what we know, though, and we haven't actually had any real world complaints, AFAIK. My concern is mostly just that MERGE manages to behave in a way that actually "provides a single SQL statement that can conditionally INSERT, UPDATE or DELETE rows, a task that would otherwise require multiple procedural language statements", as the docs put it. As long as MERGE manages to do something as close to that high level description as possible in READ COMMITTED mode (with our current semantics for multiple statements in RC taken as the baseline), then I'll probably be happy. Some novel new behavior -- "EPQ with a twist"-- is clearly necessary. I feel a bit uneasy about it because anything that anybody suggests is likely to be at least a bit arbitrary (EPQ itself is kind of arbitrary). We only get to make a decision on how "EPQ with a twist" will work once, and that should be a decision that is made following careful deliberation. Ambiguity is much more likely to kill a patch than a specific technical defect, at least in my experience. Somebody can usually just fix a technical defect. > I am sorry, I know you and Simon have probably done that a few times already > and I should rather study those proposals first. So it's okay if you don't > want to repeat those; I will work on them next week once I am back from > holidays. Unfortunately, I didn't get very far with Simon on this. I didn't really start talking about this until recently, though, so it's not like you missed much. The first time I asked Simon about this was January 23rd, and I first proposed something about 10 days ago. Something very tentative. (I did ask some questions about EPQ, and even WHEN ... AND quals much earlier, but that was in the specific context of a debate about MERGE's use of ON CONFLICT's speculative insertion mechanism. I consider this to be a totally different discussion, that ended before Simon even posted his V1 patch, and isn't worth spending your time on now.) > TBH I did not consider partitioning any less complex and it was indeed very > complex, requiring at least 3 reworks by me. And from what I understood, it > would have been a blocker too. So is subquery handling and RLS. That's why I > focused on addressing those items while you and Simon were still debating > EPQ semantics. Sorry if I came across as dismissive of that effort. That was certainly not my intention. I am pleasantly surprised that you've managed to move a number of things forward rather quickly. I'll rephrase: while it would probably have been a blocker in theory (I didn't actually weigh in on that), I doubted that it would actually end up doing so in practice (and it now looks like I was right to doubt that, since you got it done). It was a theoretical blocker, as opposed to an open item that could drag on indefinitely despite everyone's best efforts. Obviously details matter, and obviously there are a lot of details to get right outside of RC semantics, but it seems wise to focus on the big risk that is EPQ/RC conflict handling. The only other thing that comes close to that risk is the risk that we'll get stuck on RLS. Though even the RLS discussion may actually end up being blocked on this crucial question of EPQ/RC conflict handling. Did you know that the RLS docs [1] have a specific discussion of the implications of EPQ for users of RLS, and that it mentions doing things like using SELECT ... FOR SHARE to work around the problem? It has a whole example of a scenario that users actually kind of need to know about, at least in theory. RC conflict handling semantics could bleed into a number of other things. I'll need to think some more about RC conflict handling (deciding what "EPQ with a twist" actually means), since I haven't focused on MERGE recently. Bear with me. [1] https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html -- Peter Geoghegan
