Re: [HACKERS] Last gasp
On Sat, Apr 14, 2012 at 2:28 PM, Jay Levitt jay.lev...@gmail.com wrote: Christopher Browne wrote: On Thu, Apr 12, 2012 at 6:11 PM, Jay Levittjay.lev...@gmail.com wrote: Rather than extend the CF app into a trivial-patch workflow app, it might be worth looking at integrating it with github. There's a reluctance to require a proprietary component that could disappear on us without notice. Excellent point. I was thinking that GitHub's API would allow archival exporting to counter that, along the lines of let's take advantage of it for the next five years until it goes south, and THEN we could write our own. But I can see how that might not be the best choice for a project that expects to preserve history for a few decades. GitHub does offer an enterprise version that you can self-host, but it seems to be priced per-user and intended for solely intranet use. If the feature set is desirable, though, I wonder if Postgres is big/high profile enough for them to figure out some sort of better arrangement. They *love* it when big open-source projects use GitHub as their public repo - they'll email and blog announcements about it - and if there's interest I'd be happy to open a conversation with them. The existence of git itself is a result of *exactly* that circumstance, as Linux kernel developers had gotten dependent on BitKeeper, whereupon the owner decided to take his toys home, at which point they were left bereft of their SCM tool. http://kerneltrap.org/node/4966 Good history lesson there, with a great outcome. I expect that it would be more worthwhile to look into enhancements to git workflow such ashttp://code.google.com/p/gerrit/ Gerrit. I don't know that Gerrit is THE answer, but there are certainly projects that have found it of value, and it doesn't have the oops, it's proprietary problem. I've looked at it in conjunction with Jenkins CI; it looked nice but was way too heavy-weight for a four-person startup (what's code review?). It's probably much more suitable for this sized project. Gerrit's a full-featured code review app with a tolerable UI; I was thinking of GitHub more as a great lightweight UI for doc patches and other trivial patches where you might have lots of casual review and comments but no need for, say, recording regression tests against each patch version. e.g.: https://github.com/rails/rails/pull/5730 Also, for doc patches, GitHub has the great advantage of in-place editing right from the web UI. I don't know if GitHub's pull request or Gerrit is a good tool (I doubt, actually), but I've been thinking how we could improve our review process in terms of both of human process perspective and tool process. As we have our simple CF app (while there are a bunch of tools like JIRA or something), I'd think we could have our own review UI connected to the rest of our toolset including CF app. I know we want the mail archive history of the whole discussion, but still giving feedback to the submitter via email is hard-work and the successors cannot read it entirely. From a human- rather than technology-oriented perspective: I was shocked to find that you folks *WANT* reviews from non-contributors. It was my assumption as a newcomer that if I don't feel well-versed enough to submit patches yet, the last thing you'd want me to do was to look over someone else's patch and say Yeah, that looks good, any more than I care if my mom thinks my latest web app is very nice. I see now that the Reviewing a Patch wiki page explains this, but maybe this info should be pushed higher into the docs and web site; a How can I contribute page, open calls for reviewers on the non-hackers mailing lists, things like that. Or maybe just make the wiki page bright red and blink a lot. I found myself enjoying reviewing other patches where I don't have strong knowledge. I strongly believe we should encourage more and more people who haven't worked particular patches in that area, to review patches. The more eyeballs there are, the more quality we get. Thanks, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Clobbered parameter names via DECLARE in PL/PgSQL
Hello hackers, It turns out that in a PL/PgSQL function, you can DECLARE a variable using the same name as one of the function parameters. This has the effect of clobbering the parameter, for example: CREATE OR REPLACE FUNCTION declare_clobber(foo int) RETURNS int LANGUAGE plpgsql AS $$ DECLARE foo text; BEGIN RETURN foo; END; $$; SELECT declare_clobber(1); == NULL On the other hand, PL/PgSQL does protect against duplicate definitions within DECLARE: CREATE OR REPLACE FUNCTION declare_clobber(foo int) RETURNS int LANGUAGE plpgsql AS $$ DECLARE foo int; foo text; BEGIN RETURN foo; END; $$; == ERROR: duplicate declaration at or near foo And it also protects against using a DECLAREd name as a parameter alias: CREATE OR REPLACE FUNCTION declare_clobber(foo int) RETURNS int LANGUAGE plpgsql AS $$ DECLARE bar int; bar ALIAS FOR $1; BEGIN RETURN bar; END; $$; == ERROR: duplicate declaration at or near bar I would suggest that if the user DECLAREs a variable with the same name as a parameter, it is very evidently a programming error, and we should raise the same duplicate declaration error. I haven't yet looked at how difficult this would be to fix, but if there are no objections I would like to attempt a patch. Cheers, BJ -- 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] Clobbered parameter names via DECLARE in PL/PgSQL
2012/4/15 Brendan Jurd dire...@gmail.com: Hello hackers, It turns out that in a PL/PgSQL function, you can DECLARE a variable using the same name as one of the function parameters. This has the effect of clobbering the parameter, for example: CREATE OR REPLACE FUNCTION declare_clobber(foo int) RETURNS int LANGUAGE plpgsql AS $$ DECLARE foo text; BEGIN RETURN foo; END; $$; SELECT declare_clobber(1); == NULL On the other hand, PL/PgSQL does protect against duplicate definitions within DECLARE: CREATE OR REPLACE FUNCTION declare_clobber(foo int) RETURNS int LANGUAGE plpgsql AS $$ DECLARE foo int; foo text; BEGIN RETURN foo; END; $$; == ERROR: duplicate declaration at or near foo And it also protects against using a DECLAREd name as a parameter alias: CREATE OR REPLACE FUNCTION declare_clobber(foo int) RETURNS int LANGUAGE plpgsql AS $$ DECLARE bar int; bar ALIAS FOR $1; BEGIN RETURN bar; END; $$; == ERROR: duplicate declaration at or near bar I would suggest that if the user DECLAREs a variable with the same name as a parameter, it is very evidently a programming error, and we should raise the same duplicate declaration error. I haven't yet looked at how difficult this would be to fix, but if there are no objections I would like to attempt a patch. I disagree - variables and parameters are in different namespace so you can exactly identify variable and parameter. More - it is compatibility break. If plpgsql_check_function exists, then this check can be implemented as warning. Regards Pavel Cheers, BJ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Clobbered parameter names via DECLARE in PL/PgSQL
On 15 April 2012 17:55, Pavel Stehule pavel.steh...@gmail.com wrote: 2012/4/15 Brendan Jurd dire...@gmail.com: It turns out that in a PL/PgSQL function, you can DECLARE a variable using the same name as one of the function parameters. This has the effect of clobbering the parameter, for example: ... I would suggest that if the user DECLAREs a variable with the same name as a parameter, it is very evidently a programming error, and we should raise the same duplicate declaration error. I haven't yet looked at how difficult this would be to fix, but if there are no objections I would like to attempt a patch. I disagree - variables and parameters are in different namespace so you can exactly identify variable and parameter. More - it is compatibility break. They may technically be in different namespaces, but the fact that the declared variable quietly goes ahead and masks the parameter locally, seems like a recipe for unexpected consequences. It certainly was in my case, and I doubt I'm the first or the last to make that mistake. Under these conditions, you now have foo which refers to the variable, and declare_clobber.foo which refers to the parameter. Not exactly a model of clarity, and it's also quite easy to miss the part of the PL/PgSQL docs mentioning this notation. Perhaps it's a failure of imagination on my part, but I can't think of a legitimate reason for a programmer to deliberately use the same name to refer to a declared variable and a function parameter. What would be the benefit? Cheers, BJ -- 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] Clobbered parameter names via DECLARE in PL/PgSQL
2012/4/15 Brendan Jurd dire...@gmail.com: On 15 April 2012 17:55, Pavel Stehule pavel.steh...@gmail.com wrote: 2012/4/15 Brendan Jurd dire...@gmail.com: It turns out that in a PL/PgSQL function, you can DECLARE a variable using the same name as one of the function parameters. This has the effect of clobbering the parameter, for example: ... I would suggest that if the user DECLAREs a variable with the same name as a parameter, it is very evidently a programming error, and we should raise the same duplicate declaration error. I haven't yet looked at how difficult this would be to fix, but if there are no objections I would like to attempt a patch. I disagree - variables and parameters are in different namespace so you can exactly identify variable and parameter. More - it is compatibility break. They may technically be in different namespaces, but the fact that the declared variable quietly goes ahead and masks the parameter locally, seems like a recipe for unexpected consequences. It certainly was in my case, and I doubt I'm the first or the last to make that mistake. I agree so this issue is relative usual. But I don't think so we can use too hard solution like exclusion of parameter names, because it just has not support in standard or PL/SQL or SQL/PSM. And we introduce a few years ago different solution - function's namespace. Under these conditions, you now have foo which refers to the variable, and declare_clobber.foo which refers to the parameter. Not exactly a model of clarity, and it's also quite easy to miss the part of the PL/PgSQL docs mentioning this notation. it is not well documented and should be documented better: http://www.postgresql.org/docs/9.1/static/plpgsql-structure.html 39.2. Structure of PL/pgSQL Note: There is actually a hidden outer block surrounding the body of any PL/pgSQL function. This block provides the declarations of the function's parameters (if any), as well as some special variables such as FOUND (see Section 39.5.5). The outer block is labeled with the function's name, meaning that parameters and special variables can be qualified with the function's name. 39.3.1. Declaring Function Parameters Note: These two examples are not perfectly equivalent. In the first case, subtotal could be referenced as sales_tax.subtotal, but in the second case it could not. (Had we attached a label to the inner block, subtotal could be qualified with that label, instead. It should be documented better. Perhaps it's a failure of imagination on my part, but I can't think of a legitimate reason for a programmer to deliberately use the same name to refer to a declared variable and a function parameter. What would be the benefit? it depends on level of nesting blocks. For simple functions there parameter redeclaration is clean bug, but for more nested blocks and complex procedures, there should be interesting using some local variables with same identifier like some parameters and blocking parameter's identifier can be same unfriendly feature like RO parameters in previous pg versions. I understand your motivation well, but solution should be warning, not blocking. I think. Regards Pavel Cheers, BJ -- 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] query planner does not canonicalize infix operators
Tom Lane t...@sss.pgh.pa.us writes: More generally, I'm not prepared to buy into the idea that the planner should be expected to recognize alternate spellings of the same expression. There are too many variants of that idea that are infeasible either because the planner doesn't have the necessary knowledge, or it does but trying to recognize the equivalence would cost an impractical number of planning cycles. Neither of those objections apply to replace an operator by its underlying function; but they do apply to other comparable requests we've gotten such as recognize that x + 0 is the same as x or x + 1 is the same as 1 + x. Looks like we're missing out some operator properties, like the neutral element and if the operator is transitive, commutative or associative. I think I remember us talking about how knowing about operators being associative would also help optimize a class of join problems. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] Clobbered parameter names via DECLARE in PL/PgSQL
On 15 April 2012 18:54, Pavel Stehule pavel.steh...@gmail.com wrote: 2012/4/15 Brendan Jurd dire...@gmail.com: Perhaps it's a failure of imagination on my part, but I can't think of a legitimate reason for a programmer to deliberately use the same name to refer to a declared variable and a function parameter. What would be the benefit? it depends on level of nesting blocks. For simple functions there parameter redeclaration is clean bug, but for more nested blocks and complex procedures, there should be interesting using some local variables with same identifier like some parameters and blocking parameter's identifier can be same unfriendly feature like RO parameters in previous pg versions. I understand your motivation well, but solution should be warning, not blocking. I think. I can accept that ... but I wonder about the implementation of such a warning. Can we raise a WARNING message on CREATE [OR REPLACE] FUNCTION? If so, should there be a way to switch it off? If so, would this be implemented globally, or per-function? Would it be a postgres run-time setting, or an extension to CREATE FUNCTION syntax, or something within the PL/pgSQL code (like Perl's 'use strict')? Cheers, BJ -- 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] [BUGS] BUG #6572: The example of SPI_execute is bogus
2012-04-14 18:15 keltezéssel, Peter Eisentraut írta: On lör, 2012-04-14 at 08:23 -0400, Robert Haas wrote: On Sat, Apr 14, 2012 at 3:27 AM, Pavel Stehulepavel.steh...@gmail.com wrote: It has a lot of sense. Without it, it's very difficult to do logical replication on a table with no primary key. (Whether or not people should create such tables in the first place is, of course, beside the point.) I am not against to functionality - I am against just to syntax DELETE FROM tab LIMIT x because is it ambiguous what means: DELETE FROM tab RETURNING * LIMIT x What's ambiguous about that? I suppose one could wonder whether the LIMIT applies to the deleting or just the returning. Ambigous only in this order. LIMIT x RETURNING * wouldn't be. -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ -- 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] [BUGS] BUG #6572: The example of SPI_execute is bogus
2012/4/15 Boszormenyi Zoltan z...@cybertec.at: 2012-04-14 18:15 keltezéssel, Peter Eisentraut írta: On lör, 2012-04-14 at 08:23 -0400, Robert Haas wrote: On Sat, Apr 14, 2012 at 3:27 AM, Pavel Stehulepavel.steh...@gmail.com wrote: It has a lot of sense. Without it, it's very difficult to do logical replication on a table with no primary key. (Whether or not people should create such tables in the first place is, of course, beside the point.) I am not against to functionality - I am against just to syntax DELETE FROM tab LIMIT x because is it ambiguous what means: DELETE FROM tab RETURNING * LIMIT x What's ambiguous about that? I suppose one could wonder whether the LIMIT applies to the deleting or just the returning. Ambigous only in this order. LIMIT x RETURNING * wouldn't be. but theoretically you can has two LIMIT clauses in one SQL statements DELETE FROM tab LIMIT n RETURNING * LIMIT m without updatable CTE it is probably only one solution, but because we have UCTE, then we don't need this construct. Regards Pavel -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ -- 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] Clobbered parameter names via DECLARE in PL/PgSQL
2012/4/15 Brendan Jurd dire...@gmail.com: On 15 April 2012 18:54, Pavel Stehule pavel.steh...@gmail.com wrote: 2012/4/15 Brendan Jurd dire...@gmail.com: Perhaps it's a failure of imagination on my part, but I can't think of a legitimate reason for a programmer to deliberately use the same name to refer to a declared variable and a function parameter. What would be the benefit? it depends on level of nesting blocks. For simple functions there parameter redeclaration is clean bug, but for more nested blocks and complex procedures, there should be interesting using some local variables with same identifier like some parameters and blocking parameter's identifier can be same unfriendly feature like RO parameters in previous pg versions. I understand your motivation well, but solution should be warning, not blocking. I think. I can accept that ... but I wonder about the implementation of such a warning. Can we raise a WARNING message on CREATE [OR REPLACE] FUNCTION? If so, should there be a way to switch it off? If so, would this be implemented globally, or per-function? Would it be a postgres run-time setting, or an extension to CREATE FUNCTION syntax, or something within the PL/pgSQL code (like Perl's 'use strict')? We can raise warning from CREATE OR REPLACE FUNCTION - but I would to like have plpgsql_check_function inside core - and it is better place for this and similar issues. Now we talk about features in 9.3, and there check_function should be. Regards Pavel Cheers, BJ -- 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] Last gasp
On Fri, Apr 6, 2012 at 8:19 AM, Tom Lane t...@sss.pgh.pa.us wrote: Greg Smith g...@2ndquadrant.com writes: On 04/05/2012 04:27 PM, Simon Riggs wrote: It's shocking since after months of work and an especially extended edition CF, we expect people to deliver something, not just shunt the whole thing off as rejected with 1 days's notice to alter that outcome. I don't think this is being fair to Robert. If we're going to ship a release at all, somebody's got to be willing to say no. Personally, been there, done that, got the t-shirt [1]. Robert's just pointing out what has to be pointed out. Just returned from a week away, so I'm chipping in on key points only. I accept the command trigger patch is gone now and I would add comments only about our processes. The problem remains that we have wasted many months of development and slipped a release on what appears to be an important, universally popular feature that had bucket loads of early planning. We shouldn't hide from recognising that as an issue. I completely agree that somebody has to be willing to say No, since we all agree that the default for any patch is non-acceptance. My first observation is that if No is received early enough for something to be done, then the outcome could be different. It was not clear that this important patch was going to be totally refused and many people have expressed their surprise about that. Noah signalled to everybody that the FK locks patch was likely to be rejected and a number of us have tried hard to save that, unluckily as it turns out. So an early No helped people allocate their time on what they considered to be important. In contrast the Command Triggers and FKs for arrays patches received a No so late that nothing could be done. So I fully agree that people should say No, but the key point is *when* they say it. For the future, I think we should have a triage week as the first week in each CF; lets shake out the No comments early - in some cases nothing can be done and we can refocus attention onto important topics. Second, my point was that No should not be applied in black/white form if at all possible. If some aspect of a patch is unworkable, it may be possible to provide some of the functionality, rather than simply nothing at all. That wasn't possible with FK locks, and maybe it was possible with command triggers. I do consider it the responsibility of a reviewer to salvage as much as is easily and reasonably possible from each contribution. In most cases that requires a few words from the reviewer, since many patch contributors listen carefully to what is said and try hard to make changes that work. Frequently I see reviewers simply making authors dance around with various additions and tweaks; all very well if the patch is acceptable, but its just a waste of time if there is no route to acceptance laid out clearly by the reviewer. That's something I've mentioned before: the reviewer *must* supply a list of things which, if solved, would allow the patch to be accepted. Clearly there will always be last minute issues that cause rejection. If we can get our information transfer a little slicker we'd be able to find more review time. Instead of spending months on their own dead patches, people would be available to assist on those with a chance to be saved. Our problem is not lack of resource, it is ineffective delegation. As Hannu points out, he didn't know the patch would be rejected, so he didn't know help was needed to save something useful. I considered that the job of the CF manager, but perhaps it is was not. In any case, it would seem best for the future if the CF manager was not also a committer, since those people are clearly too busy to do both roles as well as the project needs them to be. Just as our processes evolved into the creation of CFs and a CF manager, we must evolve again towards someone/team whose main task is ensuring that the delegation problem is solved. That won't work by bossing people around, it has to work by informing and encouraging people to contribute in the ways that they wish. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] index-only scans vs. Hot Standby, round two
On Fri, Apr 13, 2012 at 5:33 PM, Robert Haas robertmh...@gmail.com wrote: Currently, we have a problem with index-only scans in Hot Standby mode: the xmin horizon on the standby might lag the master, and thus an index-only scan might mistakenly conclude that no heap fetch is needed when in fact it is. I suggested that we handle this by suppressing generation of index-only scan plans in Hot Standby mode, but Simon, Noah, and Dimitri were arguing that we should instead do the following, which is now on the open items list: * Make XLOG_HEAP2_VISIBLE records generate recovery snapshot conflicts so that IndexOnlyScans work on Hot Standby ... snip very long email /snip Luckily its much simpler than all of that suggests. It'll take a few hours for me to write a short reply but its Sunday today, so that will happen later. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Last gasp
On Wed, Apr 11, 2012 at 6:59 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Apr 11, 2012 at 1:39 PM, Joshua Berkus j...@agliodbs.com wrote: Ultimately, we're herding cats here. I don't think you're going to get the community to suddenly be willing to march in lockstep instead. If you, Peter, Simon, Robert, Heikki, Magnus, Peter G., Greg, Bruce and Andrew agreed on a calendar-driven, mostly unambiguous process and adhered to that process, then the one or two people who didn't follow along wouldn't matter. Everyone else would follow you. The reason things are chaotic now is that our lead committers do not have consensus and are even inconsistent from CF to CF individually. In other words: the problem is only unsolvable because *you* think it's unsolvable. If you decide the problem is solvable, you already have the means to solve it. That's a somewhat bizarre list of people. It both includes people who haven't expressed many concerns about our process one way or the other and excludes some who have. At any rate, clearly the problem is exactly that there isn't consensus on this. I would generally say that Tom, Greg Smith, and I are pretty close together on this issue, and Peter G., Simon, and Dimitri are pretty close together on this issue, but with a big gap in between those two groups. I am less clear on how everyone else feels, but I think that saying that all we need is to get consensus among those people is to define the problem, not the solution. Having just come back from a week away, I'm not really clear what you mean by this issue. It worries me greatly to see my name in a divisive list like that. There are no teams here; each issue needs to be judged on its own, independently of each other and without regard to the person speaking. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] documentation bug - missing info about unpackaged control files for extension
Hello is somewhere documented sense of foo--unpackaged--version.sql files? Regards Pavel -- 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] Clobbered parameter names via DECLARE in PL/PgSQL
On 04/15/2012 03:49 AM, Brendan Jurd wrote: Hello hackers, It turns out that in a PL/PgSQL function, you can DECLARE a variable using the same name as one of the function parameters. This has the effect of clobbering the parameter, for example: ... I would suggest that if the user DECLAREs a variable with the same name as a parameter, it is very evidently a programming error, and we should raise the same duplicate declaration error. I haven't yet looked at how difficult this would be to fix, but if there are no objections I would like to attempt a patch. This doesn't need fixing, IMNSHO. The name isn't clobbered and the partameter is accessible. The docs state: Any statement in the statement section of a block can be a subblock. Subblocks can be used for logical grouping or to localize variables to a small group of statements. Variables declared in a subblock mask any similarly-named variables of outer blocks for the duration of the subblock; but you can access the outer variables anyway if you qualify their names with their block's label. ... There is actually a hidden outer block surrounding the body of any PL/pgSQL function. This block provides the declarations of the function's parameters (if any), as well as some special variables such as FOUND (see Section 39.5.5). The outer block is labeled with the function's name, meaning that parameters and special variables can be qualified with the function's name. Note that you can label the outermost block of the function body, as the example in the docs shows. cheers andrew -- 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] Last gasp
On Sat, Apr 7, 2012 at 10:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: [ among other good points ] ... On a related note, letting CommitFests go on for three months because there's insufficient reviewer activity to get them done in one or two is, in my opinion, not much of a solution. If there's even less reviewer activity next time, are we going to let it go on for four months? Six months? Twelve months? At some point, it boils down to we're just going to stop accepting patches for an indefinite period of time. Yuck. Yeah, this is something I was thinking about yesterday. In the first couple of release cycles with the CommitFest process, we were willing to let the last fest of a release cycle go on for as long as it takes, or at least that was what I felt the policy to be. This time we eventually gave up and declared closure, but in hindsight we should likely have done that a month earlier. The fact of the matter is that quite a few of the patches we were dealing with were *not* ready to commit, or even close to that, at the start of the fest. If it weren't the last fest they would have gotten marked Returned With Feedback a lot sooner. I wonder whether we ought to have a preset schedule for last fests just like the others. I'd be willing to let them run, say, 2 months instead of 1, but no deadline at all risks turning the whole affair into a death march, which is no fun for anybody and threatens the quality of the end result too. There's too much temptation to commit patches that are not really ready, just to get them out of the way. In short, the idea of strongly calendar-driven releases looks more and more attractive to me the more times we go through this process. If your patch isn't ready on date X, then it's not getting into this release; but there'll be another bus coming along before long. Stretching out release cycles to get in those last few neat features just increases the pressure for more of the same, because people don't know how long it will be to the next release. A 2 month hard deadline seems enough for me. I spoke in favour of reasonableness previously, but that didn't mean no dates at all. If we can do Triage Week at the beginning, that will keep out the ones that aren't ready and allow us to focus our attention on the ones we really care about. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Last gasp
On Sat, Apr 7, 2012 at 9:51 PM, Robert Haas robertmh...@gmail.com wrote: I think this basically just boils down to too many patches and not enough people. I was interested in Command Triggers from the beginning of this CommitFest, and I would have liked to pick it up sooner, but there were a LOT of patches to work on for this CommitFest. The first three CommitFests of this cycle each had between 52 and 60 patches, while this one had 106 which included several very complex and invasive patches, command triggers among them. So there was just a lot more to do, and a number of the people who submitted all of those patches didn't do a whole lot to help review them, sometimes because they were still furiously rewriting their submissions. It's not surprising that more patches + fewer reviewers = each patch getting less attention, or getting it later. This is a good point. The current process lacks inherent scalability. I would really like us to enforce a policy of 1 patch = 1 review. That way we automatically have enough review time, no matter how many patches we get. If we don't enforce that, then patch sponsors are more likely to take the attitude that review isn't something they need to pay for, just the dev work. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] Last gasp
On Tue, Apr 10, 2012 at 3:26 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Christopher Browne cbbro...@gmail.com wrote: Robert Haas robertmh...@gmail.com wrote: CommitFests are a time for patches that are done or very nearly done to get committed, and a time for other patches to get reviewed if they haven't been already. If we make it clear that the purpose of the CommitFest is to assess whether the patch is committable, rather than to provide an open-ended window for it to become committable, we might do better. Yeah, I think there's pretty good room for a +1 on that. Yeah, +1 for sure. The top comment that has been +1'd presents just 2 states; 1 is deliberately phrased to be ridiculous, so of course everybody will vote for the other one. What is missing there is all of the other possible states between those two extremes. Let me re-phrase that: I think open ended CFs aren't much use. Hard edges are needed. But having said that, I can't think of a major feature that didn't have some tweaking after commit, and after end of CF. One other sort of mechanical test which I think can and should be applied to patches submitted to the last CF is that if *at the start of the CF* the patch doesn't apply, compile, pass regression tests, and demonstrably provide the functionality claimed for the patch, it should not be a candidate for inclusion in the release. A patch on which the author is continuing to work even in the absence of review should be considered a WIP want feedback submission; it should not be allowed to constitute a placeholder for inclusion in the release. It's one thing if review turns up corner case bugs missed by the author; it's quite another if there is a month or two of solid development left to be done. The CF period is not the time for now I'll get serious about wrapping this up. Agreed. But again, mistakes do happen, so reasonableness is required. CommitFests should be a finalisation period where submissions get tweaked to fix problems/bugs and allow them to be committed by the end of the CF. Again, in some cases that might be on the last day of the CF (else its not the last day...). In the past, patches could starve on the queue for very long periods, sometimes years. Having a too-harsh process makes it then easy to go back to the old way of quickly bouncing things that lack popularity from committers. Anyway, this discussion is just the annual make things better discussion. Our process was good to start with and has get better each release for years and years now, so objectively we are doing quite well. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] documentation bug - missing info about unpackaged control files for extension
On 15.04.2012 13:47, Pavel Stehule wrote: is somewhere documented sense of foo--unpackaged--version.sql files? See section 35.15.4. Extension Updates, paragraph starting with The update mechanism can be used to solve an important special case: converting a loose collection of objects into an extension. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] query planner does not canonicalize infix operators
Dimitri Fontaine dimi...@2ndquadrant.fr writes: Looks like we're missing out some operator properties, like the neutral element and if the operator is transitive, commutative or associative. I think I remember us talking about how knowing about operators being associative would also help optimize a class of join problems. We do understand, and use, transitivity for btree equality operators (cf mergejoin planning, EquivalenceClasses, etc). I have limited enthusiasm for introducing a more general concept, because it seems like doing anything with it would add a great deal more planning effort for (typically) little reward. I can imagine cases where, say, deducing a c from a b and b c would be helpful ... but they don't come up in common queries. regards, tom lane -- 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] Clobbered parameter names via DECLARE in PL/PgSQL
Pavel Stehule pavel.steh...@gmail.com writes: We can raise warning from CREATE OR REPLACE FUNCTION - but I would to like have plpgsql_check_function inside core - and it is better place for this and similar issues. I agree. This is a perfectly legal use of nested declaration scopes, so it would be totally inappropriate to complain about it in normal use of a plpgsql function. On the other hand, it would probably be sane and useful for CHECK FUNCTION to flag any case where an inner declaration shadows an outer-scope name (not only the specific case of topmost block vs function parameter). regards, tom lane -- 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] Last gasp
Simon Riggs si...@2ndquadrant.com writes: I completely agree that somebody has to be willing to say No, since we all agree that the default for any patch is non-acceptance. My first observation is that if No is received early enough for something to be done, then the outcome could be different. It was not clear that this important patch was going to be totally refused and many people have expressed their surprise about that. Noah signalled to everybody that the FK locks patch was likely to be rejected and a number of us have tried hard to save that, unluckily as it turns out. So an early No helped people allocate their time on what they considered to be important. In contrast the Command Triggers and FKs for arrays patches received a No so late that nothing could be done. I think this is a rather unfair summary of the history. It was clear very early in the CF that people thought Command Triggers had major design problems, and Dimitri was doing significant rewrites to try to fix that. Anyone who did not think that patch was at serious risk of not being committed simply wasn't paying attention. Given the range of different design options that were considered, I think it's just as well that the patch has been put off till 9.3: we will probably get a better feature than if it had made it this time, and we will certainly be taking less schedule risk. I will agree that the array-FKs patch got the short end of the stick: had we been willing to let the CF go on for another month, it would have gotten looked at more carefully, and quite possibly committed. But once we made the decision to cut off the CF, there was not time to look at it closely enough. Again, to my mind this was mostly a risk minimization decision: when you make up a fundamental feature out of whole cloth, there's substantial risk that you didn't get the design right. At this point we don't have enough time for the feature to settle in and get used before 9.2 will ship and it'll be too late to correct any design problems. The more general point here is that the last fest of a release cycle is the worst possible time to be landing big, destabilizing patches. I think we ought to be conservative at this stage of the cycle, in hopes of keeping beta phase short and predictable. regards, tom lane -- 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] Last gasp
Simon Riggs si...@2ndquadrant.com writes: If we can do Triage Week at the beginning, that will keep out the ones that aren't ready and allow us to focus our attention on the ones we really care about. I think there's some merit in this idea, but there needs to be time allocated to examine all the large patches before we make any hard go/no-go decisions. Maybe we could make such choices about two weeks in, rather than at the very start? Another thought is that triage is probably not the right image to have here. Patches that are obviously going to be rejected altogether are not that common, and they don't take up much time when they do show up. Where I think we have been fooling ourselves is in failing to tell the difference between a patch that is committable in the current fest, versus one that is still WIP and is going to need more development time. Now the latter category *is still deserving of review*, just as much as the former. So even if we can correctly determine early on which patches are WIP, it doesn't mean we should bounce them out of the fest. But it would mean they get approached differently by the reviewers. regards, tom lane -- 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] Clobbered parameter names via DECLARE in PL/PgSQL
2012/4/15 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: We can raise warning from CREATE OR REPLACE FUNCTION - but I would to like have plpgsql_check_function inside core - and it is better place for this and similar issues. I agree. This is a perfectly legal use of nested declaration scopes, so it would be totally inappropriate to complain about it in normal use of a plpgsql function. On the other hand, it would probably be sane and useful for CHECK FUNCTION to flag any case where an inner declaration shadows an outer-scope name (not only the specific case of topmost block vs function parameter). yes, it is very simple check there. There should be levels of warnings in future and performance or semantic warnings. But, we don't need to increase complexity of CHECK FUNCTION now. A design of CHECK FUNCTION was rich for this purposes. And we need to find way to push plpgsql_check_function to core first. Regards Pavel regards, tom lane -- 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] [BUGS] BUG #6572: The example of SPI_execute is bogus
Robert Haas robertmh...@gmail.com writes: On Thu, Apr 5, 2012 at 2:39 AM, Hitoshi Harada umi.tan...@gmail.com wrote: On Wed, Apr 4, 2012 at 8:00 AM, Tom Lane t...@sss.pgh.pa.us wrote: Given the lack of complaints since 9.0, maybe we should not fix this but just redefine the new behavior as being correct? But it seems mighty inconsistent that the tuple limit would apply if you have RETURNING but not when you don't. In any case, the ramifications are wider than one example in the SPI docs. To be honest, I was surprised when I found tcount parameter is said to be applied to even INSERT. I believe people think that parameter is to limit memory consumption when returning tuples thus it'd be applied for only SELECT or DML with RETURNING. So I'm +1 for non-fix but redefine the behavior. Who wants to limit the number of rows processed inside the backend, from SPI? Yeah. Okay, apparently nobody cares about RETURNING behaving differently from non-RETURNING, so the consensus is to redefine the current behavior as correct. That means what we need is to go through the docs and see what places need to be updated (and, I guess, back-patch the changes to 9.0). I will get to this if nobody else does, but not right away. I think it would be a good idea for UPDATE and DELETE to expose a LIMIT option, but I can't really see the virtue in making that functionality available only through SPI. FWIW, I'm not excited about that. You can get well-defined behavior today from a SELECT/LIMIT drawing from a writable CTE (namely, that the UPDATE/DELETE runs to completion but you only see a subset of its RETURNING result). LIMIT directly on the UPDATE/DELETE would be ill-defined, unless perhaps you want to also invent a way of specifying the order in which rows get selected for update; but I don't want to go there. regards, tom lane -- 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] documentation bug - missing info about unpackaged control files for extension
2012/4/15 Heikki Linnakangas heikki.linnakan...@enterprisedb.com: On 15.04.2012 13:47, Pavel Stehule wrote: is somewhere documented sense of foo--unpackaged--version.sql files? See section 35.15.4. Extension Updates, paragraph starting with The update mechanism can be used to solve an important special case: converting a loose collection of objects into an extension. I understand better now. Thank you Pavel -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Fix PL/Python metadata when there is no result
On fre, 2012-02-10 at 17:44 +0100, Jean-Baptiste Quenot wrote: Dear hackers, Thanks for the work on PLPython result metadata, it is very useful! I just came across a crash when trying to access this metadata on the result of an UPDATE, which obviously cannot return any tuple (unless you specify a RETURNING clause maybe?). Please find attached a patch that solves this issue. Instead of a PG crash, we get the following message: ERROR: plpy.Error: no result fetched After much soul-searched about the API I have now committed your fix that throws the exception. Thanks. -- 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] Last gasp
On Sun, Apr 15, 2012 at 4:50 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think this is a rather unfair summary of the history. It was clear very early in the CF that people thought Command Triggers had major design problems, and Dimitri was doing significant rewrites to try to fix that. Anyone who did not think that patch was at serious risk of not being committed simply wasn't paying attention. Fair comment, since I was definitely not paying attention. My I-Want-a-Pony idea is some kind of rating system that allows us all to judge patches in terms of importance/popularity, complexity and maturity. I guess a Balanced Scorecard for the development process. So we can all see whats going on. We already do this when we speak to each other in hushed tones that so-and-so a patch looks unlikely etc.. If we could do that more openly it would help. The more general point here is that the last fest of a release cycle is the worst possible time to be landing big, destabilizing patches. I think we ought to be conservative at this stage of the cycle, in hopes of keeping beta phase short and predictable. There is a definite selection effect that means the bigger the patch the more likely it is to land later in the release cycle, regrettably. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] how to create a non-inherited CHECK constraint in CREATE TABLE
On Thu, Apr 12, 2012 at 10:50:31AM +0530, Nikhil Sontakke wrote: CHECK NO INHERIT sounds fine to me; will that display ALTER TABLE ONLY x as the one true way of doing this? s/display/displace/, I think you meant? Yeah, that's what I understand the proposal to be. +1 for that proposal. Displace yes. It would error out if someone says ALTER TABLE ONLY... CHECK (); suggesting to use the ONLY with the CHECK. I'd say the behavior for that case can revert to the PostgreSQL 9.1 behavior. If the table has children, raise an error. Otherwise, add an inheritable CHECK constraint, albeit one lacking inheritors at that moment. Thanks, nm -- 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] Last gasp
On 04/14/2012 06:03 PM, Robert Haas wrote: If someone's work is going to require substantial revision, it is much better and much less work to do that revision before the code goes into our repository (and particularly, before it gets released) rather than after. I would think one of the major factors in deciding who should be able to commit code is whether they'll likely to commit substandard material. Someone who reviews and is seen to mark patches ready for commit, and they're not, should surely not be committing things either. The review process we have now does a pretty good job of identifying which submissions are baked and which aren't. I'd never argue that there should be more people to commit so they can slip in half baked material. Someone doesn't need to know how to bake everything to be useful as a committer though; they just need to know what they can and can't handle. And, on a related note, I am having a hard time imagining that it's a good idea to give very many people commit bits primarily so that they can commit their own work. If someone has committed their own work after that submission went through the full CF and review process, I don't see a lot of harm in them committing the result. I'd certainly never suggest that the reason to have more committers is so that the CF workflow was easier to subvert. Yes, there are problems with having enough reviewers and ushering large patches through the CF process. But it seems to me there are a fair number of submission that start solid, turn excellent through thorough review, and once they do hit ready for committer they could be picked up for commit by more people than the existing very small pool (committers who process other people's submissions regularly). Also, and I'm aware this is a more controversial point, I believe there are some people who would do more review if they could just move toward committing the stuff that looks good without going through quite as much process. At some times, if you realize something is close and just needs a bit more work, the easy path is to just do it yourself and be done. Non committing reviewers can't get that efficiency boost in the cases it's appropriate. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com -- 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] Last gasp
On 04/14/2012 05:28 PM, Jay Levitt wrote: I see now that the Reviewing a Patch wiki page explains this, but maybe this info should be pushed higher into the docs and web site; a How can I contribute page, open calls for reviewers on the non-hackers mailing lists, things like that. Or maybe just make the wiki page bright red and blink a lot. It is too bad the page we used to have like this on MySpace isn't around anymore. Right now there's some rumbling at multiple companies around things like how to sponsor new features for PostgreSQL. Tying that into a larger page covering the various ways people can contribute is an interesting idea. I think we've only recently crossed the point where there's enough details on pages like Reviewing a Patch that it's clear to people what they should do on the technical side. That pretty well completely work could use something that's more like marketing now though. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com -- 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] Last gasp
On 04/15/2012 05:46 AM, Simon Riggs wrote: Our problem is not lack of resource, it is ineffective delegation. As Hannu points out, he didn't know the patch would be rejected, so he didn't know help was needed to save something useful. I considered that the job of the CF manager, but perhaps it is was not. Note that one of the influences on the death march here was lack of a fully devoted CF manager for the full duration. I did some rabble rousing to get things started in the usual way, but my time for this only budgeted for six weeks in that role. And that was quite optimistic for this one. Trying to quantify how much time investment the CF manager role really involves is one of my important projects to chew on. Whoever ends up doing that should at least have an idea what scale of problem they're getting into. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com -- 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] Last gasp
Excerpts from Alex's message of dom abr 15 01:52:16 -0300 2012: Jay Levitt jay.lev...@gmail.com writes: Alex wrote: I didn't follow this whole thread, but have we considered Redmine[1]? As the resident Ruby is shiny, let's do everything in Rails on my MacBook guy, I'd like to make a statement against interest: I've tried Redmine a few times and it's been painful. Much of the codebase is deprecated, it's slow, it has no meaningful search (in 2012?!), I've seen wiki edits disappear, and at the moment pulling up its own FAQ page at redmine.org times out. Yay, that's totally FUD to me. I've used Redmine a lot, as you know, and I only keep using it because it's a requirement at work. It is certainly not close to usable for general pgsql stuff. (Trac, which we used to use prior to Redmine, was certainly much worse, though). I can't say that it's all that slow, or that there's a problem with the code, or that the search doesn't work right (and I've never had a wiki edit disappear, either, and I've used that a lot). It's just the wrong tool altogether. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] ECPG FETCH readahead
On Tue, Apr 10, 2012 at 07:56:35PM +0200, Boszormenyi Zoltan wrote: With the above, it would be possible to use a comma separated list of -r suboptions, e.g. -r prepare,questionmarks,readahead=16 in one option. Yes, that sounds like a good plan. But of course it's outside the scope of this patch, so we can add this later on. - Also added a note to the documentation about a possible performance trap if a previously written ECPG application uses its own custom readahead via multi-row FETCH statements. I didn't know that before you send this patch. Noah, did you? Frankly, I don't like this at all. If I got it right that means a FETCH N is essantially computed as N times FETCH 1 unless you either add a non-standard option to the DECLARE statement or you add a command-line option to ecpg. Did I get that right? If so we would deliberately make ecpglib work incorrectly and remove performance. Why is that? I'm interested in what others think, but to me that sounds like a show-stopper. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org Jabber: michael.meskes at googlemail dot com VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL -- 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] Improving our clauseless-join heuristics
I want to clarify small doubt in this regard. In function make_rels_by_clause_joins(..), it tries to join the given relation old_rel with other relations if there exist a join between them. What I can understand is, it is because if there exists a join condition its better to join with that relation. However if the given relation old_rel is not able to join any relation, then why can't it try to make cross-join with other relations there itself. -Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Tom Lane Sent: Friday, April 13, 2012 8:02 PM To: pgsql-hackers@postgreSQL.org Subject: [HACKERS] Improving our clauseless-join heuristics I looked into the behavior complained of here: http://archives.postgresql.org/pgsql-performance/2012-04/msg00093.php The problem query can be abstracted to select * from a, b, c, d where a.x = b.y and (a.z = c.c or a.z = d.d) Table a is much larger than the others (in fact, in the given example c and d are known to be single rows), and there are indexes on the mentioned columns of a. In this situation, the best plan is to cross-join c and d, then use a BitmapOr indexscan to pick out the rows of a that satisfy the OR condition, and finally join that small number of rows to b. The planner will use a cross-join-first plan if we omit b and the first WHERE clause from the query; but in the query as given, it fails to discover that plan and falls back on a vastly inferior plan that involves forming the a/b join first. The reason for this behavior is the anti-clauseless-join heuristics in join_search_one_level(). Without b, there are no join clauses available at join level 2, so the planner is forced to form all three 2-way cross joins; and then at level 3 it finds out that joining a to c/d works well. With b, we find the a/b join has a usable join clause so we form that join, and then we decide not to make any 2-way clauseless joins. So the c/d join is never constructed and there is no way to exploit the desirable indexscan at higher levels. After some reflection I think that the blame should be pinned on have_relevant_joinclause(), which is essentially defined as is there any join clause that can be evaluated at the join of these two relations?. I think it would work better to define it as is there any join clause that both these relations participate in?. In the majority of real-world queries, join clauses relate exactly two relations, so that these two definitions are equivalent. However, when we do have join clauses involving 3 or more relations, such as the OR clause in this example, it's evidently useful to consider cross-product joins of the smaller relations so that the join clause can be applied during the scan of the largest table. It would probably not be a good idea to back-patch such a change, since it might have consequences I can't foresee at the moment. But I'm strongly tempted to squeeze it into 9.2. Thoughts? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Improving our clauseless-join heuristics
Amit Kapila amit.kap...@huawei.com writes: I want to clarify small doubt in this regard. In function make_rels_by_clause_joins(..), it tries to join the given relation old_rel with other relations if there exist a join between them. What I can understand is, it is because if there exists a join condition its better to join with that relation. However if the given relation old_rel is not able to join any relation, then why can't it try to make cross-join with other relations there itself. Hm? That case is handled by make_rels_by_clauseless_joins. I suppose we could refactor to combine those two functions, but what's the point? If that's not what your question is about, then I don't understand. regards, tom lane -- 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] Improving our clauseless-join heuristics
That case is handled by make_rels_by_clauseless_joins It will be handled by make_rels_by_clauseless_joins() if given rel old_rel doesn't have any join clause. However if it has join clause but doesn't able to join with any other rels like in the example you have provided for relation c, it is not able to join with other rel d. In such cases it can do cross-join with d, because it has not found any relation to join with. Doesn't it will address the problem you mentioned? -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Monday, April 16, 2012 9:10 AM To: Amit Kapila Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Improving our clauseless-join heuristics Amit Kapila amit.kap...@huawei.com writes: I want to clarify small doubt in this regard. In function make_rels_by_clause_joins(..), it tries to join the given relation old_rel with other relations if there exist a join between them. What I can understand is, it is because if there exists a join condition its better to join with that relation. However if the given relation old_rel is not able to join any relation, then why can't it try to make cross-join with other relations there itself. Hm? That case is handled by make_rels_by_clauseless_joins. I suppose we could refactor to combine those two functions, but what's the point? If that's not what your question is about, then I don't understand. regards, tom lane -- 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] ECPG FETCH readahead
2012-04-16 04:46 keltezéssel, Michael Meskes írta: On Tue, Apr 10, 2012 at 07:56:35PM +0200, Boszormenyi Zoltan wrote: With the above, it would be possible to use a comma separated list of -r suboptions, e.g. -r prepare,questionmarks,readahead=16 in one option. Yes, that sounds like a good plan. But of course it's outside the scope of this patch, so we can add this later on. - Also added a note to the documentation about a possible performance trap if a previously written ECPG application uses its own custom readahead via multi-row FETCH statements. I didn't know that before you send this patch. Noah, did you? Frankly, I don't like this at all. If I got it right that means a FETCH N is essantially computed as N times FETCH 1 unless you either add a non-standard option to the DECLARE statement or you add a command-line option to ecpg. Did I get that right? Yes, just like when the readahead window set to 256, FETCH 1024 will iterate through 4 windows or FETCH 64 iterates through the same window 4 times. This is the idea behind the readahead window. If so we would deliberately make ecpglib work incorrectly and remove performance. Why is that? I'm interested in what others think, but to me that sounds like a show-stopper. How about allowing the readahead window to be resized for the non-decorated case if the runtime encounters FETCH N and N is greater than the previous window? Michael -- -- Zoltán Böszörményi Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/ -- 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] Improving our clauseless-join heuristics
Amit Kapila amit.kap...@huawei.com writes: That case is handled by make_rels_by_clauseless_joins It will be handled by make_rels_by_clauseless_joins() if given rel old_rel doesn't have any join clause. However if it has join clause but doesn't able to join with any other rels like in the example you have provided for relation c, it is not able to join with other rel d. In such cases it can do cross-join with d, because it has not found any relation to join with. Doesn't it will address the problem you mentioned? Sounds to me like that's going in the wrong direction, ie, joining to exactly the wrong relations. If you have to cross-join it's better to cross-join against relations that are included in one of the join clauses that does mention the current relation. Another way to look at this is that if we have select ... from a,b,c,d where a.x = b.y + c.z we want to consider a cross-join of b and c, in the hopes that we can do something useful with the join clause at the next level where it can join to a. From b's perspective there is no percentage in joining to d. On the other hand, when we come to consider d, it will have no join clauses so we will consider joining it to each other rel in turn. So if there is any value in joining d early, we will find that out. Or at least that's the theory. Now that I look at it this way, I think there is a bug here: when we are at level 2, we only consider later rels in the list for forced clauseless joins. That would be okay if the condition were symmetrical, but it isn't. This makes for a bogus FROM-list ordering dependency in handling of clauseless joins. Not sure how much that matters in the real world, but it still seems wrong. regards, tom lane -- 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] typo fix
Thanks! Best regards, Etsuro Fujita -Original Message- From: Peter Eisentraut [mailto:pete...@gmx.net] Sent: Saturday, April 14, 2012 7:07 AM To: Etsuro Fujita Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] typo fix On fre, 2012-04-13 at 17:27 +0900, Etsuro Fujita wrote: This is a little patch to fix a typo in file-fdw.sgml Fixed. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 9.3 Pre-proposal: Range Merge Join
I hope this is not an inappropriate time for 9.3 discussions. The flip side of asking for submissions in the first couple commitfests means that I need to submit proposals now. What is a Range Join? See attached SQL for example. The key difference is that the join condition is not equality, but overlaps (). Problem statement: slow. Nested loops are the only option, although they can benefit from an inner GiST index if available. But if the join is happening up in the plan tree somewhere, then it's impossible for any index to be available. Proposed solution: a modified merge join that can handle ranges. 1. Order the ranges on both sides by the lower bound, then upper bound. Empty ranges can be excluded entirely. 2. Left := first range on left, Right := first range on right 3. If Left or Right is empty, terminate. 4. If lower(Left) upper(Right), discard Right, goto 2 5. If lower(Right) upper(Left), discard Left, goto 2 6. return (Left, Right) as joined tuple 7. Right := next range on right 8. goto 3 If we get step 4 or step 5 keeps getting triggered, and a btree index is available (ordered by lower bound), we can re-probe to go to the correct position, and consider that the new top range on that side. This is an optimization for the case where there are large numbers of ranges with no match on the other side. Thanks to Nathan Boley for helping me devise this algorithm. However, any bugs are mine alone ;) Weaknesses: I haven't thought through the optimization, but I suspect it will be hard to be very accurate in the costing. That might be OK, because there aren't very many options anyway, but I'll need to think it through. Questions: * Is this idea sane? -- that is, are ranges important enough that people are willing to maintain a new operator? * The more general problem might be spatial joins which can operate in N dimensions, and I doubt this would work very well in that case. Does someone know of a spatial join algorithm (without IP claims) that would be as good as this one for ranges? * Other thoughts? Regards, Jeff Davis -- rate is billing rate in dollars per hour CREATE OR REPLACE FUNCTION bill(rate NUMERIC, during TSTZRANGE) RETURNS NUMERIC LANGUAGE plpgsql AS $$ DECLARE usage_s NUMERIC; BEGIN IF isempty(during) THEN usage_s := 0; ELSE usage_s := extract(epoch from (upper(during) - lower(during))); END IF; RETURN rate * (usage_s/3600.0); END; $$; CREATE TABLE billing_rate(rate NUMERIC, during TSTZRANGE); CREATE TABLE billing_usage(customer_id INT8, during TSTZRANGE); INSERT INTO billing_rate VALUES (12.50, '[2010-01-01 14:00, 2010-01-01 15:00)'); INSERT INTO billing_rate VALUES (14.50, '[2010-01-01 15:00, 2010-01-01 15:45)'); INSERT INTO billing_usage VALUES (123, '[2010-01-01 14:30, 2010-01-01 15:30)'); INSERT INTO billing_usage VALUES (234, '[2010-01-01 14:15, 2010-01-01 15:45)'); SELECT customer_id, bill(rate, range_intersect(u.during, r.during)) AS bill FROM billing_rate r, billing_usage u WHERE r.during u.during; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
