Re: [HACKERS] hot standby - merged up to CVS HEAD
On Fri, Aug 7, 2009 at 11:33 PM, Bruce Momjian wrote: > Third, Robert, you should have communicated to the list that you were > going to work on the patch, so that there would not be duplicate effort > if someone else was also working on it. As I understood it, Heikki was > in control of the patch, but it doesn't hurt to send out a short email > stating you wanted to work on it now. In this case no one was working > on it, but if someone had been, there would have been duplicate effort > and that is disappointing to everyone. Odds are when you started on the > patch you didn't realize you would be overhauling it, but once that > became clear, an email to hackers, ideally CC'ing the original patch > authors, would have been a good idea. Simon asked me about this offlist as well: I'll repeat the gist of what I said to him here. I really wasn't sure how far I was going to be able to get with this, and I didn't put more work into it before sending it than the amount of time I was willing to waste on it. I figured that I was fairly safe because there had been no activity for 5 months, but if I had been wrong, I was prepared to accept that. I thought it would be a little arrogant to say I was going to work on the patch without having any idea whether I was going to be able to do anything useful with it; since I ended up getting something that may be useful done, it now seems like I should've said something, but that was a little less obvious at the time. At any rate, I am sensitive to the issue and will try to handle it better the next time. 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. ...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] pg_dump Add dumping of comments on index columns
2009/8/8 Bruce Momjian : > Just to verify, this patch was about comments on composite columns, not > about dumping comments on index columns (as the subject states), right? > We do have a TODO for index column comments: Correct. If you scroll up a couple of messages [1] you'll see that I added that TODO after I reviewed the patch. Cheers, BJ [1] 37ed240d0907212253peaab8abtf8fd58fbde44c...@mail.gmail.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] pg_dump Add dumping of comments on index columns
Tom Lane wrote: > Brendan Jurd writes: > > I've also done an initial review of the patch. Everything looks sane > > and the patch works as advertised. I made a couple of minor tweaks > > for code-style and comment consistency, and my version 3 is attached. > > > I'm marking this patch Ready for Committer. > > Applied with minor revisions --- mostly, it leaked memory in the case > of no comments, and the query wasn't very schema-safe. Just to verify, this patch was about comments on composite columns, not about dumping comments on index columns (as the subject states), right? We do have a TODO for index column comments: Forbid COMMENT on columns of an index Postgres currently allows comments to be placed on the columns of an index, but pg_dump doesn't handle them and the column names themselves are implementation-dependent. -- 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] hot standby - merged up to CVS HEAD
Robert Haas wrote: > On Fri, Aug 7, 2009 at 11:33 PM, Bruce Momjian wrote: > > Third, Robert, you should have communicated to the list that you were > > going to work on the patch, so that there would not be duplicate effort > > if someone else was also working on it. ?As I understood it, Heikki was > > in control of the patch, but it doesn't hurt to send out a short email > > stating you wanted to work on it now. ?In this case no one was working > > on it, but if someone had been, there would have been duplicate effort > > and that is disappointing to everyone. ?Odds are when you started on the > > patch you didn't realize you would be overhauling it, but once that > > became clear, an email to hackers, ideally CC'ing the original patch > > authors, would have been a good idea. > > Simon asked me about this offlist as well: I'll repeat the gist of > what I said to him here. I really wasn't sure how far I was going to > be able to get with this, and I didn't put more work into it before > sending it than the amount of time I was willing to waste on it. I > figured that I was fairly safe because there had been no activity for > 5 months, but if I had been wrong, I was prepared to accept that. I > thought it would be a little arrogant to say I was going to work on > the patch without having any idea whether I was going to be able to do > anything useful with it; since I ended up getting something that may > be useful done, it now seems like I should've said something, but that > was a little less obvious at the time. At any rate, I am sensitive to > the issue and will try to handle it better the next time. Yea, it is a learning experience. > 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. -- 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] Alpha releases: How to tag
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. ...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] Alpha releases: How to tag
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. -- 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] Fixing geometic calculation
Paul Matthews wrote: > EPSILON in postgres is 1.0E-06 > EPSILON for floats is 1.19209e-07 > EPSILON for doubles is 2.22045E-016 > Bad form to reply to my own post and all. If EPSILON for double represented 1mm, then postgres is rounding to the nearest 10,000,000 km. Since its only about 380,000 km to the moon it's a good thing does not use postgres. -- 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
Joshua D. Drake wrote: > On Wed, 2009-07-15 at 17:27 +0100, Simon Riggs wrote: > > On Tue, 2009-07-14 at 21:12 -0400, Robert Haas wrote: > > > > > > 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. > > > > Robert, thank you for your work. > > Simon you need to realize that a lot of people really want this patch. I > for one applaud Robert's work (and Heikki's). If you want a summer > holiday, go for it. I certainly haven't been working that hard this > summer. > > However, I certainly don't think that is any reason for people who are > showing initiation and drive should stop. If Robert wants to work on > this patch, more power to him. Perhaps he can solve something you can't. > Perhaps it will be done before you are done with holiday. If not, then > at least we have moved a little further in the process and in theory > taken some workload off of you. Sorry to be chiming in weeks late on this, but there are some procedural issues I want to address. First, I agree with everything Joshua Drake said, and thank you for chiming in on this. Second, Simon, you seem disappointed that Robert Haas helped with the hot standby patch. By your own admission you weren't working on it, so I would think you would be grateful that someone moved it forward. This is not a question of 'faith' and 'patience', but getting the patch completed. The goal is to get the patch completed, not for a single individual to complete it. Third, Robert, you should have communicated to the list that you were going to work on the patch, so that there would not be duplicate effort if someone else was also working on it. As I understood it, Heikki was in control of the patch, but it doesn't hurt to send out a short email stating you wanted to work on it now. In this case no one was working on it, but if someone had been, there would have been duplicate effort and that is disappointing to everyone. Odds are when you started on the patch you didn't realize you would be overhauling it, but once that became clear, an email to hackers, ideally CC'ing the original patch authors, would have been a good idea. -- 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] Commitfest 2009-07 - 6 patches moved to "Returned with Feedback"
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 Thanks, ...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] GRANT ON ALL IN schema
I am sorry I forgot to write my opinion on these. Do we want to differentiate views from tables in these commands or not ? I'd like to have views separate but I don't feel strongly about it. However having single statement for TABLE, VIEW and SEQUENCE is not a good idea IMHO, it will add confusion with standard GRANT statement and I don't think we could call it a TABLE anymore. Do we want GRANT ON ALL (or GRANT ON * which is mysql style, btw) in SQL form (not functions or client enhancements) at all ? - if we decide that we don't want to have this as SQL statement then I'll drop the effort. 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. Adding the possibility of running commands on many objects at once in psql would be nice addition in the future, especially since we could have more wild syntax there, but I still feel strongly about having the simplest case handled by SQL. 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. -- Regards Petr Jelinek (PJMODOS) -- 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
Tom Lane wrote: > No, I'm worried about code that supposes that it can divide by (x - y) > after testing that FPeq(x,y) is not true. point_sl() for instance. > > We could perhaps fix those specific issues by testing the difference > explicitly instead of doing it like that. But there's still the overall > problem of error accumulation ... > > regards, tom lane > 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. I don't see why it should be is postgres job to worry about error accumulation. And then why only worry about it in geometric calculations. Where in normal postgres code, or in perl, python, lisp, or any in any other general purpose tool, is it where you ask it to tell you 1.0+1.0 it responds with 2.0 +or- 0.0001? -- 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
Tom Lane wrote: > It's a hack but it's dealing with an extremely real problem, namely > the built-in inaccuracy of floating-point arithmetic. You can't just > close your eyes to that and hope that everything will be okay. > If the above statement was true, then the FP* macros should be extended to all numerical calculations in postgres. And I don't think anyone here would suggest that doing that, as the results, as per my previous email, would be immediately and clearly ludicrous. Yes, floating point arithmetic has built in inaccuracy. However the FP* macros produce results that are even less accurate than single point arithmetic, and less accurate than my 25 year old calculator. And if I could find my slide rule, it could probably do better. At best, the EPSILON value might have be appropriate for single precision arithmetic, but in no way is it appropriate for double precision arithmetic. Visual C++ defines EPSILON as "The difference between 1 and smallest value greater than 1". The postgres EPSILON is 10 orders of magnitude greater than the precision supported by the underlying hardware. This is clearly preposterous. EPSILON in postgres is 1.0E-06 EPSILON for floats is 1.19209e-07 EPSILON for doubles is 2.22045E-016 -- 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
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? Quite aside from that, the fixed size of shared memory makes this seem pretty impractical. 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
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? The other point is that we don't change the on-disk format for fun, but for good reason. If it's going to get harder we'll pay a price. That doesn't mean we should go on as we have been, but just saying "we've been sloppy" is telling less than half the story. If one believes the rumors, Oracle at least has in the past paid dearly for supporting old formats. 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] Alpha releases: How to tag
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? 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
On Fri, Aug 07, 2009 at 06:02:32PM -0400, Tom Lane wrote: > Andrew Dunstan writes: > > I think it's a lot more nebulous than that. At the same time I think the > > days when we can blithely change the on-disk format with hardly a > > thought for migration are over. IOW, there's agreement things have to > > change, but the exact shape of the change is not yet clear (at least to > > me) ;-) > > Yeah. I think we're going to start paying more than zero attention to > this, but we don't yet have a handle on what the real parameters are. > In particular, it's hard to argue that pg_migrator has yet achieved > more than experimental status, so accepting or rejecting patches on > the grounds of whether they would or would not break pg_migrator might > be a bit premature. And at the other end of the spectrum, nobody except > Zdenek wants to deal with changes as invasive as the ones he's proposed. > So we're still feeling our way here. We do *not* have a framework in > which someone could submit a patch that includes an on-disk migration > aspect, so David's position that we should immediately institute a > hard requirement for such seems a bit ivory-tower. I am not suggesting that this change be immediate, and it's not ivory tower. It's just how everybody else does it. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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
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. 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
Andrew Dunstan writes: > I think it's a lot more nebulous than that. At the same time I think the > days when we can blithely change the on-disk format with hardly a > thought for migration are over. IOW, there's agreement things have to > change, but the exact shape of the change is not yet clear (at least to > me) ;-) Yeah. I think we're going to start paying more than zero attention to this, but we don't yet have a handle on what the real parameters are. In particular, it's hard to argue that pg_migrator has yet achieved more than experimental status, so accepting or rejecting patches on the grounds of whether they would or would not break pg_migrator might be a bit premature. And at the other end of the spectrum, nobody except Zdenek wants to deal with changes as invasive as the ones he's proposed. So we're still feeling our way here. We do *not* have a framework in which someone could submit a patch that includes an on-disk migration aspect, so David's position that we should immediately institute a hard requirement for such seems a bit ivory-tower. We might as well just say that the on-disk format is frozen, because that's what the effect would be. But having said all that, I'm okay with a go-slow position on on-disk changes for the next little while. That would give us some breathing room to see if something like pg_migrator is really workable at all. We need to find out just how far that approach goes before we can make many decisions in this area. 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
[HACKERS] contrib/pg_freespacemap
Hi, Is there any reason we didn't move the pg_freespace function from contrib to core? -- 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] [GENERAL] \copy: unexpected response (4)
Neil Best writes: > On Fri, Aug 7, 2009 at 12:33 PM, Tom Lane wrote: >> BTW, the "SSL renegotiation failure" bit >> suggests that it could have been an OpenSSL bug not a real network >> lossage, so you might want to see how up-to-date your openssl libraries >> are. > Thanks for your comments, Tom. The operation seems more reliable if I > move the data to the server and do it across a local connection, which > I presume does not involve SSL, so that may be the weak link as you > surmise. Would you expect the SSL library problem more likely to be > on the server or the client, or is it just hard to say? You're talking like you've found this to be repeatable. Is it? 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] Implementation of GROUPING SETS (T431: Extended grouping capabilities)
Олег Царев escribió: > Hello all! > If no one objecte (all agree, in other say) i continue work on patch - > particulary, i want support second strategy (tuple store instead of > hash-table) for save order of source (more cheap solution in case with > grouping sets + order by), investigate and brainstorm another > optimisation, writing regression tests and technical documentation. > But I need some time for complete my investigation internals of > PostgreSQL, particulary CTE. Where are we on this patch? Is it moving forward? -- 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] Durability?
Emmanuel Cecchet writes: > Tom Lane wrote: >> It looks like you've got a corrupted page in shared buffers, and every >> time the system tries to flush it to disk for a checkpoint, it fails. > Will the database be able to restart with a corrupted WAL? I don't think you have a corrupted WAL. 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
Alvaro Herrera wrote: David Fetter wrote: 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. But, are we? I think it's a lot more nebulous than that. At the same time I think the days when we can blithely change the on-disk format with hardly a thought for migration are over. IOW, there's agreement things have to change, but the exact shape of the change is not yet clear (at least to me) ;-) 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] Alpha releases: How to tag
On Fri, Aug 07, 2009 at 05:32:13PM -0400, Alvaro Herrera wrote: > David Fetter wrote: > > > 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. > > But, are we? This is an area where we have been sorely lacking and sloppy for years. Every other RDBMS development team requires it. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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
David Fetter wrote: > 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. But, are we? -- 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] Alpha releases: How to tag
On Fri, Aug 07, 2009 at 04:07:08PM -0400, Bruce Momjian wrote: > David Fetter wrote: > > On Mon, Aug 03, 2009 at 12:19:40PM -0400, Tom Lane wrote: > > > David Fetter writes: > > > > On Mon, Aug 03, 2009 at 11:32:48AM -0400, Tom Lane wrote: > > > >> And I doubt we'd bother generating pg_migrator builds that > > > >> work for pairs of alpha releases. > > > > > > > That's an interesting idea. Shouldn't pg_migrator be mandated > > > > to work for *any* catversion bump? > > > > > > Oh, are you volunteering to make that happen? That code is > > > going to be ugly enough just dealing with pairs of major > > > releases ... > > > > With all due respect, if pg_migrator does not function in such a > > way as to have data-driven composable changes, it's going to bang > > us up in the long haul much worse than not branching alphas ever > > could. > > > > We require that people supply docs with their changes, and it is > > totally unreasonable to let them send in catalog changes which do > > not include need migration changes. That's how it works in every > > other RDBMS outfit that has changes on disk, and we do not need to > > be the exception. > > > > If pg_migrator doesn't have this ability, we need to refactor it > > until it does, or go with something that can. > > 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? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] [GENERAL] \copy: unexpected response (4)
On Fri, Aug 7, 2009 at 12:33 PM, Tom Lane wrote: > BTW, the "SSL renegotiation failure" bit > suggests that it could have been an OpenSSL bug not a real network > lossage, so you might want to see how up-to-date your openssl libraries > are. Thanks for your comments, Tom. The operation seems more reliable if I move the data to the server and do it across a local connection, which I presume does not involve SSL, so that may be the weak link as you surmise. Would you expect the SSL library problem more likely to be on the server or the client, or is it just hard to say? Does either of them have a facility that exposes the SSL version information or do I have to go to the OS for that? Incidentally, I have not experienced any sort of instability in my interactive sessions over an SSL connection, so is it related to the \copy operation itself, the higher volume of data across the connection, or the fact that I am asking it to do multiple \copies in rapid succession, would you say? I expect it's hard to say definitively, but maybe you or someone else can say something about likelihoods. I appreciate the information. -- 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] docs: mention autovacuum when ANALYZE is recommended
Alvaro Herrera wrote: > Tom Lane wrote: > > Alvaro Herrera writes: > > > Bruce asked me to look for places in the docs that mention that an > > > ANALYZE is recommended, to mention the possibility that autovacuum takes > > > care. This patch does that. > > > > I think you found the right places to touch, but is "let the autovacuum > > daemon do it" sufficient? It seems like that needs some qualifiers > > about whether autovacuum is enabled, how long you should expect to wait > > for the stats to get updated, etc. It's probably not a good idea to > > duplicate all that in each place, but maybe a link to the main > > documentation about autovacuum is reasonable in each place. > > Sorry this fell through the cracks. How does this look? My idea would > be to backpatch it to 8.3 and 8.4. Committed, but backpatched to 8.4 only. -- 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] Durability?
Tom Lane wrote: Emmanuel Cecchet writes: I got an error like this: ERROR: xlog flush request 1/C121E998 is not satisfied --- flushed only to 1/BCBCB440 CONTEXT: writing block 529 of relation 1663/233690/1247 WARNING: could not write block 529 of 1663/233690/1247 DETAIL: Multiple failures --- write error might be permanent. The xrecoff value (logs show 1/xrecoff) advances a few times during the day, but the message keeps appearing. It looks like you've got a corrupted page in shared buffers, and every time the system tries to flush it to disk for a checkpoint, it fails. What I'd try for getting out this is to kill -9 some backend in order to force a database restart. Of course, if you want to investigate what caused it, you should dig around in shared memory first and try to get a copy of that buffer's contents. Will the database be able to restart with a corrupted WAL? If the database restarts, what transactions will be missing: - just the block that couldn't be flushed? - all transactions that were committed after the faulty block? - more? Thanks Emmanuel -- 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] Review: Revise parallel pg_restore's scheduling heuristic
"Kevin Grittner" writes: > Robert Haas wrote: >> So should we give up on this patch? > No, this is not news; just confirmation of the earlier gut feelings > and less convincing statistics that there is no problem. Tom's > argument that if there's no slowdown for common cases, preventing > O(N^2) behavior for extreme cases is compelling for me, and we've > beaten up on it enough for me to feel comfortable that it doesn't > break anything. Yeah. I had hoped to see some evidence of actual improvement for common cases, but we haven't got that. What we do have is evidence that it's not making common cases worse, so avoiding the possible O(N^2) behavior for extreme cases seems like a reasonable argument for committing it anyway. I'll go ahead and commit it just to get it out of the queue ... we can always revert the commit if new info surfaces. 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] Durability?
Emmanuel Cecchet writes: > I got an error like this: > ERROR: xlog flush request 1/C121E998 is not satisfied --- flushed only to > 1/BCBCB440 > CONTEXT: writing block 529 of relation 1663/233690/1247 > WARNING: could not write block 529 of 1663/233690/1247 > DETAIL: Multiple failures --- write error might be permanent. > The xrecoff value (logs show 1/xrecoff) advances a few times during the day, > but the message keeps appearing. It looks like you've got a corrupted page in shared buffers, and every time the system tries to flush it to disk for a checkpoint, it fails. What I'd try for getting out this is to kill -9 some backend in order to force a database restart. Of course, if you want to investigate what caused it, you should dig around in shared memory first and try to get a copy of that buffer's contents. 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] Review: Revise parallel pg_restore's scheduling heuristic
I wrote: > I remember someone else on the thread saying [...] > it provided better structure for future enhancements. Found the reference: http://archives.postgresql.org/pgsql-hackers/2009-08/msg00078.php This was the email which I thought confirmed that the changes were worth it, even in the absence of benchmarks showing performance improvement. I guess the counter-argument is that so far this has been framed as a performance patch, and I've seen posts before which say that we don't accept those without benchmarks to show an actual performance improvement. Accepting it on the basis of evidence and comments so far would, I suppose, put it more in the category of a refactoring for cleaner code. Should we leave it to the code committer to make the final call? -Kevin -- 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] Re: [Pg-migrator-general] Composite types break pg_migrated tables
Tom Lane wrote: > Bruce Momjian writes: > > Peter Eisentraut wrote: > >> That might be a bit excessive. As I understand it, arrays of built-in > >> types > >> (e.g., int[]) should work fine. I suspect the majority of uses of arrays > >> will > >> be with built-in types, so allowing that would help a significant portion > >> of > >> installations. > > > Agreed. I realized that last night, and have modified pg_migrator to > > test FirstNormalObjectId. > > That's really the wrong thing. It's safe to assume OIDs below 1 > are portable across versions, because for them not to be would require > someone to have changed a hand assignment. However, OIDs between 1 > and 16K are assigned on-the-fly by initdb, and those are *not* likely > to be portable across versions. As an example, the rowtype for > pg_statistic has slightly different OIDs in 8.3 and 8.4. So if you > allow someone to port a database that is using a system catalog's > rowtype, it will fail. Admittedly that's not a real likely scenario, > but if you're going to have a check it should be accurate. Thanks, I changed FirstNormalObjectId to FirstBootstrapObjectId for the array/enum/composite oid test, and added a C comment about it. -- 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] Alpha releases: How to tag
David Fetter wrote: > On Mon, Aug 03, 2009 at 12:19:40PM -0400, Tom Lane wrote: > > David Fetter writes: > > > On Mon, Aug 03, 2009 at 11:32:48AM -0400, Tom Lane wrote: > > >> And I doubt we'd bother generating pg_migrator builds that work > > >> for pairs of alpha releases. > > > > > That's an interesting idea. Shouldn't pg_migrator be mandated to > > > work for *any* catversion bump? > > > > Oh, are you volunteering to make that happen? That code is going to > > be ugly enough just dealing with pairs of major releases ... > > With all due respect, if pg_migrator does not function in such a way > as to have data-driven composable changes, it's going to bang us up in > the long haul much worse than not branching alphas ever could. > > We require that people supply docs with their changes, and it is > totally unreasonable to let them send in catalog changes which do not > include need migration changes. That's how it works in every other > RDBMS outfit that has changes on disk, and we do not need to be the > exception. > > If pg_migrator doesn't have this ability, we need to refactor it until > it does, or go with something that can. 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. -- 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] Alpha releases: How to tag
Tom Lane wrote: > Magnus Hagander writes: > > I haven't actually looked into pg_migrator enough to know how likely > > it is that it'll "just work" going alpha->alpha when there have only > > been "normal" changes? How invasive are the changes that actually > > require pg_migrator to be touched at all? > > To my mind there are three categories of stuff that pg_migrator has to > do: > > * catalog changes (handled by pg_dump/reload) > * on-disk data layout changes (not handled yet anyway) > * random weird unclassifiable stuff > > It's the third category that is the problem. An example from the 8.3 to > 8.4 migration is the need to specially handle sequences because they're > not compatible on-disk. Any pair of releases you might pick is going to > have its own little quirks like that. Our intention (at least as I see > it) is to minimize pg_migrator's complexity by decreeing that any given > release of pg_migrator deals with exactly one pair of PG releases. > I don't think that works if alpha->alpha has to be supported --- in > particular, 8.5alpha1 to 8.5alpha2 might be quite different from what > will be needed later for 8.4 to 8.5, so you would end up with multiple > live branches of pg_migrator, or else a lot of spaghetti code trying to > track which actions to take for a given case. > > The other little problem is who's going to do this work and when. > The alpha-release idea was sold to us on the basis of being a small > amount of incremental work: just tag an alpha release right after > CommitFest and kick it out there. If we're waiting around for someone > to produce and test a compatible pg_migrator, that's not going to be > how it works. Because the 8.5 is alpha anyway, and because pg_migrator works with current CVS, let's just say it works and wait for someone to report a problem. -- 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] Alpha releases: How to tag
Magnus Hagander wrote: > > The bug-fixing situation for betas and RCs is a bit different because > > it's expected that there will be a compatible update available shortly. > > So you can usually assume that updating to the next beta/RC/release will > > fix whatever problems got found. ?Alphas are going to be out there on > > their own with absolutely no expectation that the next alpha is > > catversion-compatible. ?And I doubt we'd bother generating pg_migrator > > builds that work for pairs of alpha releases. > > > > I agree with you that it'd be insane to run anything mission-critical on > > an alpha build; but I could see someone having loaded up quite a lot of > > test data on one. ?(If we aren't hoping for some pretty serious testing > > of these releases, I am not sure what is the point of doing them at all.) > > So it seems to me that having the ability to fix show-stopper bugs > > without forcing a migration to a later alpha would be a good thing. > > Maybe we'll never need it, or maybe we will. > > I think the alpha->alpha migration would actually be very useful to > these testers. That'll get them onto the new alpha. I think that's > actually a lot more important than alpha->release or release->next > alpha. > > I haven't actually looked into pg_migrator enough to know how likely > it is that it'll "just work" going alpha->alpha when there have only > been "normal" changes? How invasive are the changes that actually > require pg_migrator to be touched at all? pg_migrator handles 8.5devel just fine now with no changes. What will happen is that people will try pg_migrator in 8.5devel and if it fails we will hear about it and and adjust pg_migrator (or the backend) to get it working. This will give us early warning about backend changes and hopefully make pg_migrator more useful when 8.5 final is released. What I have found is that pg_migrator has a useful toolkit for detecting and correcting database incompatibilities. It wasn't easy to get working but now that it is complete adding stuff is actually easy --- see the enum/array/composite fix that was done yesterday within 24 hours of the report. Now, I am not guaranteeing that pg_migrator is always going to work for 8.5devel, but while it does let's use it, and when it fails, we can just update the README to say it doesn't work for 8.5devel, or fix things. -- 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] Alpha releases: How to tag
Alvaro Herrera wrote: > David Fetter wrote: > > > Is it strictly necessary that its release cycles match exactly those > > of the database engine, or would it be OK for it to release as needed, > > not triggering a major PostgreSQL release? > > Well, pg_migrator already released an 8.4.1 ... Well, actually at 8.4.4 right now, but who's counting. It is clear that pg_migrator has to be separate so it can rapidly address deficiencies without being tied to database server releases. We found a problem with arrays/composites/enums two days ago and had it fixed/detected in 24 hours. -- 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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock
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. -- 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] Alpha releases: How to tag
Robert Haas wrote: > On Mon, Aug 3, 2009 at 2:07 PM, David Fetter wrote: > > On Mon, Aug 03, 2009 at 12:19:40PM -0400, Tom Lane wrote: > >> David Fetter writes: > >> > On Mon, Aug 03, 2009 at 11:32:48AM -0400, Tom Lane wrote: > >> >> And I doubt we'd bother generating pg_migrator builds that work > >> >> for pairs of alpha releases. > >> > >> > That's an interesting idea. ?Shouldn't pg_migrator be mandated to > >> > work for *any* catversion bump? > >> > >> Oh, are you volunteering to make that happen? ?That code is going to > >> be ugly enough just dealing with pairs of major releases ... > > > > With all due respect, if pg_migrator does not function in such a way > > as to have data-driven composable changes, it's going to bang us up in > > the long haul much worse than not branching alphas ever could. > > > > We require that people supply docs with their changes, and it is > > totally unreasonable to let them send in catalog changes which do not > > include need migration changes. ?That's how it works in every other > > RDBMS outfit that has changes on disk, and we do not need to be the > > exception. > > > > If pg_migrator doesn't have this ability, we need to refactor it until > > it does, or go with something that can. > > I don't disagree with this, but we don't have anyone to do the work > atm, even if it were exactly clear what to do, which it isn't. What > we could do, though, is try to figure out whether it will work between > 8.4.0 and 8.5alpha1. I just tested this and it works fine. In fact it even does 8.3 to 8.5devel, with no pg_migrator changes needed. -- 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] Review: Revise parallel pg_restore's scheduling heuristic
On Fri, Aug 7, 2009 at 3:36 PM, Sam Mason wrote: > On Fri, Aug 07, 2009 at 03:18:54PM -0400, Robert Haas wrote: >> On Fri, Aug 7, 2009 at 3:08 PM, Kevin Grittner >> wrote: >> > With the 20 samples from that last round of tests, the answer (rounded >> > to the nearest percent) is 60%, so "probably noise" is a good summary. >> >> So should we give up on this patch? > > That's the joy of stats, it only tells you *very* precisely about the > *exact* thing you've chosen to test. Interpreting the result is still > awkward, but it does remove one problem! > > If you think the tests that've been done cover the use cases that the > new code was been designed to help with and you're not showing any > benefit I'd probably give up and put it down to a learning experience. > Sorry, but I've not been following enough to comment on this much more. Yeah, I more wanted to here from Tom or Kevin or anyone else who had a technical thought about this. I haven't looked at the patch, but there may be reasons other than performance to commit it - or there may not. Tom posted a note on the commitfest suggesting that maybe we should give up on this, and I don't care one way or the other, except that in my role as CommitFest manager (or Mom) I'm trying to push the remaining patches to some sort of conclusion: either committed, or rejected, or needs more work please resubmit for the next CommitFest. ...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] Review: Revise parallel pg_restore's scheduling heuristic
Robert Haas wrote: > So should we give up on this patch? No, this is not news; just confirmation of the earlier gut feelings and less convincing statistics that there is no problem. Tom's argument that if there's no slowdown for common cases, preventing O(N^2) behavior for extreme cases is compelling for me, and we've beaten up on it enough for me to feel comfortable that it doesn't break anything. I held off on investigating the artificial extreme cases when I thought we might possibly have a small performance problem here; but this statistics exercise has just gotten me from "gut feel" that it was noise and "not having 90% confident that we made things worse" to "90% of the time noise would produce a bigger difference". It's one thing to require 90% confidence that an improvement is real before accepting something, it's another to accept a change on the basis of not having 90% confidence that there is degradation -- so I wanted to see a more compelling statistic. Personally, I'm happy with it being in "Ready for Committer" status. I remember someone else on the thread saying that besides the elimination of O(N^2) behavior, it provided better structure for future enhancements. I'll run a few more benchmarks over the next few weeks to try to characterize the improvements in extreme cases, "just for the record", but I don't think we want to wait for that; we've got justification enough as is. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Durability?
Hi, I got an error like this: ERROR: xlog flush request 1/C121E998 is not satisfied --- flushed only to 1/BCBCB440 CONTEXT: writing block 529 of relation 1663/233690/1247 WARNING: could not write block 529 of 1663/233690/1247 DETAIL: Multiple failures --- write error might be permanent. The xrecoff value (logs show 1/xrecoff) advances a few times during the day, but the message keeps appearing. I am not sure to understand clearly the consequences of such error since Postgres continues to accept new transactions. If my WAL is corrupted, are my transactions still durable? If this is a violation of durability, is there a way to force Postgres to terminate on such error? Thanks in advance for the clarification. Emmanuel -- 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] Review: Revise parallel pg_restore's scheduling heuristic
On Fri, Aug 07, 2009 at 03:18:54PM -0400, Robert Haas wrote: > On Fri, Aug 7, 2009 at 3:08 PM, Kevin Grittner > wrote: > > With the 20 samples from that last round of tests, the answer (rounded > > to the nearest percent) is 60%, so "probably noise" is a good summary. > > So should we give up on this patch? That's the joy of stats, it only tells you *very* precisely about the *exact* thing you've chosen to test. Interpreting the result is still awkward, but it does remove one problem! If you think the tests that've been done cover the use cases that the new code was been designed to help with and you're not showing any benefit I'd probably give up and put it down to a learning experience. Sorry, but I've not been following enough to comment on this much more. -- Sam http://samason.me.uk/ -- 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] Review: Revise parallel pg_restore's scheduling heuristic
On Fri, Aug 07, 2009 at 02:08:21PM -0500, Kevin Grittner wrote: > With the 20 samples from that last round of tests, the answer (rounded > to the nearest percent) is 60%, so "probably noise" is a good summary. > Combined with the 12 samples from earlier comparable runs with the > prior version of the patch, it goes to a 90% probability that noise > would generate a difference at least that large, so I think we've > gotten to "almost certainly noise". :-) > > To me, that seems more valuable for this situation than saying "we > haven't reached 90% confidence that it's a real difference." I used > the same calculations up through the t-statistic. The stats people in our group just tend to say that things are significant or not at a specific level; never bothered to find out why, I'll ask someone when I get a chance. > The one question I have left for this technique is why you went with > > ((avg1 - avg2) / (stddev * sqrt(2/samples))) > instead of > ((avg1 - avg2) / (stddev / sqrt(samples))) I was just doing a literal translation of what was on the Wikipedia page: http://en.wikipedia.org/wiki/Student's_t-test#Independent_two-sample_t-test If you really want to find out, there should be much better implementations in the pl/r language already in PG. I'd trust R much more than Wikipedia, but for things like this Wikipedia is reasonable. > I assume that it's because the baseline was a set of samples rather > than a fixed mark, but I couldn't pick out a specific justification > for this in the literature (although I might have just missed it), so > I'd feel more comfy if you could clarify. Sorry, that's about my limit! I've never studied stats, I'm a computer science person who just happens to be around people who use stats on a day-to-day basis and think it needs more use in the software world. I think you're right and you're aggregating the errors from two (assumed independent) datasets hence you want to keep a bit more of the error in there. As to the formal justification (and probably proof) I've no real idea. > Given the convenience of capturing benchmarking data in a database, > has anyone tackled implementation of something like the spreadsheet > TDIST function within PostgreSQL? Again, pl/r is what you want! -- Sam http://samason.me.uk/ -- 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] Review: Revise parallel pg_restore's scheduling heuristic
On Fri, Aug 7, 2009 at 3:08 PM, Kevin Grittner wrote: > Sam Mason wrote: > >> All we're saying is that we're less than 90% confident that there's >> something "significant" going on. All the fiddling with standard >> deviations and sample sizes is just easiest way (that I know of) >> that statistics currently gives us of determining this more formally >> than a hand-wavy "it looks OK to me". Science tells us that humans >> are liable to say things are OK when they're not, as well as vice >> versa; statistics gives us a way to work past these limitations in >> some common and useful situations. > > Following up, I took the advice offered in the referenced article, and > used a spreadsheet with a TDIST function for more accurate results > than available through the table included in the article. That allows > what I think is a more meaningful number: the probability that taking > a sample that big would have resulted in a t-statistic larger than was > actually achieved if there was no real difference. > > With the 20 samples from that last round of tests, the answer (rounded > to the nearest percent) is 60%, so "probably noise" is a good summary. > Combined with the 12 samples from earlier comparable runs with the > prior version of the patch, it goes to a 90% probability that noise > would generate a difference at least that large, so I think we've > gotten to "almost certainly noise". :-) So should we give up on this patch? ...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] Review: Revise parallel pg_restore's scheduling heuristic
Sam Mason wrote: > All we're saying is that we're less than 90% confident that there's > something "significant" going on. All the fiddling with standard > deviations and sample sizes is just easiest way (that I know of) > that statistics currently gives us of determining this more formally > than a hand-wavy "it looks OK to me". Science tells us that humans > are liable to say things are OK when they're not, as well as vice > versa; statistics gives us a way to work past these limitations in > some common and useful situations. Following up, I took the advice offered in the referenced article, and used a spreadsheet with a TDIST function for more accurate results than available through the table included in the article. That allows what I think is a more meaningful number: the probability that taking a sample that big would have resulted in a t-statistic larger than was actually achieved if there was no real difference. With the 20 samples from that last round of tests, the answer (rounded to the nearest percent) is 60%, so "probably noise" is a good summary. Combined with the 12 samples from earlier comparable runs with the prior version of the patch, it goes to a 90% probability that noise would generate a difference at least that large, so I think we've gotten to "almost certainly noise". :-) To me, that seems more valuable for this situation than saying "we haven't reached 90% confidence that it's a real difference." I used the same calculations up through the t-statistic. The one question I have left for this technique is why you went with ((avg1 - avg2) / (stddev * sqrt(2/samples))) instead of ((avg1 - avg2) / (stddev / sqrt(samples))) I assume that it's because the baseline was a set of samples rather than a fixed mark, but I couldn't pick out a specific justification for this in the literature (although I might have just missed it), so I'd feel more comfy if you could clarify. Given the convenience of capturing benchmarking data in a database, has anyone tackled implementation of something like the spreadsheet TDIST function within PostgreSQL? -Kevin -- 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 Fri, Aug 07, 2009 at 07:48:15PM +0100, Greg Stark wrote: > On Fri, Aug 7, 2009 at 7:13 PM, Tom Lane wrote: > > Underflow. x!=y does not imply (x-y) != 0, if x and y are sufficiently > > small and close together. The difference could underflow to zero. > > Actually I don't think subtraction can underflow with IEEE floats but > I don't think we want to count on IEEE floats everywhere. Even if we > did there's the risk on intel that FPeq() gets called on values which > have just been calculated and are still in registers but then get > spilled to RAM and lose precision before the division happens. If it does one subtraction in registers you can be reasonably certain the other will be, either way just doing the subtraction and explicitly testing if it's zero will do the right thing--the semantics of C are bad but not that bad. -- Sam http://samason.me.uk/ -- 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
Stephen Frost wrote: As for changing the default ACL syntax to not be based around SCHEMA- I'm concerned that we'll then have to define some kind of ordering preference if we get away from the defaults being associated with the container object. If we have defaults for users and schemas, which takes precedence? I don't like the idea of trying to merge them. I'm also not really a fan of having the defaults be based on pattern-matching to a relation name, that's just creating another namespace headache, imv. Right, if we make it per user with different types of filters, we'd have to merge them when more then one applies, that might be confusing. For my needs, the syntax is not of great importance, I'll use what I have to. If ALTER DEFAULT PERMISSIONS is the concensus, then I'd rather at least have it than not have anything. Yeah ALTER DEFAULT PERMISSIONS actually seems like quite reasonable. But we need to have consensus on the filters, either have one (either schema or user based) or have multiple possibilities and then merge them if more then one applies. While I don't want to go against the SQL spec, it's opinion is that in 'GRANT SELECT ON TABLE tab1' the 'TABLE' is optional and not relevant. We can keep that and still implement a 'GRANT SELECT ON VIEW tab1' which is limited to only operating on views, allowing admins to be more explicit about what they want. That would at least reduce the disconnect between 'grant on all', 'default acls', and regular GRANT with regard to tables vs. views, presuming we keep them split. Well, reducing confusion between GRANT ON ALL + DefaultACLs and regular GRANT is the whole reason for GRANT ON VIEW. I think we either have to have VIEW in all of them or none of them. I do like the general idea of making it easier to run commands across multiple tables, etc, rather than having 'GRANT ON ALL' syntax. As I believe has been mentioned before, this is a case where we could improve our client tools rather than implement it on the server. For example: \cmd grant select on * to user Of course, our new psql * handling would mean this would grant select on everything in pg_catalog too, at least if we do the same as \d * This could be fixed using schema.* maybe if we did this ? Adding some kind of 'run-multiple' stored proc is an interesting idea but I'm afraid the users this is really targetting aren't going to appreciate or understand something like: select cmd('grant select on ' || quote_ident(nspname) || '.' || quote_ident(relname) || ' to public') from pg_class join pg_namespace on (pg_class.nspoid = pg_namespace.oid) where pg_namespace.nspname = 'myschema'; Right, something like that goes against the idea of having something simple. GRANT ON ALL was meant to be simple tool for beginners not swiss knife for mass granting. I don't think all new features have to be targeted at advanced dbas or VLDBs. I really feel like we should be able to take a page from the unix book here and come up with some way to handle wildcards in certain statements, ala chmod. grant select on * to role; grant select on myschema.* to role; grant select on ab* to role; This syntax would be doable although I am not particularly fond of having that "ab*" option. So, I still don't see consensus on these 3 things. Do we want to differentiate views from tables in these commands or not ? Do we want GRANT ON ALL (or GRANT ON * which is mysql style, btw) in SQL form (not functions or client enhancements) at all ? - if we decide that we don't want to have this as SQL statement then I'll drop the effort. And how do we want to filter default acls ? -- Regards Petr Jelinek (PJMODOS) -- 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] "PANIC: cannot make new WAL entries during recovery" in the wild
Heikki Linnakangas writes: > Tom Lane wrote: >> So that confirms my speculation that btree index cleanup is the source >> of the message. We have two basic approaches to dealing with it: >> >> 1. Decide that the check added to XLogInsert is wrong and take it out. >> >> 2. Arrange for some sort of explicit state transition between the >> WAL-reading and cleanup phases of recovery, and make sure XLogInsert >> knows about it. > I'd suggest we temporarily allow XLog insertion by calling > LocalSetXLogInsertAllowed() just before the rm_cleanup() loop, and reset > it with "LocalXLogInsertAllowed = -1" just after the loop. Like we do at > the startup checkpoint. The sanity check still feels very useful to me, > I'd hate to lose it. Yeah, that looks like a sane solution. The disturbing thing is that we didn't catch this sooner. 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] Fixing geometic calculation
On Fri, Aug 7, 2009 at 7:13 PM, Tom Lane wrote: > Sam Mason writes: >> On Fri, Aug 07, 2009 at 12:50:39PM -0400, Tom Lane wrote: >>> No, I'm worried about code that supposes that it can divide by (x - y) >>> after testing that FPeq(x,y) is not true. point_sl() for instance. > >> OK, but I'm still not sure what you're getting at. > > Underflow. x!=y does not imply (x-y) != 0, if x and y are sufficiently > small and close together. The difference could underflow to zero. Actually I don't think subtraction can underflow with IEEE floats but I don't think we want to count on IEEE floats everywhere. Even if we did there's the risk on intel that FPeq() gets called on values which have just been calculated and are still in registers but then get spilled to RAM and lose precision before the division happens. -- greg http://mit.edu/~gsstark/resume.pdf -- 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 Fri, Aug 07, 2009 at 02:13:26PM -0400, Tom Lane wrote: > Sam Mason writes: > > On Fri, Aug 07, 2009 at 12:50:39PM -0400, Tom Lane wrote: > >> No, I'm worried about code that supposes that it can divide by (x - y) > >> after testing that FPeq(x,y) is not true. point_sl() for instance. > > > OK, but I'm still not sure what you're getting at. > > Underflow. x!=y does not imply (x-y) != 0, if x and y are sufficiently > small and close together. The difference could underflow to zero. I've just realized why this discussion hasn't been making any sense. I thought you were talking about correctness of the code with EPSILON still there and not about what would happen if EPSILON was removed. Thanks for the patience. If EPSILON is indeed removed then yes, this will become a problem and the easiest fix would seem to be to calculate the difference first and test it explicitly. The "error accumulation" comment also makes sense now! Does anyone know the original use case for using the EPSILON (need some shorthand for that, a mail client that supports Unicode?) based comparisons so liberally? It only makes sense to me if they're done right at the end of all the calculations, not all the way though. What defines the "end" seems up to the user as well, or am I missing something. -- Sam http://samason.me.uk/ -- 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] "PANIC: cannot make new WAL entries during recovery" in the wild
Tom Lane wrote: > Alvaro Herrera writes: >> Today we got a report in the spanish list about the message in $subject. >> The server is 8.4 running on Windows. > > I accidentally managed to reproduce this in HEAD just now, by kill -9'ing > a backend that was in the midst of a COPY IN operation (I was trying to > reproduce Neil Best's unrelated issue...) The server log is You're lucky. I once tried to trigger the rm_cleanup() code with repeated "killall -9 postmaster", but failed. IIRC I just put an abort() at the right place in the end. > So that confirms my speculation that btree index cleanup is the source > of the message. We have two basic approaches to dealing with it: > > 1. Decide that the check added to XLogInsert is wrong and take it out. > > 2. Arrange for some sort of explicit state transition between the > WAL-reading and cleanup phases of recovery, and make sure XLogInsert > knows about it. I'd suggest we temporarily allow XLog insertion by calling LocalSetXLogInsertAllowed() just before the rm_cleanup() loop, and reset it with "LocalXLogInsertAllowed = -1" just after the loop. Like we do at the startup checkpoint. The sanity check still feels very useful to me, I'd hate to lose it. -- 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] Fixing geometic calculation
Sam Mason writes: > On Fri, Aug 07, 2009 at 12:50:39PM -0400, Tom Lane wrote: >> No, I'm worried about code that supposes that it can divide by (x - y) >> after testing that FPeq(x,y) is not true. point_sl() for instance. > OK, but I'm still not sure what you're getting at. Underflow. x!=y does not imply (x-y) != 0, if x and y are sufficiently small and close together. The difference could underflow to zero. 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] "PANIC: cannot make new WAL entries during recovery" in the wild
Alvaro Herrera writes: > Today we got a report in the spanish list about the message in $subject. > The server is 8.4 running on Windows. I accidentally managed to reproduce this in HEAD just now, by kill -9'ing a backend that was in the midst of a COPY IN operation (I was trying to reproduce Neil Best's unrelated issue...) The server log is LOG: server process (PID 23846) was terminated by signal 9 LOG: terminating any other active server processes LOG: all server processes terminated; reinitializing LOG: database system was interrupted; last known up at 2009-08-07 11:27:36 EDT LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at 0/1B9D7790 LOG: unexpected pageaddr 0/1532E000 in log file 0, segment 28, offset 3334144 LOG: redo done at 0/1C32D200 PANIC: cannot make new WAL entries during recovery LOG: startup process (PID 23883) was terminated by signal 6 LOG: aborting startup due to startup process failure and the stack trace of the panic'd startup process looks like #4 0x4b6e20 in errfinish (dummy=1) at elog.c:503 #5 0x4b86a0 in elog_finish (elevel=1073803952, fmt=0x7b0394b0 "") at elog.c:1142 #6 0x1f722c in XLogInsert (rmid=11 '\013', info=114 'r', rdata=0xc004d07c) at xlog.c:555 #7 0x1df290 in _bt_insertonpg (rel=0x4006cf28, buf=70, stack=0x3, itup=0x4006d150, newitemoff=38, split_only_page=0) at nbtinsert.c:833 #8 0x1e0898 in _bt_insert_parent (rel=0x4006cf28, buf=304, rbuf=854, stack=0x7b03b9d8, is_root=0, is_only=0) at nbtinsert.c:1627 #9 0x1ef098 in btree_xlog_cleanup () at nbtxlog.c:927 #10 0x201c44 in StartupXLOG () at xlog.c:5767 #11 0x206134 in StartupProcessMain () at xlog.c:8034 #12 0x228d0c in AuxiliaryProcessMain (argc=2, argv=0x7b03b6d8) at bootstrap.c:433 #13 0x39bb68 in StartChildProcess (type=StartupProcess) at postmaster.c:4243 So that confirms my speculation that btree index cleanup is the source of the message. We have two basic approaches to dealing with it: 1. Decide that the check added to XLogInsert is wrong and take it out. 2. Arrange for some sort of explicit state transition between the WAL-reading and cleanup phases of recovery, and make sure XLogInsert knows about it. 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
Re: [HACKERS] Fixing geometic calculation
On Fri, Aug 07, 2009 at 12:50:39PM -0400, Tom Lane wrote: > Sam Mason writes: > > Sorry, I'm struggling to parse that. I think it's all the double > > negatives. Are you saying that HYPOT() should really return zero when > > it's currently giving back would be FPzero? > > No, I'm worried about code that supposes that it can divide by (x - y) > after testing that FPeq(x,y) is not true. point_sl() for instance. OK, but I'm still not sure what you're getting at. If it's infinities and NaNs then they shouldn't matter and will be taken care of by the normal FP rules anyway. > We could perhaps fix those specific issues by testing the difference > explicitly instead of doing it like that. But there's still the overall > problem of error accumulation ... Errors will accumulate whatever happens, that's why things like interval arithmetic exist that usefully track those errors and why I said testing EPSILON isn't a useful. -- Sam http://samason.me.uk/ -- 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] [GENERAL] \copy: unexpected response (4)
I wrote: > Hmm, so it looks like the connection dropped and libpq failed to > recognize that, or maybe libpq was okay but psql needs to check a bit > more carefully here. I'll take a look. I could not reproduce this problem in testing, but after eyeballing the code awhile I have a theory. It looks like it is possible for PQputCopyEnd to fail and leave the PGconn in COPY_IN state, but this only happens (1) if the output buffer contained at least 8K already, causing pqSendSome to be invoked from pqPutMsgEnd, and (2) pqSendSome returned failure. In that situation the loop in copy.c becomes infinite, since there's no mechanism for getting out of COPY_IN state. This case would be relatively difficult to trigger, but it seems to fit all the facts, if we assume that the connection had failed for some reason just at that point. BTW, the "SSL renegotiation failure" bit suggests that it could have been an OpenSSL bug not a real network lossage, so you might want to see how up-to-date your openssl libraries are. Anyway, it seems to me that the most appropriate fix is to add some code to that loop, along the lines of /* * Make sure we have pumped libpq dry of results; else it may still be in * ASYNC_BUSY state, leading to false readings in, eg, get_prompt(). */ while ((result = PQgetResult(pset.db)) != NULL) { success = false; psql_error("\\copy: unexpected response (%d)\n", PQresultStatus(result)); + /* if still in COPY IN state, try to get out of it */ + if (PQresultStatus(result) == PGRES_COPY_IN) + PQputCopyEnd(conn, _("trying to exit copy mode")); PQclear(result); } This would cover this particular case and perhaps others as well. 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] mixed, named notation support
On Fri, Aug 7, 2009 at 12:04 PM, Tom Lane wrote: > There is definitely not enough evidence here to justify breaking > existing applications, which is what introducing => would do. > When and if there's a ratified standard using =>, it'll be time > to break stuff. In the meantime we can do something with AS and > be reasonably certain we haven't painted ourselves into a corner. I wasn't proposing introducing => ; I thought we might want to hold off on doing anything at all. But I may be the only one, so never mind. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 2nd Call for papers, PostgreSQL Conference
Hey folks, West is upon us shortly. Get those talks in: http://www.postgresqlconference.org/2009/west Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] slow commits with heavy temp table usage in 8.4.0
Tom Lane wrote: > If you roll back a truncate, do you get the expected state? I did a number of variations on the test below, with and without "on drop commit", and similar tests where the "create table" is done before the "begin". After the checkpoint, the number of files in the database directory always returned to the value before the "begin" (210 in this case). Everything behaved as expected. test=# begin; BEGIN test=# create temp table t1 (x integer) ; CREATE TABLE test=# insert into t1 select s from generate_series(1,1000) s ; INSERT 0 1000 test=# select count(*) from t1 ; 1000 test=# savepoint s1; SAVEPOINT test=# truncate t1; TRUNCATE TABLE test=# select count(*) from t1 ; 0 test=# insert into t1 select s from generate_series(1,11000) s ; INSERT 0 1001 test=# select count(*) from t1 ; 1001 test=# rollback to savepoint s1 ; ROLLBACK test=# select count(*) from t1 ; 1000 test=# commit ; COMMIT test=# select count(*) from t1 ; 1000 test=# checkpoint; CHECKPOINT test=# > How about after a database crash? Repeating the same test as above, after the second insert, I did "killall -9 postgres". Restarting generated the expected messages in the log: 2009-08-07 13:09:56 EDT LOG: database system was interrupted; last known up at 2009-08-07 13:06:01 EDT 2009-08-07 13:09:56 EDT LOG: database system was not properly shut down; automatic recovery in progress 2009-08-07 13:09:56 EDT LOG: redo starts at 0/1F8D6D0 2009-08-07 13:09:56 EDT LOG: invalid magic number in log file 0, segment 1, offset 16392192 2009-08-07 13:09:56 EDT LOG: redo done at 0/1F9F3B8 2009-08-07 13:09:56 EDT LOG: autovacuum launcher started 2009-08-07 13:09:56 EDT LOG: database system is ready to accept connections However, the DB directory now has 214 files (up from 210); I have no idea whether this means anything or not. Repeating the previous tests gives expected results. -- todd -- 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] Table and Index compression
Pierre, > I added a field in PageHeader which contains : > - 0 to indicate a non-compressed page > - length of compressed data if compressed > > If compression gains nothing (ie gains less than 4K), the page is > stored raw. > > It seems that only pages having a PageHeader are handled by md.c, so > it should work (am I mistaken ?) Well, there's the issue of upgradability; this would require us to have an incompatible upgrade of on-disk formats. So we don't want to go further down this route unless we're sure it's worthwhile. -- 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] Fixing geometic calculation
Sam Mason writes: > Sorry, I'm struggling to parse that. I think it's all the double > negatives. Are you saying that HYPOT() should really return zero when > it's currently giving back would be FPzero? No, I'm worried about code that supposes that it can divide by (x - y) after testing that FPeq(x,y) is not true. point_sl() for instance. We could perhaps fix those specific issues by testing the difference explicitly instead of doing it like that. But there's still the overall problem of error accumulation ... 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] slow commits with heavy temp table usage in 8.4.0
On Fri, Aug 7, 2009 at 10:10, Todd A. Cook wrote: > Alex Hunsaker wrote: >> With double the number of files maybe something >> >> simple like turning on dir_index if you are ext3 will help? > > Thanks for the tip. Doing "tune2fs -O +dir_index" didn't seem to make > a difference, which is kinda expected for an existing directory. When > I get a chance, I'll try to recreate the filesystem. e2fsck -D should also do the trick. > -- todd > -- 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 Fri, Aug 07, 2009 at 11:40:58AM -0400, Tom Lane wrote: > Sam Mason writes: > > I would agree with Paul that EPSILON is a hack and probably should be > > removed. > > It's a hack but it's dealing with an extremely real problem, namely > the built-in inaccuracy of floating-point arithmetic. You can't just > close your eyes to that and hope that everything will be okay. Yes, I know it's a fiddle to get right. Choosing the right primitives is generally the most difficult part. > A quick look through the geometry sources says that we might not be > critically dependent on anything except the assumption that two values > that aren't FPeq() will have a nonzero difference. Sorry, I'm struggling to parse that. I think it's all the double negatives. Are you saying that HYPOT() should really return zero when it's currently giving back would be FPzero? > (If you think this > is a tautology, you don't know enough about floating point arithmetic > to be qualified to offer an opinion here...) I think I have a reasonable idea about FP arithmetic, I have had to worry about rounding modes and such like before. Never tried to write a FP emulator though so I'm sure I could know more. -- Sam http://samason.me.uk/ -- 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] slow commits with heavy temp table usage in 8.4.0
"Todd A. Cook" writes: > Tom Lane wrote: >> The attached prototype patch does this >> and seems to fix the speed problem nicely. It's not tremendously >> well tested, but perhaps you'd like to test? Should work in 8.4. > With the patch applied, the test only took 35 seconds, and the commit > was practically instant (30ms). I know it's faster, what I meant by testing was does it *work*. If you roll back a truncate, do you get the expected state? How about after a database crash? > Is there any chance that it will be backpatched to 8.4? Not a lot. 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] Review: Revise parallel pg_restore's scheduling heuristic
On Fri, Aug 07, 2009 at 10:39:19AM -0500, Kevin Grittner wrote: > Sam Mason wrote: > > Yes, all that sounds as though you've got it. > > Thanks. I read through it carefully a few times, but I was still only > 80% confident that I had it more-or-less right. ;-) And which method did you use to determine that you're 80% confident? :) -- Sam http://samason.me.uk/ -- 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] slow commits with heavy temp table usage in 8.4.0
Alex Hunsaker wrote: FYI, on my 8.2.13 system, the test created 30001 files which were all deleted during the commit. Â On my 8.4.0 system, the test created 60001 files, of which 3 were deleted at commit and 30001 disappeared later (presumably during a checkpoint?). Smells like fsm? Yes, that was it. 3 of the filenames ended with "_fsm". > With double the number of files maybe something simple like turning on dir_index if you are ext3 will help? Thanks for the tip. Doing "tune2fs -O +dir_index" didn't seem to make a difference, which is kinda expected for an existing directory. When I get a chance, I'll try to recreate the filesystem. -- todd -- 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 wrote: > Michael Meskes írta: > > Did you take care of the copyright/licensing issue with sqlda? > > I added notice about the PostgreSQL license. Is it ok? > Or should I resend without indicating the authors? I think we're normally OK with mentioning the authors, i.e. "Author: such and such", but the (C) line should attribute copyright to UCB/PGDG. Michael is free to dictate something else for ECPG of course ... -- 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] slow commits with heavy temp table usage in 8.4.0
Tom Lane wrote: Actually, this is easier than I thought, because there is already bookkeeping being done that (in effect) tracks whether a table has already been truncated in the current transaction. So we can rely on that, and with only a very few lines of code added, ensure that a situation like this does only one full-scale transaction-safe truncation per transaction. The attached prototype patch does this and seems to fix the speed problem nicely. It's not tremendously well tested, but perhaps you'd like to test? Should work in 8.4. I downloaded the 8.4 source, built it unmodified, created a new cluster, and ran the test in an empty DB there. Function execution took about 230 seconds, and commit took about 6 seconds. With the patch applied, the test only took 35 seconds, and the commit was practically instant (30ms). I monitored the database directory, and the test execution only created 2 files (down from 6). Thanks for the patch; it looks great. :) Is there any chance that it will be backpatched to 8.4? -- todd -- 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] mixed, named notation support
Robert Haas writes: > Stepping back a bit, are we sure this is a feature we even want to > support? It was already pointed out in the thread on "Parser's hook > based on funccall" that SQL:201x may standardize => for this purpose. Absolutely no evidence has been presented that the SQL committee is even going to standardize something in this area, much less that they are likely to adopt => as the syntax. I think it would be completely out of character for them to do that --- their entire body of work over the past twenty years has been reflective of a COBOL-ish approach to syntax, ie use keywords not punctuation. Look at the built-in functions like SUBSTRING, POSITION, TREAT; or what they did to introduce window functions. There is definitely not enough evidence here to justify breaking existing applications, which is what introducing => would do. When and if there's a ratified standard using =>, it'll be time to break stuff. In the meantime we can do something with AS and be reasonably certain we haven't painted ourselves into a corner. 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
On Fri, Aug 7, 2009 at 11:35 AM, Bruce Momjian wrote: > Peter Eisentraut wrote: >> On Monday 03 August 2009 21:07:00 David Fetter wrote: >> > We require that people supply docs with their changes, and it is >> > totally unreasonable to let them send in catalog changes which do not >> > include need migration changes. That's how it works in every other >> > RDBMS outfit that has changes on disk, and we do not need to be the >> > exception. >> >> Well, blocker number one for that is that pg_migrator is not even in the >> PostgreSQL CVS repository, but is more like an endorsed third-party product. > > I wouldn't say pg_migrator is "endorsed". It is on pgfoundry and was > mentioned in the press release, but it isn't mentioned in our > documentation about upgrading, it wasn't mentioned in the release notes, > and it isn't mentioned on our web site, except as a news item. > > I believe this is because of concerns about pg_migrator's "experimental" > nature. I think so. And also because it has a fair number of documented restrictions. Hopefully we'll be able to remove some of those in a future release. ...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 Fri, Aug 07, 2009 at 10:29:27AM -0500, Kenneth Marshall wrote: > On Fri, Aug 07, 2009 at 04:16:56PM +0100, Sam Mason wrote: > > points in PG [..] don't > > arbitrarily go jumping off millions of miles away or being annihilated > > by their anti-particle just because it's possible. > It was definitely a tongue-in-cheek response since QT is not really > a topic for this mailing list. Yup, I know. Hence my somewhat over the top examples. > > I would agree with Paul that EPSILON is a hack and probably should be > > removed. However it will cause user visible changes so it's not quite > > as simple as that to change. I don't have anything really very useful > > to add apart from saying that maybe the default should be the other way > > around? > > However, removing EPSILON completely > is not a good idea for the exact reason it was included originally. Hum, I think it's good in some limited situations but not by default. I personally think that PG should be exposing rawer access here, mainly because FP math is hard to get right and the more we fiddle trying to make it easier to appear to do the right thing in the common case the more general cases become impossible. It's similar to the auto TEXT casting thing that was changed in 8.3, but at least you get a nice error when things aren't automatically cast to TEXT. There are also much more reliable ways of solving the inaccuracies than what's done now by just relying on a simple test, interval arithmetic is my favorite at the moment but it is slower and can make thins more complicated. -- Sam http://samason.me.uk/ -- 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] Filtering dictionaries support and unaccent dictionary
On Thu, Aug 6, 2009 at 10:46 PM, Robert Haas wrote: > > I am not sure whether this has been formally reviewed by anyone yet; > do we think it's "Ready for Committer"? > i was trying to make some review of this but besides that it compiles fine and passes regression tests doesn't know how to test it -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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
Sam Mason writes: > I would agree with Paul that EPSILON is a hack and probably should be > removed. It's a hack but it's dealing with an extremely real problem, namely the built-in inaccuracy of floating-point arithmetic. You can't just close your eyes to that and hope that everything will be okay. A quick look through the geometry sources says that we might not be critically dependent on anything except the assumption that two values that aren't FPeq() will have a nonzero difference. (If you think this is a tautology, you don't know enough about floating point arithmetic to be qualified to offer an opinion here...) We might be able to base a tighter comparison procedure on that rule. It would take a lot more investigation to be sure though. 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] Review: Revise parallel pg_restore's scheduling heuristic
Sam Mason wrote: > Yes, all that sounds as though you've got it. Thanks. I read through it carefully a few times, but I was still only 80% confident that I had it more-or-less right. ;-) That does seem like a good test, with the advantage of being relatively easy to calculate. Thanks again for suggesting it. -Kevin -- 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
Peter Eisentraut wrote: > On Monday 03 August 2009 21:07:00 David Fetter wrote: > > We require that people supply docs with their changes, and it is > > totally unreasonable to let them send in catalog changes which do not > > include need migration changes. That's how it works in every other > > RDBMS outfit that has changes on disk, and we do not need to be the > > exception. > > Well, blocker number one for that is that pg_migrator is not even in the > PostgreSQL CVS repository, but is more like an endorsed third-party product. I wouldn't say pg_migrator is "endorsed". It is on pgfoundry and was mentioned in the press release, but it isn't mentioned in our documentation about upgrading, it wasn't mentioned in the release notes, and it isn't mentioned on our web site, except as a news item. I believe this is because of concerns about pg_migrator's "experimental" nature. -- 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] Review: Revise parallel pg_restore's scheduling heuristic
On Fri, Aug 07, 2009 at 10:19:20AM -0500, Kevin Grittner wrote: > Sam Mason wrote: > > > What do people do when testing this? I think I'd look to something > > like Student's t-test to check for statistical significance. My > > working would go something like: > > > > I assume the variance is the same because it's being tested on the > > same machine. > > > > samples = 20 > > stddev = 144.26 > > avg1= 4783.13 > > avg2= 4758.46 > > t = 0.54 ((avg1 - avg2) / (stddev * sqrt(2/samples))) > > > > We then have to choose how certain we want to be that they're > > actually different, 90% is a reasonably easy level to hit (i.e. one > > part in ten, with 95% being more commonly quoted). For 20 samples > > we have 19 degrees of freedom--giving us a cut-off[1] of 1.328. > > 0.54 is obviously well below this allowing us to say that there's no > > "statistical significance" between the two samples at a 90% level. > > Thanks for the link; that looks useful. To confirm that I understand > what this has established (or get a bit of help putting in in > perspective), what this says to me, in the least technical jargon I > can muster, is "With this many samples and this degree of standard > deviation, the average difference is not large enough to have a 90% > confidence level that the difference is significant." In fact, > looking at the chart, it isn't enough to reach a 75% confidence level > that the difference is significant. Significance here would seem to > mean that at least the given percentage of the time, picking this many > samples from an infinite set with an average difference that really > was this big or bigger would generate a value for t this big or > bigger. > > Am I close? Yes, all that sounds as though you've got it. Note that running the test more times will tend to reduce the standard deviation a bit as well, so it may well become significant. In this case it's unlikely to affect it much though. > I like to be clear, because it's easy to get confused and take the > above to mean that there's a 90% confidence that there is no actual > significant difference in performance based on that sampling. (Given > Tom's assurance that this version of the patch should have similar > performance to the last, and the samples from the prior patch went the > other direction, I'm convinced there is not a significant difference, > but if I'm going to use the referenced calculations, I want to be > clear how to interpret the results.) All we're saying is that we're less than 90% confident that there's something "significant" going on. All the fiddling with standard deviations and sample sizes is just easiest way (that I know of) that statistics currently gives us of determining this more formally than a hand-wavy "it looks OK to me". Science tells us that humans are liable to say things are OK when they're not, as well as vice versa; statistics gives us a way to work past these limitations in some common and useful situations. -- Sam http://samason.me.uk/ -- 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 Fri, Aug 07, 2009 at 04:16:56PM +0100, Sam Mason wrote: > On Fri, Aug 07, 2009 at 09:49:41AM -0500, Kenneth Marshall wrote: > > On Fri, Aug 07, 2009 at 09:12:34AM -0500, Kenneth Marshall wrote: > > > On Fri, Aug 07, 2009 at 11:29:47PM +1000, Paul Matthews wrote: > > > > We have two points with a finite separation in the x axis. > > > > Postgres thinks they are not the same point, nor one left of the > > > > other, nor to the right. This is clearly a both a physical and > > > > logical impossibility. > > > > Actually, quantum theory will allow this to happen. :) > > I'm not a physicist, but I don't think it does. QM defines the > probability distribution within which the particle will be found. Once > you've actually observed both "points" you will know their physical > relation--you'll also have given them energy them so next time you look > they'll be somewhere else, but the act of observation causes the above > distribution to be collapsed. This sidesteps the whole issue of the > fact that points in PG are defined in euclidean space and do indeed > have a definite location and can be compared at all times---they don't > arbitrarily go jumping off millions of miles away or being annihilated > by their anti-particle just because it's possible. > > I would agree with Paul that EPSILON is a hack and probably should be > removed. However it will cause user visible changes so it's not quite > as simple as that to change. I don't have anything really very useful > to add apart from saying that maybe the default should be the other way > around? > > -- > Sam http://samason.me.uk/ > It was definitely a tongue-in-cheek response since QT is not really a topic for this mailing list. However, removing EPSILON completely is not a good idea for the exact reason it was included originally. Floating point numbers are approximations and since their precision is neccessarily limited this fact must be included in any calculation using them. I do agree that hard-coding it to a value that does not reflect the reality of the calculation is not good. It would be better to have a GUC to allow it to be specified than to have it be zero. Maybe one setting would allow the system to calculate the appropriate value for EPSILON based on the hardward. One way to address the duplicity issue is to define for yourself what it means if a point is both inside and outside, i.e. in this case the point is always defined to be inside or the point is always defined to be outside. Regards, Ken -- 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] Review: Revise parallel pg_restore's scheduling heuristic
Sam Mason wrote: > What do people do when testing this? I think I'd look to something > like Student's t-test to check for statistical significance. My > working would go something like: > > I assume the variance is the same because it's being tested on the > same machine. > > samples = 20 > stddev = 144.26 > avg1= 4783.13 > avg2= 4758.46 > t = 0.54 ((avg1 - avg2) / (stddev * sqrt(2/samples))) > > We then have to choose how certain we want to be that they're > actually different, 90% is a reasonably easy level to hit (i.e. one > part in ten, with 95% being more commonly quoted). For 20 samples > we have 19 degrees of freedom--giving us a cut-off[1] of 1.328. > 0.54 is obviously well below this allowing us to say that there's no > "statistical significance" between the two samples at a 90% level. Thanks for the link; that looks useful. To confirm that I understand what this has established (or get a bit of help putting in in perspective), what this says to me, in the least technical jargon I can muster, is "With this many samples and this degree of standard deviation, the average difference is not large enough to have a 90% confidence level that the difference is significant." In fact, looking at the chart, it isn't enough to reach a 75% confidence level that the difference is significant. Significance here would seem to mean that at least the given percentage of the time, picking this many samples from an infinite set with an average difference that really was this big or bigger would generate a value for t this big or bigger. Am I close? I like to be clear, because it's easy to get confused and take the above to mean that there's a 90% confidence that there is no actual significant difference in performance based on that sampling. (Given Tom's assurance that this version of the patch should have similar performance to the last, and the samples from the prior patch went the other direction, I'm convinced there is not a significant difference, but if I'm going to use the referenced calculations, I want to be clear how to interpret the results.) -Kevin -- 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 Fri, Aug 07, 2009 at 09:49:41AM -0500, Kenneth Marshall wrote: > On Fri, Aug 07, 2009 at 09:12:34AM -0500, Kenneth Marshall wrote: > > On Fri, Aug 07, 2009 at 11:29:47PM +1000, Paul Matthews wrote: > > > We have two points with a finite separation in the x axis. > > > Postgres thinks they are not the same point, nor one left of the > > > other, nor to the right. This is clearly a both a physical and > > > logical impossibility. > > Actually, quantum theory will allow this to happen. :) I'm not a physicist, but I don't think it does. QM defines the probability distribution within which the particle will be found. Once you've actually observed both "points" you will know their physical relation--you'll also have given them energy them so next time you look they'll be somewhere else, but the act of observation causes the above distribution to be collapsed. This sidesteps the whole issue of the fact that points in PG are defined in euclidean space and do indeed have a definite location and can be compared at all times---they don't arbitrarily go jumping off millions of miles away or being annihilated by their anti-particle just because it's possible. I would agree with Paul that EPSILON is a hack and probably should be removed. However it will cause user visible changes so it's not quite as simple as that to change. I don't have anything really very useful to add apart from saying that maybe the default should be the other way around? -- Sam http://samason.me.uk/ -- 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 to remove inconsistency in dependency.c
Hi Description: = Inconsistency in dependency.c file's object_classes array elements and dependency.h file's enum ObjectClass elements. Following elements in object_classes array were missing. ForeignDataWrapperRelationId for OCLASS_FDW ForeignServerRelationId for OCLASS_FOREIGN_SERVER UserMappingRelationId forOCLASS_USER_MAPPING Files Changed: = 1. src/backend/catalog/dependency.c thanks --Aqeel diff -c -r ./src/backend/catalog/dependency.c ./src/backend/catalog/dependency.c *** ../postgresql-8.4.0-original/postgresql-8.4.0/src/backend/catalog/dependency.c 2009-06-11 20:48:54.0 +0600 --- ./src/backend/catalog/dependency.c 2009-08-06 10:58:55.0 +0600 *** *** 143,149 TSConfigRelationId, /* OCLASS_TSCONFIG */ AuthIdRelationId, /* OCLASS_ROLE */ DatabaseRelationId, /* OCLASS_DATABASE */ ! TableSpaceRelationId /* OCLASS_TBLSPACE */ }; --- 143,152 TSConfigRelationId, /* OCLASS_TSCONFIG */ AuthIdRelationId, /* OCLASS_ROLE */ DatabaseRelationId, /* OCLASS_DATABASE */ ! TableSpaceRelationId, /* OCLASS_TBLSPACE */ ! ForeignDataWrapperRelationId, /* OCLASS_FDW */ ! ForeignServerRelationId, /* OCLASS_FOREIGN_SERVER */ ! UserMappingRelationId /* OCLASS_USER_MAPPING */ }; -- 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 to remove inconsistency in dependency.c
Muhammad Aqeel writes: > Inconsistency in dependency.c file's object_classes array elements and > dependency.h file's enum ObjectClass elements. Following elements in > object_classes array were missing. Good catch --- seems to have been some sloppiness in the SQL/MED patch. Will fix, thanks. 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] Fixing geometic calculation
On Fri, Aug 07, 2009 at 09:12:34AM -0500, Kenneth Marshall wrote: > On Fri, Aug 07, 2009 at 11:29:47PM +1000, Paul Matthews wrote: > > Let us consider the ordering of real numbers in postgres. As you can see > > from > > the results below it has clearly returned the correct results. > > > > select( 1. = 1.0002 ); => f > > select( 1. < 1.0002 ); => t > > select( 1. > 1.0002 ); => f > > > > Imagine the situation however where postgres returned the following > > values to > > simple numerical inequalities. In such a case postgresql would be clearly > > defective and unfit for purpose. > > > > select( 1.00 = 1.01 ); => f > > select( 1.00 < 1.01 ); => f > > select( 1.00 > 1.01 ); => f > > > > If such a situation is unacceptable for the real number line, then in > > what way > > can it be acceptable for the real number plain. > > > > select( point(1.0,0) <> point(1.1,0) ); => f > > select( point(1.0,0) << point(1.1,0) ); => f > > select( point(1.0,0) >> point(1.1,0) ); => f > > select( point(1.0,0) <-> point(1.1,0) ); => 1.000655e-05 > > > > We have two points with a finite separation in the x axis. Postgres > > thinks they > > are not the same point, nor one left of the other, nor to the right. This is > > clearly a both a physical and logical impossibility. Actually, quantum theory will allow this to happen. :) > > > > The cause of this is the ill conceived FP* macros. They seem represent a > > solution to a problem that simply does not exist. > > > > The first effect of these macros is to reduce the accuracy of all geometric > > comparisons from double precision, to less than single precision. The > > following > > program correctly prints the correct answer. Whereas as we have seen above, > > postgres falls in a heap. > > > > int main() { > > float f = 1.0; > > float g = 1.1; > > if( f==g ) { printf( "f=g\n" ); } > > if( f > if( f>g ) { printf( "f>g\n" ); } > > return 0; > > } > > > > The second effect is to take operations that would of worked correctly > > even in > > single precision, and to cause them to produce nonsensical result. For > > example > > points that can be both inside and outside a polygon at the same time. > > > > Simple analysis of the postgres source code shows that the only places > > where the > > FPzero, FPeq, FPne, FPlt, FPle FPgt and FPge macros are defined and used > > are in > > the src/backend/utils/adt/geo_ops.c and src/include/utils/geo_decls.h files. > > > > What is the justification for these macros? Why do they only affect > > geometric > > calculations, and not all numeric calculations? Why should these macro's > > not be > > abandoned? > > > > Does anyone any any objections to me: > > 1) removing these macros, or at least disabling EPSILON by default. > > 2) adding in the obviously missing operators (ie: box @> point) > > > > Hi Paul, > > Floating point calculations always have a bit of inaccuracy > because at the very minimum some values do not have exact > floating point representations and the results can be > implimentation dependent. I think disabling EPLSILON by > default is a bad idea. In my work with numeric methods, > we actually calculated EPSILON for the system we where using > at runtime. Maybe postgresql could do the same on startup. > > Regards, > Ken -- 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] include/commands/version.h is not used
Magnus Hagander writes: > On Fri, Aug 7, 2009 at 05:04, Itagaki > Takahiro wrote: >> I found include/commands/version.h is empty and not included from any files. >> What is the purpose of the file? >> From what I can tell, it hasn't been used since back in the Berkeley > days, but it did contain some function headers that were removed back > in 1997. I think we can just get rid of it... +1. Looking at the postgres v4r2 sources, it was for their "version" facility, which evidently got ripped out later. The description in the create_version man page is interesting: This command creates a version class .IR classname1 which is related to its parent class, .IR classname2 . Initially, .IR classname1 has the same contents as .IR classname2. As updates to .IR classname1 occur, however, the content of .IR classname1 diverges from .IR classname2. On the other hand, any updates to .IR classname2 show transparently through to .IR classname1 , unless the instance in question has already been updated in .IR classname1 . .PP If the optional .IR abstime clause is specified, then the version is constructed relative to a .BR snapshot of .IR classname2 as of the time specified. .PP \*(PG uses the query rewrite rule system to ensure that .IR classname1 is differentially encoded relative to .IR classname2. Moreover, .IR classname1 is automatically constructed to have the same indexes as .IR classname2 . It is legal to cascade versions arbitrarily, so a tree of versions can ultimately result. The algorithms that control versions are explained in [ONG90]. So that appears to depend on time travel, and also on a version of query rewrite rules that we no longer have. Dead as a doornail :-( 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] Table and Index compression
On Fri, Aug 07, 2009 at 04:17:18PM +0200, Pierre Frrrdddric Caillaud wrote: > I'm answering my own question : at the beginning of the run, postgres > creates a 800MB temporary file, then it fills the table, then deletes the > temp file. > Is this because I use generate_series to fill the test table ? Doh, yes. A function's result is written to temp location first and then read back again once the function returns success. You'll have more luck if you do: SELECT now() + '1 sec'::INTERVAL, (1+random()*8), random()*1000,n+random()*1, n+random()*1000, n FROM ( SELECT generate_series( 1, 6000 )) x(n); -- Sam http://samason.me.uk/ -- 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] Re: [Pg-migrator-general] Composite types break pg_migrated tables
Bruce Momjian writes: > Peter Eisentraut wrote: >> That might be a bit excessive. As I understand it, arrays of built-in types >> (e.g., int[]) should work fine. I suspect the majority of uses of arrays >> will >> be with built-in types, so allowing that would help a significant portion of >> installations. > Agreed. I realized that last night, and have modified pg_migrator to > test FirstNormalObjectId. That's really the wrong thing. It's safe to assume OIDs below 1 are portable across versions, because for them not to be would require someone to have changed a hand assignment. However, OIDs between 1 and 16K are assigned on-the-fly by initdb, and those are *not* likely to be portable across versions. As an example, the rowtype for pg_statistic has slightly different OIDs in 8.3 and 8.4. So if you allow someone to port a database that is using a system catalog's rowtype, it will fail. Admittedly that's not a real likely scenario, but if you're going to have a check it should be accurate. 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 support for struct in INTO list
Michael Meskes írta: > On Fri, Aug 07, 2009 at 11:48:33AM +0200, Boszormenyi Zoltan wrote: > >> Which isn't exactly a good programming habit. >> > > I couldn't agree more. > :-) >> In the above code local, in-scope variables are also replaced >> with ECPG_informix_set_var() and _get_var() calls. >> Totally unnecessary, or totally necessary even in non-compatible >> mode, depending on which leg I stand on. If you move the >> struct/union unrolling into adjust_informix() and handle arrays, >> ECPGdump_a_struct() won't be needed then. >> > > Yeah, right, and you also add this hack to all applications. No. > What do you mean? In the meantime, I recalled my original idea, the patch you have already seen, where an unrolling was introduced in "ecpg_into:" rule with a new add_struct_to_head() function. "ecpg_into:" is a good central place where this unrolling can be done, so: - ECPGdump_a_struct() won't be needed, and - adjust_informix() doesn't encounter structs or unions. So, struct unrolling would still be done at only one place and one problem goes away. About the arrays, I have to think about more. >> Because that example contradicts all sensible programming habits. >> (Well, what is "sensible" is different between people, so don't take it >> personal.) >> > > Hey, don't blame me! Informix uses this feature to some extend, this is why it > got implemented. If you look into the source code you will see this: > >* This breaks standard and leads to some very dangerous programming. > * Since they do, we have to work around and accept their syntax as > well. > * But we will do so ONLY in Informix mode. > I didn't want to blame you, I just wanted to say that from my experience, it's more common that these are the separation blocks: 1. memory allocation 1a. DECLARE 2. OPEN/FETCH 2a. CLOSE 3. memory freeing Point "1a" can be grouped with either "1" or "2", similarly point "2a" can be grouped with either "2" or "3". "Grouped" means that they are in the same function, at the same visibility level. It was very alien to me seeing that only OPEN was moved out to another function in the mentioned regression test example. I think that the DECLARE doing this adjust_informix() call (which should be called adjust_out_of_scope_vars() ;-) ) shouldn't be Informix-specific at all. The above separation is natural (again, a very subjective POV), and should be supported in native mode, 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] pg_ctl stop -m fast after -m smart
Fujii Masao writes: > Takahiro wrote: >> Can we change the behavior that "fast" overwrites "smart" mode? > +1. This behavior was supported in 8.2 or before, but broken in 8.3. > Here is the patch. This should be backported to 8.3 and 8.4. I think this was my fault :-(. Thanks for the fix. 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
[HACKERS] Re: [Pg-migrator-general] Composite types break pg_migrated tables
Greg Stark wrote: > On Fri, Aug 7, 2009 at 1:56 AM, Bruce Momjian wrote: > > ? ? ? ? ? ? ? ?o ?data type 'name' and is not the first column > > > > What was that about? We changed the alignment of the 'name' column: /* * v8_3_check_for_name_data_type_usage() * * alignment for the 'name' data type changed to 'char' in 8.4; * checks tables and indexes */ -- 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] Table and Index compression
On Fri, 07 Aug 2009 15:42:35 +0200, Kevin Grittner wrote: Pierre Frédéric Caillaud wrote: tablespace is a RAID5 of 3 drives, xlog in on a RAID1 of 2 drives, but it does it too if I put the tablespace and data on the same volume. it starts out relatively fast : si sobibo in csus sy id wa 00 0 43680 2796 19162 42 18 37 3 00 0 45515 3165 20652 44 17 35 4 00 0 43130 3046 21991 43 17 38 2 then here it starts to slow down : check "bo" output 00 181 24439 577 3541 31 6 40 23 00 176 17258 292 1324 31 4 43 22 00 0 18626 162 693 35 3 49 12 00 1 21554 235 1362 31 5 50 14 00 0 19177 324 2053 35 4 50 12 00 0 19208 206 1155 36 4 48 12 00 1 20740 215 1117 33 4 50 13 00 0 20154 258 1100 32 4 50 14 00 0 20355 316 2056 34 5 49 12 ... and it stays like this until the end of the INSERT... I don't know if this is it, but we tend to see outrageously high performance at the start of a benchmark because of the battery-backed cache in the RAID controller. Every write comes back immediately after copying the data to RAM. After a while the cache gets filled and you settle down to a steady state. If it's not BBU with write-back enabled, perhaps you have drives that lie about write completion? -Kevin I'm answering my own question : at the beginning of the run, postgres creates a 800MB temporary file, then it fills the table, then deletes the temp file. Is this because I use generate_series to fill the test table ? -- 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 Fri, Aug 07, 2009 at 11:29:47PM +1000, Paul Matthews wrote: > Let us consider the ordering of real numbers in postgres. As you can see > from > the results below it has clearly returned the correct results. > > select( 1. = 1.0002 ); => f > select( 1. < 1.0002 ); => t > select( 1. > 1.0002 ); => f > > Imagine the situation however where postgres returned the following > values to > simple numerical inequalities. In such a case postgresql would be clearly > defective and unfit for purpose. > > select( 1.00 = 1.01 ); => f > select( 1.00 < 1.01 ); => f > select( 1.00 > 1.01 ); => f > > If such a situation is unacceptable for the real number line, then in > what way > can it be acceptable for the real number plain. > > select( point(1.0,0) <> point(1.1,0) ); => f > select( point(1.0,0) << point(1.1,0) ); => f > select( point(1.0,0) >> point(1.1,0) ); => f > select( point(1.0,0) <-> point(1.1,0) ); => 1.000655e-05 > > We have two points with a finite separation in the x axis. Postgres > thinks they > are not the same point, nor one left of the other, nor to the right. This is > clearly a both a physical and logical impossibility. > > The cause of this is the ill conceived FP* macros. They seem represent a > solution to a problem that simply does not exist. > > The first effect of these macros is to reduce the accuracy of all geometric > comparisons from double precision, to less than single precision. The > following > program correctly prints the correct answer. Whereas as we have seen above, > postgres falls in a heap. > > int main() { > float f = 1.0; > float g = 1.1; > if( f==g ) { printf( "f=g\n" ); } > if( f if( f>g ) { printf( "f>g\n" ); } > return 0; > } > > The second effect is to take operations that would of worked correctly > even in > single precision, and to cause them to produce nonsensical result. For > example > points that can be both inside and outside a polygon at the same time. > > Simple analysis of the postgres source code shows that the only places > where the > FPzero, FPeq, FPne, FPlt, FPle FPgt and FPge macros are defined and used > are in > the src/backend/utils/adt/geo_ops.c and src/include/utils/geo_decls.h files. > > What is the justification for these macros? Why do they only affect > geometric > calculations, and not all numeric calculations? Why should these macro's > not be > abandoned? > > Does anyone any any objections to me: > 1) removing these macros, or at least disabling EPSILON by default. > 2) adding in the obviously missing operators (ie: box @> point) > Hi Paul, Floating point calculations always have a bit of inaccuracy because at the very minimum some values do not have exact floating point representations and the results can be implimentation dependent. I think disabling EPLSILON by default is a bad idea. In my work with numeric methods, we actually calculated EPSILON for the system we where using at runtime. Maybe postgresql could do the same on startup. Regards, Ken -- 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
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 ... 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] Re: [Pg-migrator-general] Composite types break pg_migrated tables
Dne 6.08.09 04:29, Tom Lane napsal(a): Andrew Dunstan writes: preventing a clash might be fairly difficult. Yeah, I was just thinking about that. The easiest way to avoid collisions would be to make pg_dump (in --binary-upgrade mode) responsible for being sure that *every* new pg_type and pg_class row OID matches what it was in the old DB. We could stop doing that once we have all the user tables in place --- I don't believe it's necessary to preserve the OIDs of user indexes. But we need to preserve toast table OIDs, and toast table index OIDs too if those are created at the same time they are now (else we risk one of them colliding with a toast table OID we want to create later). Oh, and pg_enum rows too. It seems doable, but we're certainly not going to back-patch any such thing into 8.4 ... Another way is to use direct catalog update which I presented on PgCon. I think it should be easy to finish it (2-3weeks) for 8.4 - needs small extension of bootstrap. And of course testing, testing ... Also to remove oid in catalog and replace it with standard column (type can be oid) should make things easier. But it is for 8.5. I will send a code on Monday for people who wants to look on it. Zdenek -- 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 01:05:33PM +0200, Boszormenyi Zoltan wrote: > >> Hey, thanks. Did you only commit this, or all of >> those in the patchset? Exluding the nonfix for >> the struct problem of course. >> > > So far only this. So there are three more left to go. :-) > Okay :-) > Did you take care of the copyright/licensing issue with sqlda? > I added notice about the PostgreSQL license. Is it ok? Or should I resend without indicating the authors? Thanks, 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
[HACKERS] ALTER TABLE SET STATISTICS requires AccessExclusiveLock
Is there a good reason for $subject, other than that the code is entangled with other ALTER TABLE code? The new SET DISTINCT might be equally affected. -- 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] Table and Index compression
Pierre Frédéric Caillaud wrote: > tablespace is a RAID5 of 3 drives, xlog in on a RAID1 of 2 drives, > but it does it too if I put the tablespace and data on the same > volume. > it starts out relatively fast : > > si sobibo in csus sy id wa > 00 0 43680 2796 19162 42 18 37 3 > 00 0 45515 3165 20652 44 17 35 4 > 00 0 43130 3046 21991 43 17 38 2 > > then here it starts to slow down : check "bo" output > > 00 181 24439 577 3541 31 6 40 23 > 00 176 17258 292 1324 31 4 43 22 > 00 0 18626 162 693 35 3 49 12 > 00 1 21554 235 1362 31 5 50 14 > 00 0 19177 324 2053 35 4 50 12 > 00 0 19208 206 1155 36 4 48 12 > 00 1 20740 215 1117 33 4 50 13 > 00 0 20154 258 1100 32 4 50 14 > 00 0 20355 316 2056 34 5 49 12 > > ... and it stays like this until the end of the INSERT... I don't know if this is it, but we tend to see outrageously high performance at the start of a benchmark because of the battery-backed cache in the RAID controller. Every write comes back immediately after copying the data to RAM. After a while the cache gets filled and you settle down to a steady state. If it's not BBU with write-back enabled, perhaps you have drives that lie about write completion? -Kevin -- 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] compilation with libeditpreferred is broken
Dne 7.08.09 00:13, Tom Lane napsal(a): Zdenek Kotala writes: It seems to me that editline never distributed history.h file and HAVE_EDITLINE_HISTORY_H is nonsense. But I'm not sure. I wouldn't count on that, in part because there are so many versions of editline. On an OS X machine I see $ ls -l /usr/include/*line* /usr/include/editline: total 16 -rw-r--r-- 1 root wheel 6882 Feb 19 2008 readline.h /usr/include/readline: total 16 lrwxr-xr-x 1 root wheel 22 Jul 23 11:31 history.h@ -> ../editline/readline.h lrwxr-xr-x 1 root wheel 22 Jul 23 11:31 readline.h@ -> ../editline/readline.h It is only hack for application which wants to have readline and they don't detects libedit. Finally it is like: #include #include I little bit searching on Internet and it seems that when edit/history.h exists it is link to editline/readline.h. (See e.g. http://sysinf0.klabs.be/usr/include/editline/history.h?dist=;arch= ) This link is created by packager, because when you compiling libedit, make install does not create it. By my opinion HAVE_EDITLINE_HISTORY_H is overhead, but we can keep it. Zdenek -- 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] Table and Index compression
On Fri, Aug 07, 2009 at 03:29:44PM +0200, Pierre Frrrdddric Caillaud wrote: > vmstat output : Sorry, I don't know enough of PGs internals to suggest anything here, but iostat may give you more details as to what's going on. -- Sam http://samason.me.uk/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Fixing geometic calculation
Let us consider the ordering of real numbers in postgres. As you can see from the results below it has clearly returned the correct results. select( 1. = 1.0002 ); => f select( 1. < 1.0002 ); => t select( 1. > 1.0002 ); => f Imagine the situation however where postgres returned the following values to simple numerical inequalities. In such a case postgresql would be clearly defective and unfit for purpose. select( 1.00 = 1.01 ); => f select( 1.00 < 1.01 ); => f select( 1.00 > 1.01 ); => f If such a situation is unacceptable for the real number line, then in what way can it be acceptable for the real number plain. select( point(1.0,0) <> point(1.1,0) ); => f select( point(1.0,0) << point(1.1,0) ); => f select( point(1.0,0) >> point(1.1,0) ); => f select( point(1.0,0) <-> point(1.1,0) ); => 1.000655e-05 We have two points with a finite separation in the x axis. Postgres thinks they are not the same point, nor one left of the other, nor to the right. This is clearly a both a physical and logical impossibility. The cause of this is the ill conceived FP* macros. They seem represent a solution to a problem that simply does not exist. The first effect of these macros is to reduce the accuracy of all geometric comparisons from double precision, to less than single precision. The following program correctly prints the correct answer. Whereas as we have seen above, postgres falls in a heap. int main() { float f = 1.0; float g = 1.1; if( f==g ) { printf( "f=g\n" ); } if( fg ) { printf( "f>g\n" ); } return 0; } The second effect is to take operations that would of worked correctly even in single precision, and to cause them to produce nonsensical result. For example points that can be both inside and outside a polygon at the same time. Simple analysis of the postgres source code shows that the only places where the FPzero, FPeq, FPne, FPlt, FPle FPgt and FPge macros are defined and used are in the src/backend/utils/adt/geo_ops.c and src/include/utils/geo_decls.h files. What is the justification for these macros? Why do they only affect geometric calculations, and not all numeric calculations? Why should these macro's not be abandoned? Does anyone any any objections to me: 1) removing these macros, or at least disabling EPSILON by default. 2) adding in the obviously missing operators (ie: box @> point) -- 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] Table and Index compression
Not strictly related to compression, but I've noticed something really strange... pg 8.4 (vanilla) is doing it, and my compressed version is doing it too. tablespace is a RAID5 of 3 drives, xlog in on a RAID1 of 2 drives, but it does it too if I put the tablespace and data on the same volume. The traditional test table : BEGIN; CREATE TABLE dwb ( bid SERIAL, aid INTEGER NOT NULL, ts TIMESTAMP NOT NULL, i1 INTEGER NOT NULL, i2 INTEGER NOT NULL, i3 INTEGER NOT NULL, i4 INTEGER NOT NULL ) WITHOUT OIDS; The traditional test data : INSERT INTO dwb (ts,aid,i1,i2,i3,i4) SELECT now() + '1 sec'::INTERVAL, (1+random()*8), random()*1000, n+random()*1, n+random()*1000, n FROM generate_series( 1, 6000 ) AS n; vmstat output : it starts out relatively fast : si sobibo in csus sy id wa 00 0 43680 2796 19162 42 18 37 3 00 0 45515 3165 20652 44 17 35 4 00 0 43130 3046 21991 43 17 38 2 then here it starts to slow down : check "bo" output 00 181 24439 577 3541 31 6 40 23 00 176 17258 292 1324 31 4 43 22 00 0 18626 162 693 35 3 49 12 00 1 21554 235 1362 31 5 50 14 00 0 19177 324 2053 35 4 50 12 00 0 19208 206 1155 36 4 48 12 00 1 20740 215 1117 33 4 50 13 00 0 20154 258 1100 32 4 50 14 00 0 20355 316 2056 34 5 49 12 ... and it stays like this until the end of the INSERT... It's not writing any xlog since the table was created after the BEGIN... I'm trying to benchmark insert speed, but no luck. This volume does about 100 MB/s sustained write speed, so ?.. -- 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] Table and Index compression
For reference what I'm picturing is this: When a table is compressed it's marked read-only which bars any new tuples from being inserted or existing tuples being deleted. Then it's frozen and any pages which contain tuples wich can't be frozen are waited on until they can be. When it's finished every tuple has to be guaranteed to be fully frozen. Then the relation is rewritten in compressed form. Each block is compressed one by one and written one after the other to disk. At the same time a new fork is written which contains a pointer to each block. It could just be a directly addressed array of offsets and lengths. All block lookups have to first load the page of the indirection map, then read the appropriate section of the original file and decompress it into shared buffers. I had pondered the idea of a fork storing the compressed status of each page, because it has advantages : - no need to change the page layout to insert a "is compressed" flag - possible to compress any data, not just standard pages - if you know the compressed size of a page in advance, it is much easier to prefetch it entirely and not just the first chunk, or read too much... From a programming point of view this is nice and simple. From a user's point of view it's a bit of a pain since it means you have to rewrite your whole table when you want to compress it. And it means you have to rewrite it all again if you decide you want to set it back to read-write. My experience with people who have very large tables is that they design their whole process around the goal of avoiding having to move the data once it's written. Note that if a table is huge, it is always cut in (currently) 1GB slices, so you could operate on one slice at a time, then release a lock, let the backlog of queries flow, and resume. Realtime compression would be much less of a hassle to use, though... -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers