Re: [HACKERS] Split-up ECPG patches
Tom Lane írta: > I wrote: > >> The fundamental reason that there's a problem here is that ecpg has >> decided to accept a syntax that the backend doesn't (ie, FETCH with a >> fetch direction but no FROM/IN). I think that that's basically a bad >> idea: it's not helpful to users to be inconsistent, and it requires ugly >> hacks in ecpg, and now ugly hacks in the core grammar as well. We >> should resolve it either by taking out that syntax from ecpg, or by >> making the backend accept it too. Not by uglifying the grammars some >> more in order to keep them inconsistent. >> > > On looking a bit closer at this: I think the reason the core grammar > requires FROM/IN after fetch_direction is to leave the door open for > someday generalizing the fetch count to be an expression, not just an > integer constant. If we made FROM/IN optional, then doing that would > require some ugly syntax hack or other, such as requiring parentheses > around nontrivial expressions. So I'd like to see an actual case made > that there's a strong reason for not requiring FROM/IN in ecpg. > > regards, tom lane > The only reason is I think was the Informix-compatible mode. I don't know if it's strong enough, though. Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH 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] Split-up ECPG patches
Tom Lane írta: > Boszormenyi Zoltan writes: > >> Tom Lane írta: >> >>> I'd look at requiring from_in as being the least-bad alternative. >>> > > >> Hm. "FETCH FORWARD variable" can only be a rowcount var >> only if there's something afterwards, no? With the proposed >> change in fetch_direction (moving FORWARD and BACKWARD >> without the rowcount upper to the parent rules) now the parser is >> able to look behind "FORWARD variable"... >> > > The fundamental reason that there's a problem here is that ecpg has > decided to accept a syntax that the backend doesn't (ie, FETCH with a > fetch direction but no FROM/IN). I think that that's basically a bad > idea: it's not helpful to users to be inconsistent, and it requires ugly > hacks in ecpg, and now ugly hacks in the core grammar as well. We > should resolve it either by taking out that syntax from ecpg, or by > making the backend accept it too. Not by uglifying the grammars some > more in order to keep them inconsistent. > > If we were going to allow it in the core, I think moving the cursor > name into the fetch_direction production might work, ie, change > fetch_direction to fetch_args and make it cover everything that > FETCH and MOVE share. Probably from_in could become opt_from_in, > since the alternatives for it are fully reserved words already, and we > wouldn't need to double up any of the fetch_direction productions. > And maybe, possibly with this change as a start, someday we can support dynamic cursorname in plain SQL, too. DECLARE $1 CURSOR FOR SELECT ... It would be a much cleaner solution in ECPG, too. Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH 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] Fixing geometic calculation
marcin mank wrote: > You are correct, I think, though this does not solve the division problem: > As a first goal I'm just attempting to reduce the EPSILON from 1.0E-6 down to 1.0E-015 (give or take). The current regression test suite works fine down to 1.0E-09. At 1.0E-10 errors appear, not in the geometry sections, but in the select_view test of all things. This is most likely due to postgresql now giving the more correct (hence different) answers. A real test suite is needed for this. Setting up PostGIS + MySQL + OtherCommerical for comparison purposes. The other problem is many of the basic geometric operators in postgres, such a left of, above, etc, are so incorrectly implemented, they are not even wrong. -- 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] Alpha releases: How to tag
On Fri, Aug 07, 2009 at 06:28:34PM -0400, Tom Lane wrote: > David Fetter writes: > > I am not suggesting that this change be immediate, and it's not ivory > > tower. It's just how everybody else does it. > > You keep saying that, and it's completely meaningless. What do you know > about the development practices of Oracle, or DB2, or even Mysql? When I was at Sybase, changes to the on disk structure were required to provide code to do the migration. Nonetheless, at release time, the migrate process was almost always discovered to be broken, sometimes even before it was shipped to customers. Of course, Sybase implemented its own complete filesystem layer on top of raw partitions, so there was more scope to go wrong, especially since it was possible to corrupt the on disk structure in subtle ways that would not be discovered in normal operation but that would cause migration to corrupt it still further. In fairness, this is a very difficult problem to solve well and I expect to rely on dump/load migrations for quite sometime. -dg -- David Gould da...@sonic.net 510 536 1443510 282 0869 If simplicity worked, the world would be overrun with insects. -- 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] Patch for 8.5, transformationHook
Jeff Davis escribió: > On Mon, 2009-04-20 at 18:53 +0200, Pavel Stehule wrote: > > b) it allows constructors for data types (ANSI SQL) > > > > datatype(typefield1[, typefiedl2[, typefiedl3[, ...]]]) returns type > > Can you describe this case in more detail? What section of SQL are you > referring to? Hmm, I see them in 4.7 "user-defined types". However what's in SQL2003 and the 2008 draft I have is: 3.1.6.6 constructor function: A niladic SQL-invoked function of which exactly one is implicitly specified for every structured type. An invocation of the constructor function for data type T returns a value V of the most specific type T such that V is not null and, for every observer function O defined for T, the invocation O(V) returns the default value of the attribute corresponding to O. and later: 4.7.4 Constructors Associated with each structured type ST is one implicitly defined constructor function, if and only if ST is instantiable. Let TN be the name of a structured type T. The signature of the constructor function for T is TN() and its result data type is T. The invocation TN() returns a value V such that V is not null and, for every attribute A of T, A(V) returns the default value of A. The most specific type of V is T. For every structured type ST that is instantiable, zero or more SQL-invoked constructor methods can be specified. The names of those methods shall be equivalent to the name of the type for which they are specified. So I'm not seeing those typefields anywhere. -- Alvaro Herrerahttp://www.CommandPrompt.com/ 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] join removal
On Sun, Jul 19, 2009 at 10:56 PM, Tom Lane wrote: > I think we want something along the lines of relation_is_distinct_for > with a list of columns and a list of comparison operators, where the > first-cut implementation will be to look for matching indexes. > This will be different from query_is_distinct_for, but it's dealing > with the same sorts of considerations about whether the operator > semantics are the right things. I took at a first crack at coding up an implementation of relation_is_distinct_for() tonight. Pseudocode: for each indexoptinfo { if (not unique or not predOK or contains expressions) skip it; for c = 0 .. ind->ncolumns { opid = distinct_col_search(ind->indexkeys[c], colnos, opids); if (!OidIsValid(opid) || !equality_ops_are_compatible(opid, )) break; } if (found them all) return true; } return false; distinct_col_search() is going to return the relevant equality operator from the argument list, which is ultimately going to come from the RestrictInfo for the join clause. So I need to see whether that's compatible with the index, but equality_ops_are_compatible() wants two equality operators, and what I have is one equality operator and one operator class. Maybe it's sufficient to just check whether op_in_opfamily(opid, ind->opfamily[c]), and skip equality_ops_are_compatible()? I am having a hard time wrapping my brain around what it means to have multiple, incompatible notions of equality... any help appreciated! ...Robert -- 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] Fixing geometic calculation
On Sat, Aug 8, 2009 at 3:07 AM, Paul Matthews wrote: > IEEE754 does not allow two number X and Y, such that X!=Y and (X-Y)==0. > And since IEEE754 has been around since the 70's or 80's I think we can > start relying on its existence and behavior by now. > You are correct, I think, though this does not solve the division problem: $ cat t.c #include int main(){ double a=1.01e-307, b=1e-307, c=a-b; printf("a=%le, b=%le, c=%le, c==0:%d, a==b:%d 1/c=%le\n",a,b,c,c==0,a==b,1.0/c); return 0; } $ gcc -Wall -O2 t.c $ ./a.out a=1.01e-307, b=1.00e-307, c=1.00e-313, c==0:0, a==b:0 1/c=inf Greetings Marcin Mańk -- 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] Patch for 8.5, transformationHook
On Sun, 2009-07-26 at 15:29 +0200, Pavel Stehule wrote: > Hello > > new patch add new contrib "transformations" with three modules > anotation, decode and json. > > These modules are ported from my older work. > > Before applying this patch, please use named-fixed patch too. The hook > doesn't need it, but modules anotation and json depend on it. This is not a complete review of the patches, but I have read through the discussion and taken a brief look at the code from a use-case point of view (not a technical review). My general feeling for the use case of the patch is positive. Pavel showed a reasonable variety of valid use cases, and the possibility to make existing special cases (like XML) no longer special cases. However, there are causes for concern: 1. Robert Haas is concerned that the kind of transformations allowed might be too limited: http://archives.postgresql.org/pgsql-hackers/2009-07/msg01947.php 2. Tom Lane is concerned about multiple hooks working together: http://archives.postgresql.org/pgsql-hackers/2009-04/msg01038.php 3. All throughout the thread, there is a general concern that this might not be exactly the right solution. I think we need to wait on this patch. Waiting will hopefully provide better answers to the following questions: * What other similar features exist in the SQL spec that require a similar special case now? If we added this hook, would those still require a special case? * Can anyone think of a better hook or API change that would answer these use cases? * Can anyone think of other features that almost fit this model, but that the hook won't quite work for? * If the hook can implement XML, should we refactor the XML support (and COALESCE, etc.) to use the hook for the sake of consistency? If the hook is not good enough for those features, that might indicate a problem. Considering that the next commitfest is only about a month away, I don't think that it is too much of a burden to wait. I didn't have time to do a complete review, so I can't provide much better direction than this right now. Regards, Jeff Davis -- 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] hot standby - merged up to CVS HEAD
On Sat, Aug 8, 2009 at 1:12 PM, Bruce Momjian wrote: > You are right you don't have to justify anything, but neither can you > claim ownership of the patch/feature and complain that others are > working on it too. This is a community project --- if you want your > patches to remain your property, I suggest you no longer post them to > our community lists. If you are actively working on patches, I assume > others will not duplicate your work, but if you are idle, others are > encouraged to keep improving the patch. Again, if you don't like that, > then perhaps the community-development process isn't for you. Simon, I think it would also be fair to point out that you keep saying that you're going to deliver this patch for 8.5, but you haven't provided any real timetable as to when you're going to start working on it or when it'll be completed. Because this patch IS so important to the community, people want to know the answers to those questions. That is exactly why you were asked about your schedule at PGcon; and you demurred. I understand that your #1 priority needs to be the work for which you get paid the most money, but I think it's unfair to ask other people to wait for you to work on something when you haven't committed to a timetable for working on it. It might be unfair to ask it even if you had committed to a timetable and that timetable was well out in the future, but it's certainly unfair when there is no timetable at all. The most recent discussion of the timing of this patch was that you opined it should go after Streaming Rep. Based on the review of Streaming Rep this CommitFest, I would say that there is an awful lot of work left to be done to make that patch committable. I think we will be lucky if it makes it into 8.5. Call me a pessimist but I think we'll be doing pretty well if it makes it into 8.6. I think the chances that we are going to get streaming rep committed and still have enough CommitFests left to get Hot Standby committed too are just about zero, so waiting for Streaming Rep to be committed first does not seem like a very realistic plan to me. Note that Streaming Rep got moved to returned with feedback on *the first day* of this CommitFest; that's how much work it took to see that it was not committable. Note also that the resistance to committing large patches is going to grow and grow as we get closer to the end of this development cycle. I am very much afraid that if we don't have a version of Hot Standby that is reviewable for the next CommitFest we are going to be out of luck for 8.5. I do not think that I have the juice to make Hot Standby happen. It's possible that I don't know my own strength, but I'm not prepared to bet on it. At least, it looks like I do have the juice to dust of the bitrot, and maybe fix some of the more superficial problems with it. I would like to think that is something helpful. ...Robert -- 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] Patch for 8.5, transformationHook
On Sat, Aug 8, 2009 at 9:11 PM, Jeff Davis wrote: > On Thu, 2009-07-30 at 00:01 -0400, Robert Haas wrote: >> The JSON transformation provides functionality which is very similar >> to what we also offer for XML. I sort of think we ought to just >> provide that, rather than making it an add-on. I have found it to be >> a tremendously attractive alternative to XML. > > It's worthwhile to think about how we can fit our special cases into > general APIs -- particularly when we have two similar special cases like > JSON and XML. I agree. The way we handle XML is with special syntax that is hard-coded into the parser. If there is a more flexible solution I'm all for it, but I'm not sure this is it. ...Robert -- 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] Commitfest 2009-07 - 6 patches moved to "Returned with Feedback"
On Sat, Aug 8, 2009 at 12:33 PM, Tom Lane wrote: > Robert Haas writes: >> As we are now into the last week of this CommitFest (hopefully), I >> have moved all of the patches that were listed as "Waiting on Author" >> to "Returned with Feedback". I feel pretty good about doing this >> because most of these patches have been waiting on author for a long >> time, or they were reviewed multiple times and got a lot of good >> feedback, or both. Hopefully we will see many of these patches again >> in the next CommitFest: > >> GRANT ON ALL IN schema >> DefaultACLs >> Indexam API changes >> Index-only quals >> Support for in to_char() >> Determine client_encoding from client locale > > The only one of these that seemed to me to be close to the finish line > was the to_char patch. If Brendan can re-submit that in the next day or > two, I think we should still consider it for this fest. Well, in an ideal world, we would be able to commit patches whenever they come in, so if you have the bandwidth to get this one in, I think that's great. But, I also think it's important for us to try to close out the CommitFest in the next week so that we can all get back to working on our own patches for a bit. Although some of the patches on the wiki are marked "Needs Review" and others are marked "Ready for Committer", in fact nearly all of them need some committer attention at this point. I think the ones that could most use your attention are: - Named and mixed notation for PL (understand you are already working on this) - machine-readable explain output (not sure whether this is committable, but it may be close enough for you to whack it into shape fairly quickly: at least I'd like to get some feedback) - Parser's hook based on FuncCall (Jeff Davis is also still reviewing this one, I believe; it's unclear whether we want to accept this) ...Robert -- 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] Patch for 8.5, transformationHook
On Mon, 2009-04-20 at 18:53 +0200, Pavel Stehule wrote: > b) it allows constructors for data types (ANSI SQL) > > datatype(typefield1[, typefiedl2[, typefiedl3[, ...]]]) returns type Can you describe this case in more detail? What section of SQL are you referring to? Regards, Jeff Davis -- 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] Patch for 8.5, transformationHook
On Thu, 2009-07-30 at 00:01 -0400, Robert Haas wrote: > The JSON transformation provides functionality which is very similar > to what we also offer for XML. I sort of think we ought to just > provide that, rather than making it an add-on. I have found it to be > a tremendously attractive alternative to XML. It's worthwhile to think about how we can fit our special cases into general APIs -- particularly when we have two similar special cases like JSON and XML. Regards, Jeff Davis -- 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] Docbook toolchain interfering with patch review?
Robert Haas wrote: > Yeah. I usually build the docs and read them if I'm making er > proposing... an extensive change, but for simple stuff I just edit the > SGML and figure that if it looks sane it probably is. > > I certainly don't test the doc portions of patches I review unless I > see something sketchy in the markup. > > But I can't say I've ever had much trouble building the docs. I find > it a bit odd that "make" in the doc directory does nothing; and "make" > in doc/src does nothing, but "make" in doc/src/sgml does what you > expect. I also find the slowness of openjade to be pretty annoying. > But those are minor warts, not serious inconveniences that hinder > reviewing. FYI, bulding PDFs used to take _days_; we finally found and worked around that openjade bug. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] a short trip in the wayback machine
While following up a comment from Tom on my blog, I discovered that some 9 1/2 years ago in a patch bearing the comment: Fixed psql double quoting of SQL ids Fixed libpq printing functions the documentation of psql's --no-readline option was removed (psql-ref.sgml v 1.23). I think this was a mistake and it should be restored :-) I'm quite never sure how far back to take pure docs patches, though. Should I just fix HEAD, or HEAD plus 8.4, or all the way back to 7.4? 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] revised hstore patch
Tom Lane wrote: > Perhaps an appropriate thing to do is separate out the representation > change from the other new features, and apply just the latter for now. > Or maybe we should think about having two versions of hstore. This > is all tied up in the problem of having a decent module infrastructure > (which I hope somebody is working on for 8.5). I don't know where > we're going to end up for 8.5, but I'm disinclined to let a fairly > minor contrib feature improvement break upgrade-compatibility before > we've even really started the cycle. I can just have pg_migrator detect hstore and require it be removed before upgrading; we did that already for 8.3 to 8.4 and I am assuming we will continue to have cases there pg_migrator just will not work. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Lock Wait Statistics (next commitfest)
Mark Kirkwood wrote: Mark Kirkwood wrote: Jaime Casanova wrote: On Fri, Jul 17, 2009 at 3:38 AM, Mark Kirkwood wrote: With respect to the sum of wait times being not very granular, yes - quite true. I was thinking it is useful to be able to answer the question 'where is my wait time being spent' - but it hides cases like the one you mention. What would you like to see? would max and min wait times be a useful addition, or are you thinking along different lines? track number of locks, sum of wait times, max(wait time). but actually i started to think that the best is just make use of log_lock_waits send the logs to csvlog and analyze there... Right - I'll look at adding max (at least) early next week. Patch with max(wait time). Still TODO - amalgamate individual transaction lock waits - redo (rather ugly) temporary pg_stat_lock_waits in a form more like pg_locks This version has the individual transaction lock waits amalgamated. Still TODO: redo pg_stat_lock_waits ... lockstats-4.patch.gz Description: GNU Zip compressed data -- 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] Split-up ECPG patches
I wrote: > The fundamental reason that there's a problem here is that ecpg has > decided to accept a syntax that the backend doesn't (ie, FETCH with a > fetch direction but no FROM/IN). I think that that's basically a bad > idea: it's not helpful to users to be inconsistent, and it requires ugly > hacks in ecpg, and now ugly hacks in the core grammar as well. We > should resolve it either by taking out that syntax from ecpg, or by > making the backend accept it too. Not by uglifying the grammars some > more in order to keep them inconsistent. On looking a bit closer at this: I think the reason the core grammar requires FROM/IN after fetch_direction is to leave the door open for someday generalizing the fetch count to be an expression, not just an integer constant. If we made FROM/IN optional, then doing that would require some ugly syntax hack or other, such as requiring parentheses around nontrivial expressions. So I'd like to see an actual case made that there's a strong reason for not requiring FROM/IN in ecpg. 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] Split-up ECPG patches
Boszormenyi Zoltan writes: > Tom Lane írta: >> I'd look at requiring from_in as being the least-bad alternative. > Hm. "FETCH FORWARD variable" can only be a rowcount var > only if there's something afterwards, no? With the proposed > change in fetch_direction (moving FORWARD and BACKWARD > without the rowcount upper to the parent rules) now the parser is > able to look behind "FORWARD variable"... The fundamental reason that there's a problem here is that ecpg has decided to accept a syntax that the backend doesn't (ie, FETCH with a fetch direction but no FROM/IN). I think that that's basically a bad idea: it's not helpful to users to be inconsistent, and it requires ugly hacks in ecpg, and now ugly hacks in the core grammar as well. We should resolve it either by taking out that syntax from ecpg, or by making the backend accept it too. Not by uglifying the grammars some more in order to keep them inconsistent. If we were going to allow it in the core, I think moving the cursor name into the fetch_direction production might work, ie, change fetch_direction to fetch_args and make it cover everything that FETCH and MOVE share. Probably from_in could become opt_from_in, since the alternatives for it are fully reserved words already, and we wouldn't need to double up any of the fetch_direction productions. 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] GRANT ON ALL IN schema
> Well, since I've written the patch I am for it :) Probably with that > GRANT ON * and GRANT ON schema.* as it has indeed very low probability > that something like that will be in standard with different meaning and > also it's mysql compatible (which is the only db currently having this > feature I think), even if that's very little plus. I disagree here. While it's nice to be MySQL-compatible, a glob "*" is not at all consistent with other SQL syntax, whereas "ALL" and "GRANT ON ALL IN SCHEMA " are. The answer as far as the standard is concerned is, why not make an effort to get this into the standard? >> And how do we want to filter default acls ? > My opinion is that the best way to do this would be ALTER DEFAULT > PRIVILEGES GRANT ..., without any additional filters, it would just > affect the role which runs this command. I think this is best solution > because ALTER SCHEMA forces creation of many schemas that might not have > anything to do with structure of the database (if you want different > default privileges for different things). Also having default privileges > per role with filters on various things will IMHO create more confusion > than good. And finally if somebody wants to have different default > privileges for different things than he can just create child roles with > different default privileges and use SET SESSION AUTHORIZATION to switch > between them. I'm not sure if I'm agreeing or disagreeing with you here, but I'll say that it doesn't help a user have a consistent setup for assigning privileges. GRANT ON ALL working per *schema* while ALTER DEFAULT working per *role* will just create confusion and not improve the managability of privileges in PostgreSQL. We need a DEFAULT and a GRANT ALL statement which can be executed on the same scope so that users can easily set up a coherent access control scheme. For my part, I *do* use schema to control my security context for database objects; I find that it's a convenience to be able to take objects which a role has no permissions on out of its visibility (through search_path) as well. And schema-based security mentally maps to directory-based permissions, which unix sysadmins instinctively understand. So I think that a form of GRANT ALL/DEFAULT which supported schema-scoping would be useful to a *lot* more people than one which didn't. I do understand that other scopes (such as scoping by object owner) are equally valid and maybe more consistent with the SQL permissions model. However, I think that role-scoping is not as intuitively understandible to most users and would be, for that reason, less used and less useful. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.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] contrib/pg_freespacemap
Josh Berkus writes: > Given that the FSM is now auto-managing, is there any reason to have > this tool at all? Maybe not, but I'd be inclined to wait a release or so until we have more field experience with the new FSM. If, in a year, FSM is something nobody worries about anymore, we can kill the contrib module. 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] contrib/pg_freespacemap
On 8/8/09 10:50 AM, Alvaro Herrera wrote: > Tom Lane wrote: >> Alvaro Herrera writes: >>> Is there any reason we didn't move the pg_freespace function from >>> contrib to core? >> Is there a reason we *should* move it? The current definition doesn't >> leave me feeling that it's more than a low-level hacker's tool. > > No specific reason. I was just wondering because I saw an old message > about it. Maybe we just don't need it. Given that the FSM is now auto-managing, is there any reason to have this tool at all? Seems like it should get killed off. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.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] [PATCH] 2PC state files on shared memory
Heikki Linnakangas writes: > Tom Lane wrote: >> What if PREPARE simply didn't write the 2PC file at all, except into WAL? > Interesting idea, might be worth performance testing. Peeking into the > WAL files during normal operation feels naughty, but it should work. > However, if the bottleneck is the WAL fsyncs, I doubt it's any faster > than Michael's current patch. This isn't about faster, it's about not requiring users to estimate a suitable size for a shared-memory arena. > Actually, it would be interesting to performance test a stripped down > broken implementation that doesn't write the state files anywhere but > WAL, PREPARE releases all locks like regular COMMIT does, and COMMIT > PREPARED just writes the commit record and fsyncs. That would give an > upper bound on how much gain any of these patches can have. If that's > not much, we can throw in the towel. Good idea --- although I would think that the performance of 2PC would be pretty context-dependent anyway. What load would you test under? 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] [PATCH] 2PC state files on shared memory
Tom Lane wrote: > What if PREPARE simply didn't write the 2PC file at all, except into WAL? > Then, make CheckPointTwoPhase write the 2PC file for any still-live > GXACT, by means of reaching into the WAL and pulling the data out. > All it would need for that is the LSN of the WAL record, which I think > the GXACT has already. (It might have the end location rather than > the start, but in any case we could store both.) Similarly, COMMIT > PREPARED could be taught to pull the data from WAL instead of a 2PC > file, in the typical case where the file didn't exist yet. I think > there might be some synchronization issues against checkpoints --- you > couldn't recycle WAL until you were sure there was no COMMIT PREPARED > pulling from it. But it seems possibly workable, and there's no tuning > knob needed. Interesting idea, might be worth performance testing. Peeking into the WAL files during normal operation feels naughty, but it should work. However, if the bottleneck is the WAL fsyncs, I doubt it's any faster than Michael's current patch. Actually, it would be interesting to performance test a stripped down broken implementation that doesn't write the state files anywhere but WAL, PREPARE releases all locks like regular COMMIT does, and COMMIT PREPARED just writes the commit record and fsyncs. That would give an upper bound on how much gain any of these patches can have. If that's not much, we can throw in the towel. -- 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] Split-up ECPG patches
Tom Lane írta: > Boszormenyi Zoltan writes: > >> Michael Meskes Ărta: >> >>> The problem is that SignedIconst might be a char variable, >>> too. So how shall the parser know whether str in "FETCH BACKWARD :str" >>> carries >>> the number of records to move backwards ot the cursor name. >>> > > >> This was the problem, yes. >> > > >>> A possible solution >>> would be to force a numeric variable for numeric data. >>> > > >> By which you would remove a feature. >> > > If you ask me, the real problem here is the productions ecpg adds to > make "from_in" optional. If a CVARIABLE can be either a fetch_count > or a cursor_name, then removing from_in makes the grammar fundamentally > ambiguous; no amount of rearrangement will fix that. > > I'd look at requiring from_in as being the least-bad alternative. What > I now see is that Zoltan's previous patch is removing a different subset > of the possible parses (and has to modify the core grammar in order to > be able to do that); to wit, it's arbitrarily deciding that "FETCH > FORWARD variable" must be a cursor name variable and not a row count > variable. That strikes me as a non-orthogonal, error-prone kluge. > Hm. "FETCH FORWARD variable" can only be a rowcount var only if there's something afterwards, no? With the proposed change in fetch_direction (moving FORWARD and BACKWARD without the rowcount upper to the parent rules) now the parser is able to look behind "FORWARD variable"... Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH 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] Split-up ECPG patches
Boszormenyi Zoltan writes: > Michael Meskes Ãrta: >> The problem is that SignedIconst might be a char variable, >> too. So how shall the parser know whether str in "FETCH BACKWARD :str" >> carries >> the number of records to move backwards ot the cursor name. > This was the problem, yes. >> A possible solution >> would be to force a numeric variable for numeric data. > By which you would remove a feature. If you ask me, the real problem here is the productions ecpg adds to make "from_in" optional. If a CVARIABLE can be either a fetch_count or a cursor_name, then removing from_in makes the grammar fundamentally ambiguous; no amount of rearrangement will fix that. I'd look at requiring from_in as being the least-bad alternative. What I now see is that Zoltan's previous patch is removing a different subset of the possible parses (and has to modify the core grammar in order to be able to do that); to wit, it's arbitrarily deciding that "FETCH FORWARD variable" must be a cursor name variable and not a row count variable. That strikes me as a non-orthogonal, error-prone kluge. 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] Split-up ECPG patches
Michael Meskes írta: > On Sat, Aug 08, 2009 at 07:21:59PM +0200, Boszormenyi Zoltan wrote: > >>> A possible solution >>> would be to force a numeric variable for numeric data. >>> >> By which you would remove a feature. >> With the proposed core grammar change, >> the feature where you can pass the number of >> records to be fetched in a string variable >> can be kept. >> > > Somehow I doubt this. Yes, your patch originally didn't come with a > shift/reduce problem, but I cannot see how this solved this. The same rule > still has the same problem. > Err, no. E.g. if the whole statement is FETCH BACKWARD cursor_name then it can only carry a cursor name, as always did. No matter if cursor_name is now a static or dynamic name. The problem is with the original factorization of "fetch_direction", now with dynamic cursor name the grammar cannot decide between "FETCH BACKWARD :no_of_rec ..." and "FETCH BACKWARD :cursor" Same with the FORWARD rule. And _that_ can be solved by decreasing factorization, i.e. pulling FORWARD and BACKWARD up into the FetchStmt rule in the core grammar. >> It seems to be Informix-specific, I just looked it up in >> their guide_to_sql_syntax.pdf. >> > > The questin is, does Oracle so somthing similar? > No idea. I can look up though. >>> I'm not >>> sure if any other dbms still allows this construct, so we might we well >>> remove >>> it for 8.5. Or move it to a special compatibility mode. >>> >>> >> How would you do that? With a completely >> different parser for Informix-compatibility? >> > > No. > > >> It would reduce maintainability. Or does bison >> allow conditionally enabled rules somehow? >> It sure would come in handy in this case. >> > > No. You have to code around it. What I meant was, that other dbms should have > the same problem. So they solved it one way or the other. And we could create > both solutions just depending on the mode we're in. Informix e.g. doesn't seem > to allow a variable to carry the number of records. Heck, I don't even see > FORWARD/BACKWARD. A number is only given in ABSOLUTE and RELATIVE but no > variable. > > Michael > -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH 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] contrib/pg_freespacemap
Tom Lane wrote: > Alvaro Herrera writes: > > Is there any reason we didn't move the pg_freespace function from > > contrib to core? > > Is there a reason we *should* move it? The current definition doesn't > leave me feeling that it's more than a low-level hacker's tool. No specific reason. I was just wondering because I saw an old message about it. Maybe we just don't need it. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Split-up ECPG patches
On Sat, Aug 08, 2009 at 07:21:59PM +0200, Boszormenyi Zoltan wrote: > > A possible solution > > would be to force a numeric variable for numeric data. > > By which you would remove a feature. > With the proposed core grammar change, > the feature where you can pass the number of > records to be fetched in a string variable > can be kept. Somehow I doubt this. Yes, your patch originally didn't come with a shift/reduce problem, but I cannot see how this solved this. The same rule still has the same problem. > It seems to be Informix-specific, I just looked it up in > their guide_to_sql_syntax.pdf. The questin is, does Oracle so somthing similar? > > I'm not > > sure if any other dbms still allows this construct, so we might we well > > remove > > it for 8.5. Or move it to a special compatibility mode. > > > > How would you do that? With a completely > different parser for Informix-compatibility? No. > It would reduce maintainability. Or does bison > allow conditionally enabled rules somehow? > It sure would come in handy in this case. No. You have to code around it. What I meant was, that other dbms should have the same problem. So they solved it one way or the other. And we could create both solutions just depending on the mode we're in. Informix e.g. doesn't seem to allow a variable to carry the number of records. Heck, I don't even see FORWARD/BACKWARD. A number is only given in ABSOLUTE and RELATIVE but no variable. 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 ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: mes...@jabber.org Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use 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] Split-up ECPG patches
Michael Meskes írta: > On Sat, Aug 08, 2009 at 05:48:57PM +0200, Boszormenyi Zoltan wrote: > >> ... >> "/usr/bin/perl" ./parse.pl . < ../../../../src/backend/parser/gram.y > >> preproc.y >> /usr/bin/bison -d -o preproc.c preproc.y >> preproc.y: conflicts: 2 shift/reduce >> preproc.y: expected 0 shift/reduce conflicts >> make[4]: *** [preproc.c] Error 1 >> make[4]: Leaving directory >> `/home/zozo/Schönig-számlák/leoni/2/pgsql/src/interfaces/ecpg/preproc' >> ... >> > > Right, I missed this one. But it's ecpg specific and should not be fixed by > changing gram.y. Debatable. :-) > The problem is that SignedIconst might be a char variable, > too. So how shall the parser know whether str in "FETCH BACKWARD :str" carries > the number of records to move backwards ot the cursor name. This was the problem, yes. > A possible solution > would be to force a numeric variable for numeric data. By which you would remove a feature. With the proposed core grammar change, the feature where you can pass the number of records to be fetched in a string variable can be kept. > Also keep in mind that a > fetch statement without from/in is an addition on top of the standard. It seems to be Informix-specific, I just looked it up in their guide_to_sql_syntax.pdf. > I'm not > sure if any other dbms still allows this construct, so we might we well remove > it for 8.5. Or move it to a special compatibility mode. > How would you do that? With a completely different parser for Informix-compatibility? It would reduce maintainability. Or does bison allow conditionally enabled rules somehow? It sure would come in handy in this case. Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH 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] hot standby - merged up to CVS HEAD
Simon Riggs wrote: > > On Sat, 2009-08-08 at 00:02 -0400, Bruce Momjian wrote: > > > > Also, to my knowledge, nobody has really looked through the results to > > > see if they are any good, so the success of the endeavor remains in > > > doubt from my point of view. That's a bit of a shame because I am > > > interested in putting some more time into this, but I don't have the > > > knowledge or experience to "fly solo" here. > > > > Well, Simon stated that your version should now be used as the most > > recent one, so I would call that a success. > > I'm not sure why you're stirring this up again. > > Simon didn't state that the above. You can re-read my words and we can > debate their meaning, but that's just a waste of time. You stated: - It's going to be very confusing if people submit their own versions of - it. So now we have mine, Heikki's and Robert's. I'd like this to stop - please, have a little faith and a little patience. Presumably Robert's - rebasing patch is best place to start from now for later work. I assume your last sentence is saying exactly that Robert's version should be used as the most current reprsentation of this feature patch. > I shouldn't have to publicly justify why I haven't finished working on a > patch, when a) we have time, b) it's summer and c) I've already said I > would finish the patch, very very clearly in a big loud voice. I expect > to finish and commit comfortably in 2009, leaving many months before > next release. > > So, as I said before, I expect to be left in peace to finish my own > work. There wouldn't be anything to finish if it wasn't for me. I > specifically don't want to review other people's versions of work when > I'm trying to do my own, nor do I expect others to encourage multiple > authors on the same piece of work. The bottom line is that you think you have ownership of the patch and the feature --- you do not. You are right you don't have to justify anything, but neither can you claim ownership of the patch/feature and complain that others are working on it too. This is a community project --- if you want your patches to remain your property, I suggest you no longer post them to our community lists. If you are actively working on patches, I assume others will not duplicate your work, but if you are idle, others are encouraged to keep improving the patch. Again, if you don't like that, then perhaps the community-development process isn't for you. And your misunderstanding in this area is exactly why I am bringing this up. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Split-up ECPG patches
On Sat, Aug 08, 2009 at 05:48:57PM +0200, Boszormenyi Zoltan wrote: > ... > "/usr/bin/perl" ./parse.pl . < ../../../../src/backend/parser/gram.y > > preproc.y > /usr/bin/bison -d -o preproc.c preproc.y > preproc.y: conflicts: 2 shift/reduce > preproc.y: expected 0 shift/reduce conflicts > make[4]: *** [preproc.c] Error 1 > make[4]: Leaving directory > `/home/zozo/Schönig-számlák/leoni/2/pgsql/src/interfaces/ecpg/preproc' > ... Right, I missed this one. But it's ecpg specific and should not be fixed by changing gram.y. The problem is that SignedIconst might be a char variable, too. So how shall the parser know whether str in "FETCH BACKWARD :str" carries the number of records to move backwards ot the cursor name. A possible solution would be to force a numeric variable for numeric data. Also keep in mind that a fetch statement without from/in is an addition on top of the standard. I'm not sure if any other dbms still allows this construct, so we might we well remove it for 8.5. Or move it to a special compatibility mode. 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 ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: mes...@jabber.org Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use 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] Commitfest 2009-07 - 6 patches moved to "Returned with Feedback"
Robert Haas writes: > As we are now into the last week of this CommitFest (hopefully), I > have moved all of the patches that were listed as "Waiting on Author" > to "Returned with Feedback". I feel pretty good about doing this > because most of these patches have been waiting on author for a long > time, or they were reviewed multiple times and got a lot of good > feedback, or both. Hopefully we will see many of these patches again > in the next CommitFest: > GRANT ON ALL IN schema > DefaultACLs > Indexam API changes > Index-only quals > Support for in to_char() > Determine client_encoding from client locale The only one of these that seemed to me to be close to the finish line was the to_char patch. If Brendan can re-submit that in the next day or two, I think we should still consider it for this fest. 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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock
On Fri, 2009-08-07 at 15:58 -0400, Alvaro Herrera wrote: > Tom Lane wrote: > > Peter Eisentraut writes: > > > Is there a good reason for $subject, other than that the code is > > > entangled > > > with other ALTER TABLE code? > > > > I think it could be lower, but it would take nontrivial restructuring of > > the ALTER TABLE support. In particular, consider what happens when you > > have a list of subcommands that don't all require the same lock level. > > I think you'd need to scan the list and find the highest required lock > > level before starting ... > > IIRC there was a patch from Simon to address this issue, but it had some > holes which he didn't have time to close, so it sank. Maybe this can be > resurrected and fixed. I was intending to finish that patch in this release cycle. MERGE needs further work if you are looking for a project. It isn't immediately obvious but MERGE logic is a requirement for maintaining materialized views, which is why I was working on that. -- Simon Riggs 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] hot standby - merged up to CVS HEAD
On Sat, 2009-08-08 at 00:02 -0400, Bruce Momjian wrote: > > Also, to my knowledge, nobody has really looked through the results to > > see if they are any good, so the success of the endeavor remains in > > doubt from my point of view. That's a bit of a shame because I am > > interested in putting some more time into this, but I don't have the > > knowledge or experience to "fly solo" here. > > Well, Simon stated that your version should now be used as the most > recent one, so I would call that a success. I'm not sure why you're stirring this up again. Simon didn't state that the above. You can re-read my words and we can debate their meaning, but that's just a waste of time. I shouldn't have to publicly justify why I haven't finished working on a patch, when a) we have time, b) it's summer and c) I've already said I would finish the patch, very very clearly in a big loud voice. I expect to finish and commit comfortably in 2009, leaving many months before next release. So, as I said before, I expect to be left in peace to finish my own work. There wouldn't be anything to finish if it wasn't for me. I specifically don't want to review other people's versions of work when I'm trying to do my own, nor do I expect others to encourage multiple authors on the same piece of work. -- Simon Riggs 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] WIP: to_char, support for EEEE format
Brendan Jurd writes: > 2009/8/3 Tom Lane : >> Uh, no, we had better support more. The actual limit of the current >> numeric format is 1e+131072. > Given your comment above I'm thinking it reasonable to use an int32 to > store the exponent -- will that be safe? Seems reasonable to me. > That would allow for a maximum of 10 exponent digits. As an aside, I > note that int4out() hardcodes the maximum number of digits rather than > exposing a constant (c.f. MAXINT8LEN in int8.c). I'm considering > adding MAXINT2LEN and MAXINT4LEN to int.c in passing. Excessive > tinkering, or worthy improvement? Don't really care. short and int are the same sizes on all platforms of interest, and are likely to remain so --- if they don't, we'll have way more places to fix than this one. INT8 has historically been more platform-dependent. 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] Split-up ECPG patches
Tom Lane írta: > Michael Meskes writes: > >> Tom, AFAICT we only need one core grammar change, moving the cursor name to >> it's own rule that only resolves back to name. This rule should be eliminated >> by bison during the build process anyway, so I see no problem adding it. It >> does make the ecpg changes way smaller though. Is this okay with you? >> > > Sure, that one didn't bother me. It was the FORWARD/BACKWARD decomposition > that looked unnecessary (as your tests seem to bear out). > Of course, that one bothered me as well. Please, test the attached patch in my other mail. I would like to know what bison version does Michael use, maybe some difference from my bison-2.3 might explain his test result. Tom, you surely know more about bison releases and its grammar changes than me, you might give me some enlightenment on this issue. It might turn out that my Fedora 9 bison is not bugfree. Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH 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] Split-up ECPG patches
Michael Meskes írta: > On Mon, Aug 03, 2009 at 06:59:30PM +0200, Boszormenyi Zoltan wrote: > >>> Why is this messing with the core grammar? >>> >> ... >> > > Zoltan, could you please explain why you unrolled FORWARD and BACKWARD? I > tried > applying the rest of your patch, without this unrolling but didn't get any > shift/reduce problem. Might have been that I missed something, so could you > please try again? > Without a re-quoted explanation, please, compare your modified patch with the attached one. I rolled FORWARD and BACKWARD back into fetch_direction in the core grammar, deleting the newly introduced FETCH and MOVE rules from the core and ECPG grammar and again I got this during the ECPG grammar compilation: ... "/usr/bin/perl" ./parse.pl . < ../../../../src/backend/parser/gram.y > preproc.y /usr/bin/bison -d -o preproc.c preproc.y preproc.y: conflicts: 2 shift/reduce preproc.y: expected 0 shift/reduce conflicts make[4]: *** [preproc.c] Error 1 make[4]: Leaving directory `/home/zozo/Schönig-számlák/leoni/2/pgsql/src/interfaces/ecpg/preproc' ... FYI: $ rpm -q bison flex bison-2.3-5.fc9.x86_64 flex-2.5.35-2.fc9.x86_64 > Tom, AFAICT we only need one core grammar change, moving the cursor name to > it's own rule that only resolves back to name. This rule should be eliminated > by bison during the build process anyway, so I see no problem adding it. It > does make the ecpg changes way smaller though. Is this okay with you? > > Zoltan, two more things about this patch need to be cleared: > - I don't think your code is able to handle varchars. > I will test that, thanks. > - There is no test. Please add this to some of our test cases or write a new > one. > I will write some regression tests, of course. > Some variable handling commands look suspicious to me, a test case might > alleviate my concerns. > Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH http://www.postgresql.at/ diff -dcrpN pgsql.orig/src/backend/parser/gram.y pgsql/src/backend/parser/gram.y *** pgsql.orig/src/backend/parser/gram.y 2009-08-03 10:38:28.0 +0200 --- pgsql/src/backend/parser/gram.y 2009-08-08 17:26:00.0 +0200 *** static TypeName *TableFuncTypeName(List *** 253,259 %type relation_name copy_file_name database_name access_method_clause access_method attr_name ! index_name name file_name cluster_index_specification %type func_name handler_name qual_Op qual_all_Op subquery_Op opt_class opt_validator validator_clause --- 253,259 %type relation_name copy_file_name database_name access_method_clause access_method attr_name ! index_name name cursor_name file_name cluster_index_specification %type func_name handler_name qual_Op qual_all_Op subquery_Op opt_class opt_validator validator_clause *** reloption_elem: *** 1915,1921 */ ClosePortalStmt: ! CLOSE name { ClosePortalStmt *n = makeNode(ClosePortalStmt); n->portalname = $2; --- 1915,1921 */ ClosePortalStmt: ! CLOSE cursor_name { ClosePortalStmt *n = makeNode(ClosePortalStmt); n->portalname = $2; *** comment_text: *** 4082,4095 * */ ! FetchStmt: FETCH fetch_direction from_in name { FetchStmt *n = (FetchStmt *) $2; n->portalname = $4; n->ismove = FALSE; $$ = (Node *)n; } ! | FETCH name { FetchStmt *n = makeNode(FetchStmt); n->direction = FETCH_FORWARD; --- 4082,4095 * */ ! FetchStmt: FETCH fetch_direction from_in cursor_name { FetchStmt *n = (FetchStmt *) $2; n->portalname = $4; n->ismove = FALSE; $$ = (Node *)n; } ! | FETCH cursor_name { FetchStmt *n = makeNode(FetchStmt); n->direction = FETCH_FORWARD; *** FetchStmt: FETCH fetch_direction from_in *** 4098,4111 n->ismove = FALSE; $$ = (Node *)n; } ! | MOVE fetch_direction from_in name { FetchStmt *n = (FetchStmt *) $2; n->portalname = $4; n->ismove = TRUE; $$ = (Node *)n; } ! | MOVE name { FetchStmt *n = makeNode(FetchStmt); n->direction = FETCH_FORWARD; --- 4098,4111 n->ismove = FALSE; $$ = (Node *)n;
Re: [HACKERS] Split-up ECPG patches
Michael Meskes writes: > Tom, AFAICT we only need one core grammar change, moving the cursor name to > it's own rule that only resolves back to name. This rule should be eliminated > by bison during the build process anyway, so I see no problem adding it. It > does make the ecpg changes way smaller though. Is this okay with you? Sure, that one didn't bother me. It was the FORWARD/BACKWARD decomposition that looked unnecessary (as your tests seem to bear out). 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] [PATCH] 2PC state files on shared memory
Robert Haas writes: > On Sat, Aug 8, 2009 at 9:31 AM, Heikki > Linnakangas wrote: >> I'm a bit disappointed by the performance gains. I would've expected >> more, given a decent battery-backed-up cache to buffer the WAL fsyncs. > It doesn't seem that surprising to me that a write to shared memory > and a write to an un-fsync'd file would be about the same speed. I just had a second thought about this. The idea is to avoid writing the separate 2PC state file until/unless it has to be checkpointed. (And, per the comments for CheckPointTwoPhase, that is an uncommon case --- especially now with our time-extended checkpoints.) What if PREPARE simply didn't write the 2PC file at all, except into WAL? Then, make CheckPointTwoPhase write the 2PC file for any still-live GXACT, by means of reaching into the WAL and pulling the data out. All it would need for that is the LSN of the WAL record, which I think the GXACT has already. (It might have the end location rather than the start, but in any case we could store both.) Similarly, COMMIT PREPARED could be taught to pull the data from WAL instead of a 2PC file, in the typical case where the file didn't exist yet. I think there might be some synchronization issues against checkpoints --- you couldn't recycle WAL until you were sure there was no COMMIT PREPARED pulling from it. But it seems possibly workable, and there's no tuning knob needed. 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] [PATCH] 2PC state files on shared memory
Heikki Linnakangas writes: > Tom Lane wrote: >> Quite aside from that, the fixed size of shared memory makes this seem >> pretty impractical. > Most state files are small. If one doesn't fit in the area reserved for > this, it's written to disk as usual. It's just an optimization. What evidence do you have for that assumption? And what's "small" anyway? I think setting the size parameter for this would be a frightfully difficult problem; the fact that average installations wouldn't use it doesn't make that any better for those who would. After our bad experiences with fixed-size FSM, I'm pretty wary of introducing new fixed-size structures that the user is expected to figure out how to size. > I'm a bit disappointed by the performance gains. I would've expected > more, given a decent battery-backed-up cache to buffer the WAL fsyncs. > But it looks like they're still causing the most overhead, even with a > battery-backed-up cache. If you can't demonstrate order-of-magnitude speedups, I think we shouldn't touch this. 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] Alpha releases: How to tag
Robert Haas wrote: > On Aug 7, 2009, at 11:50 PM, Bruce Momjian wrote: > > > David Fetter wrote: > >>> Odds are that the patch submitters will not understand enough to > >>> know how to modify pg_migrator, but just knowing something broke is > >>> usually enough for the hackers group to find a fix. > >> > >> This is a pretty serious drawback. If we're going to require that > >> people send migration scripts when they change the on-disk format, > >> this needs to be easy. The program, whatever it turns out to be, > >> needs to take composable changes which people would submit along with > >> their patch, just as they do docs. > >> > >> How much refactoring of pg_migrator would such a change take? Would > >> it be worthwhile to do that, or just start a different project? > > > > I have no idea what you are thinking. pg_migrator is in C just like > > the > > backend code. Do you want some plugin module to do migrations? I > > have > > neither the time or desire to implement that. > > It seems that it would also negate one of the major benefits of > pg_migrator, which is that it doesn't require you to rewrite all of > your data. I don't understand how this relates to rewriting data. David was asking if we should require patch submitters to update pg_migrator. If they can do that, great, but if not, we can figure out an approach for them. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Split-up ECPG patches
On Mon, Aug 03, 2009 at 06:59:30PM +0200, Boszormenyi Zoltan wrote: > > Why is this messing with the core grammar? > ... Zoltan, could you please explain why you unrolled FORWARD and BACKWARD? I tried applying the rest of your patch, without this unrolling but didn't get any shift/reduce problem. Might have been that I missed something, so could you please try again? Tom, AFAICT we only need one core grammar change, moving the cursor name to it's own rule that only resolves back to name. This rule should be eliminated by bison during the build process anyway, so I see no problem adding it. It does make the ecpg changes way smaller though. Is this okay with you? Zoltan, two more things about this patch need to be cleared: - I don't think your code is able to handle varchars. - There is no test. Please add this to some of our test cases or write a new one. Some variable handling commands look suspicious to me, a test case might alleviate my concerns. 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 ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: mes...@jabber.org Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use 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] [PATCH] 2PC state files on shared memory
On Sat, Aug 8, 2009 at 9:31 AM, Heikki Linnakangas wrote: > Tom Lane wrote: >> Michael Paquier writes: >>> Based on an idea of Heikki Linnakangas, here is a patch in order to improve >>> 2PC >>> by sending the state files of prepared transactions to shared memory instead >>> of disk. >> >> I don't understand how this can possibly work. The entire point of >> 2PC is that the state file is guaranteed to be on disk so it will >> survive a crash. What good is it if it's in shared memory? > > The state files are not fsync'd when they're written, but a copy is > written to WAL so that it can be replayed on crash. With this patch, > it's still written to WAL, but the write to a file on disk is skipped, > and it's stored in shared memory instead. > >> Quite aside from that, the fixed size of shared memory makes this seem >> pretty impractical. > > Most state files are small. If one doesn't fit in the area reserved for > this, it's written to disk as usual. It's just an optimization. > > I'm a bit disappointed by the performance gains. I would've expected > more, given a decent battery-backed-up cache to buffer the WAL fsyncs. > But it looks like they're still causing the most overhead, even with a > battery-backed-up cache. It doesn't seem that surprising to me that a write to shared memory and a write to an un-fsync'd file would be about the same speed. The file write will eventually generate some I/O when it goes to disk, but at the time you make the system call it's basically just a memory copy. ...Robert -- 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] GEQO vs join order restrictions
On Sun, Jul 19, 2009 at 3:08 PM, Tom Lane wrote: > Robert Haas writes: >> Tom, do you think the "independent subproblem" stuff from last night >> would be worth pursuing? > > It's worth looking into. I'm not certain if it will end up being a good > idea or not. Right now the joinlist collapse code is pretty stupid > (as you know --- it only thinks about the collapse_limit variables, > plus IIRC it knows about FULL JOIN). Making it smarter might result in > duplication of logic, or require unpleasant refactoring to avoid such > duplication, or even add more cycles than it's likely to save later on. > Another issue is order of operations: I'm not sure if all the > information needed to make such decisions has been computed at that > point. But we won't know unless we try it. It seems at least > potentially useful. I thought about this a little more and I don't think it's going to work. The problem is that LEFT joins can be reordered VERY freely. So if you have something like: A LJ (B IJ C IJ D) Then, sure, {B C D} is a subproblem. But if you have: A LJ (B IJ C IJ D) LJ E ...then it's not, any more. Suppose E is being joined against B, for example. You could decide to do this: A LJ (B LJ E IJ C IJ D) So I think this idea has crashed and burned, as Tom would say, unless someone has an idea for resurrecting it. ...Robert -- 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] [PATCH] 2PC state files on shared memory
Tom Lane wrote: > Michael Paquier writes: >> Based on an idea of Heikki Linnakangas, here is a patch in order to improve >> 2PC >> by sending the state files of prepared transactions to shared memory instead >> of disk. > > I don't understand how this can possibly work. The entire point of > 2PC is that the state file is guaranteed to be on disk so it will > survive a crash. What good is it if it's in shared memory? The state files are not fsync'd when they're written, but a copy is written to WAL so that it can be replayed on crash. With this patch, it's still written to WAL, but the write to a file on disk is skipped, and it's stored in shared memory instead. > Quite aside from that, the fixed size of shared memory makes this seem > pretty impractical. Most state files are small. If one doesn't fit in the area reserved for this, it's written to disk as usual. It's just an optimization. I'm a bit disappointed by the performance gains. I would've expected more, given a decent battery-backed-up cache to buffer the WAL fsyncs. But it looks like they're still causing the most overhead, even with a battery-backed-up cache. -- 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] hot standby - merged up to CVS HEAD
On Sat, Aug 8, 2009 at 12:02 AM, Bruce Momjian wrote: > Well, Simon stated that your version should now be used as the most > recent one, so I would call that a success. Fair enough, but it still needs more work. I had some review comments I was hoping to get responses to, in the section beginning with "A few other comments based on a preliminary reading of this patch": http://archives.postgresql.org/pgsql-hackers/2009-07/msg00854.php ...Robert -- 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] Split-up ECPG patches
Michael Meskes írta: > On Fri, Aug 07, 2009 at 12:08:00PM -0400, Alvaro Herrera wrote: > >> I think we're normally OK with mentioning the authors, i.e. "Author: >> > > Yes, it's OK, but I think we normally only acknowledge the author in our > commit > messages, don't we? > > >> such and such", but the (C) line should attribute copyright to UCB/PGDG. >> Michael is free to dictate something else for ECPG of course ... >> > > No special rule for ecpg, I absolutely agree. > > Michael > OK, I can resend if you want. Or feel free to delete the (C) lines from the sqlda.c file. Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH 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] Patch : seq scan readahead (WIP)
A Dissabte, 8 d'agost de 2009, Pierre Frédéric Caillaud va escriure: > I guess it would need some experimenting with the values, and a > per-tablespace setting, but since lots of people use Linux Software RAID1 > on servers, this might be interesting... > > You guys want to try it ? Your tests involve only one user. What about having two (or more) users reading different tables? You're using both disks for one user for a 35% performance gain "only"... > > Patch attached. -- Albert Cervera i Areny http://www.NaN-tic.com Mòbil: +34 669 40 40 18 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Patch : seq scan readahead (WIP)
This is a spinoff of the current work on compression... I've discovered that linux doesn't apply readahead to sparse files. So I added a little readahead in seq scans. Then I realized this might also be beneficial for the standard Postgres. On my RAID1 it shows some pretty drastic effects. The PC : - RAID1 of 2xSATA disks, reads at about 60 MB/s - RAID5 of 3xSATA disks, reads at about 210 MB/s Both RAIDs are Linux Software RAID. Test data : A 9.3GB table with not too small rows, so count(*) doesn't use lots of CPU. The problem : - On the RAID5 there is no problem, count(*) maxes out the disk. - On the RAID1, count(*) also maxes out the disk, but there are 2 disks. One works, one sits idle. It does nothing. Linux Software RAID cannot use 2 disks on sequential reads, at least on my kernel version. What do your boxes do in such a situation ? For standard postgres, iostat says : Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 3,00 0,0040,00 0 40 sdb 727,00116600,0040,00 116600 40 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 124,00 23408,00 0,00 23408 0 sdb 628,00101640,00 0,00 101640 0 Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 744,00124536,00 0,00 124536 0 sdb 0,00 0,00 0,00 0 0 Basically it is reading the disks in turn, but not at the same time. The solution : Somehow coerce Linux Software RAID to stripe reads across the 2 mirrors to get more throughput. After a bit of fiddling, this seems to do it : - for each page read in a seq scan Strategy 0 : do nothing (this is the current strategy) Strategy 1 : issue a Prefetch call 4096 pages ahead (32MB) of current position Strategy 2 : if (the current page & 4096) == 1, issue a Prefetch call 4096 pages ahead (32MB) of current position Strategy 3 : issue a prefetch at 32MB * ((the current page & 4096) ? 1 : 2) ahead of current position Results to seq scan 9.3GB of data on the RAID5 : Strategy 0 :46.4 s It maxes out the disk anyway, so I didn't try the others. However RAID1 is better for not so read-only databases... Results to seq scan 9.3GB of data on the RAID1 : Strategy 0 :162.8 s Strategy 1 :152.9 s Strategy 2 :105.2 s Strategy 3 :152.3 s Strategy 2 cuts the seq scan duration by 35%, ie. disk bandwidth gets a +54% boost. For strategy 2, iostat says : Device:tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 625,00105288,00 0,00 105288 0 sdb 820,00105968,00 0,00 105968 0 Both RAID1 volumes are exploited at the same time. I guess it would need some experimenting with the values, and a per-tablespace setting, but since lots of people use Linux Software RAID1 on servers, this might be interesting... You guys want to try it ? Patch attached. diff -rupN postgresql-8.4.0-orig/src/backend/access/heap/heapam.c postgresql-8.4.0-ra/src/backend/access/heap/heapam.c --- postgresql-8.4.0-orig/src/backend/access/heap/heapam.c 2009-06-11 16:48:53.0 +0200 +++ postgresql-8.4.0-ra/src/backend/access/heap/heapam.c2009-08-08 10:41:15.0 +0200 @@ -135,6 +135,8 @@ initscan(HeapScanDesc scan, ScanKey key, { if (scan->rs_strategy == NULL) scan->rs_strategy = GetAccessStrategy(BAS_BULKREAD); + + scan->rs_readahead_pages = 4096;/* TODO: GUC ? or maybe put it in AccessStrategy ? */ } else { @@ -766,6 +768,12 @@ heapgettup_pagemode(HeapScanDesc scan, if (page == 0) page = scan->rs_nblocks; page--; + + /* +* do some extra readahead (really needed for compressed files) +*/ + if( scan->rs_readahead_pages && !finished ) + PrefetchBuffer( scan->rs_rd, MAIN_FORKNUM, page - scan->rs_readahead_pages + ((page >= scan->rs_readahead_pages) ? 0 : scan->rs_nblocks)); } else { @@ -788,6 +796,13 @@ heapgettup_pagemode(HeapScanDesc scan, */ if (scan->rs_syncscan) ss_report_location(scan->rs_rd, page); + + /* +* do some extra readahead (really needed for compressed files) +*/ + + if( scan->rs_readahead_pages && !finished && (page & 4096)) + PrefetchBuffer( scan->rs_rd, MAIN_FORKNUM, (page + scan->rs_r
Re: [HACKERS] Split-up ECPG patches
On Fri, Aug 07, 2009 at 12:08:00PM -0400, Alvaro Herrera wrote: > I think we're normally OK with mentioning the authors, i.e. "Author: Yes, it's OK, but I think we normally only acknowledge the author in our commit messages, don't we? > such and such", but the (C) line should attribute copyright to UCB/PGDG. > Michael is free to dictate something else for ECPG of course ... No special rule for ecpg, I absolutely agree. 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 ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: mes...@jabber.org Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use 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] Split-up ECPG patches
On Fri, Aug 07, 2009 at 04:03:38PM +0200, Boszormenyi Zoltan wrote: > I added notice about the PostgreSQL license. Is it ok? > Or should I resend without indicating the authors? Normally all our source files are "Copyright PostgreSQL Global Development Group" and I don't see a reason why this should be handled differently. 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 ICQ: 179140304, AIM/Yahoo/Skype: michaelmeskes, Jabber: mes...@jabber.org Go VfL Borussia! Go SF 49ers! Use Debian GNU/Linux! Use 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] WIP: to_char, support for EEEE format
2009/8/3 Tom Lane : > Uh, no, we had better support more. The actual limit of the current > numeric format is 1e+131072. > Given your comment above I'm thinking it reasonable to use an int32 to store the exponent -- will that be safe? That would allow for a maximum of 10 exponent digits. As an aside, I note that int4out() hardcodes the maximum number of digits rather than exposing a constant (c.f. MAXINT8LEN in int8.c). I'm considering adding MAXINT2LEN and MAXINT4LEN to int.c in passing. Excessive tinkering, or worthy improvement? 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