Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Am 11.06.2010 21:19, schrieb Robert Haas: On Fri, Jun 11, 2010 at 2:46 PM, Dimitri Fontaine But of course you don't ever do that. What you do once the restore failed on you is fix the schema and the application before to upgrade. Presumably, you mean that YOU don't ever do that. What everybody else does is up to them, and there are plenty of people on this thread saying either (1) they don't want to do what you're proposing or (2) their application doesn't need fixing because it already quotes everything. and 3) the application is fixed already by somebody else (the vendor) -- Schönen Gruß - Regards Hartmut Goebel Dipl.-Informatiker (univ.), CISSP, CSSLP Goebel Consult Spezialist für IT-Sicherheit in komplexen Umgebungen http://www.goebel-consult.de Monatliche Kolumne: http://www.cissp-gefluester.de/ Goebel Consult mit Mitglied bei http://www.7-it.de smime.p7s Description: S/MIME Cryptographic Signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Top posting, sorry for that. -- dim Le 10 juin 2010 à 03:40, Robert Haas robertmh...@gmail.com a écrit : On Wed, Jun 9, 2010 at 9:35 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: I think users would rather have the restore fail, and know right away they have an issue, than to do the upgrade, and find out later that some of their application queries fail and they need to run around fixing them. ?(FYI, pg_upgrade would use the new pg_dump and would not fail.) In a way, the fact that the restore fails can be seen as a feature --- they get the error before the go live on 8.4. ?(Yeah, I am serious.) +1 Eeh, I've had this happen to me on earlier releases, and it didn't feel like a feature to me. YMMV, of course. Would you have preferred later application failure? YES! It's a heck of a lot easier to fix the application than it is to doctor the dump output with vi. But of course you don't ever do that. What you do once the restore failed on you is fix the schema and the application before to upgrade. At least you have a chance to upgrade to a working setup. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
On Fri, Jun 11, 2010 at 2:46 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Eeh, I've had this happen to me on earlier releases, and it didn't feel like a feature to me. YMMV, of course. Would you have preferred later application failure? YES! It's a heck of a lot easier to fix the application than it is to doctor the dump output with vi. But of course you don't ever do that. What you do once the restore failed on you is fix the schema and the application before to upgrade. Presumably, you mean that YOU don't ever do that. What everybody else does is up to them, and there are plenty of people on this thread saying either (1) they don't want to do what you're proposing or (2) their application doesn't need fixing because it already quotes everything. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Stephen Frost sfr...@snowman.net writes: If it's an option w/ a default of off, then chances are the admin will get the failure you're talking about, realize there's an issue, but then have a way to actually *fix* the restore without having to hack up multi-gigabyte files using vi. If you'd like, you're welcome to consider the default of off as a feature. +1 from me for adding the option. +1 too. Seems I've been unclear, my thinking is that this option is not a solution for everyone, but I do agree that it will help in some cases. And in those cases you'll be so happy to have it! Regards, -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Robert Haas robertmh...@gmail.com writes: Suppose I have a server running 8.2 and I'm going to wipe it and install the latest version of $DISTRIBUTION which bundles 8.4. [...] I'm sure someone will tell me my system administration practices suck, but people do these kinds of things, in real life, all the time. Maybe if we all had an IQ of 170 and an infinite hardware budget we wouldn't, but my IQ is only 169. :-) So why aren't you using a distribution that ease the pain by supporting exactly your use case? :) Regards, -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Hartmut Goebel wrote: re. 1): While this may be true for many applications it is using hand-crafted SQL statements, it is plain wrong for all applications using some abstraction layer. These layers need to quote column names anyway and the application does not need to be changed here at all. I have a feeling that many here don't understand how ubiquitous such frameworks are. Our programmers have no way to get a statement to the database from within the application *without* all identifiers being quoted. On the other hand, my conversion software has always been a command line with pg_dump piped to psql. (We are making our first test of Bruce's new techniques this week, though.) -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Am 10.06.2010 03:10, schrieb Bruce Momjian: The point is that if WINDOW was not a reserved word in 8.3 but is in 8.4, then every reference to a user column of WINDOW in any 8.4 application will need to be double-quoted, and odds are the user did not do that in 8.3. This argument is like: We do not need to fix the flat tire. I'm sure the engine is broken, too, so the driver can not drive anyway. Or to say it differently: IMHO your arguments are wrong in three points: 1) You make assumptions about the application bot quoting column names. 2) You are assuming the database maintainer is using an self developed application. 3) You are taking these assumptions as an excuse not to fix your part of the job. re. 1): While this may be true for many applications it is using hand-crafted SQL statements, it is plain wrong for all applications using some abstraction layer. These layers need to quote column names anyway and the application does not need to be changed here at all. re. 2): Simply consider the case where the application is developed by some third party (e.g. some open source project). The application developers already did change the application to work with. So this part of the job is already done. re 3): This is plain finger pointing: Look, there are other issued to be fixed. No need to fix ours. For me these leaves a horrible impression about the Postgresql community: bone-head dogmatic tech-geeks, not willed to make the administrators live easier. In a way, the fact that the restore fails can be seen as a feature --- they get the error before the go live on 8.4. (Yeah, I am serious.) Be happy that I shot you, you would have starved anyway. Gnaa! -- Schönen Gruß - Regards Hartmut Goebel Dipl.-Informatiker (univ.), CISSP, CSSLP Goebel Consult Spezialist für IT-Sicherheit in komplexen Umgebungen http://www.goebel-consult.de Monatliche Kolumne: http://www.cissp-gefluester.de/ Goebel Consult mit Mitglied bei http://www.7-it.de smime.p7s Description: S/MIME Cryptographic Signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Am 05.06.2010 22:02, schrieb Dimitri Fontaine: Alvaro Herrera alvhe...@commandprompt.com writes: I don't think dumps must be human-readable is an argument to reject such a switch, as long as it's off by default. And I haven't seen any other valid argument either, so +1 from me. Well as Bruce said this option won't solve the OP's problem, unless the application he's using for managing the backups do use the option. As I already wrote, this would solve my problem. The application uses a generic framework which quotes all column names (and such) automatically. Esp. this would solve the problem for *all other users* of this application, too. And as I already wrote, too, most of these users/administrators are not database gurus. They need as much support as possible to make their live easier. -- Schönen Gruß - Regards Hartmut Goebel Dipl.-Informatiker (univ.), CISSP, CSSLP Goebel Consult Spezialist für IT-Sicherheit in komplexen Umgebungen http://www.goebel-consult.de Monatliche Kolumne: http://www.cissp-gefluester.de/ Goebel Consult mit Mitglied bei http://www.7-it.de smime.p7s Description: S/MIME Cryptographic Signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Am 07.06.2010 02:32, schrieb Robert Haas: But we will likely add more keywords at some point in the future, and while providing an output format that quotes everything won't fix every potential problem, it might make life easier for some people. +10 Exactly my point: Make life easier for others. Admins have a hard job anyway. BTW: mysql does a far better job here. -- Schönen Gruß - Regards Hartmut Goebel Dipl.-Informatiker (univ.), CISSP, CSSLP Goebel Consult Spezialist für IT-Sicherheit in komplexen Umgebungen http://www.goebel-consult.de Monatliche Kolumne: http://www.cissp-gefluester.de/ Goebel Consult mit Mitglied bei http://www.7-it.de smime.p7s Description: S/MIME Cryptographic Signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Bruce, * Bruce Momjian (br...@momjian.us) wrote: Robert Haas wrote: In a way, the fact that the restore fails can be seen as a feature --- they get the error before the go live on 8.4. ?(Yeah, I am serious.) Eeh, I've had this happen to me on earlier releases, and it didn't feel like a feature to me. YMMV, of course. Would you have preferred later application failure? If it's an option w/ a default of off, then chances are the admin will get the failure you're talking about, realize there's an issue, but then have a way to actually *fix* the restore without having to hack up multi-gigabyte files using vi. If you'd like, you're welcome to consider the default of off as a feature. +1 from me for adding the option. Thanks, Stephen signature.asc Description: Digital signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Hartmut Goebel wrote: Am 07.06.2010 02:32, schrieb Robert Haas: But we will likely add more keywords at some point in the future, and while providing an output format that quotes everything won't fix every potential problem, it might make life easier for some people. +10 Exactly my point: Make life easier for others. Admins have a hard job anyway. I for myself would be rather annoyed if we started quoting all column names in our dumps. This is seriously hampering readability and while it is already annoying that pg_dump output is slightly different from the original DDL used this would make it far worse. I'm also not convinced that this is a good idea at all, using keywords like that is always an issue and forward portability of dumps in general is imho a pipe dream BTW: mysql does a far better job here. not sure I agree here but well... Stefan -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
On Thu, Jun 10, 2010 at 9:02 AM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: I for myself would be rather annoyed if we started quoting all column names in our dumps. This is seriously hampering readability and while it is already annoying that pg_dump output is slightly different from the original DDL used this would make it far worse. It's only been proposed to make it an option, not to shove it down anyone's throat. Given Tom's comments upthread, I suspect that much of this will come down to whether anyone feels like trying to put in the work to make this happen, and whether they can come up with a reasonably clean design that doesn't involve massive code changes. Having not studied the problem, I don't have an opinion on whether that's possible. I do agree that the human readability of pg_dump is an asset in many situations - I have often dumped out the DDL for particular objects just to look at it, for example. However, I emphatically do NOT agree that leaving someone with a 500MB dump file (or, for some people on this list, a whole heck of a lot larger than that) that has to be manually edited to reload is a useful behavior. It's a huge pain in the neck. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Robert Haas wrote: On Thu, Jun 10, 2010 at 9:02 AM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: I for myself would be rather annoyed if we started quoting all column names in our dumps. This is seriously hampering readability and while it is already annoying that pg_dump output is slightly different from the original DDL used this would make it far worse. It's only been proposed to make it an option, not to shove it down anyone's throat. that will pretty much defeat the purpose for most use cases i guess because people will dump with the defaults and only discover the problem after the fact. Given Tom's comments upthread, I suspect that much of this will come down to whether anyone feels like trying to put in the work to make this happen, and whether they can come up with a reasonably clean design that doesn't involve massive code changes. Having not studied the problem, I don't have an opinion on whether that's possible. Well it is probably not possible in the general sense anyway especially not if one considers dynamic SQL and stuff in plpgsql and friends - it still feels like a lot of wasted effort(or rather a promise we are tzrying to make but wont be able to hold) for only limited gain to me. I do agree that the human readability of pg_dump is an asset in many situations - I have often dumped out the DDL for particular objects just to look at it, for example. However, I emphatically do NOT agree that leaving someone with a 500MB dump file (or, for some people on this list, a whole heck of a lot larger than that) that has to be manually edited to reload is a useful behavior. It's a huge pain in the neck. well that's why we recommend to use the new version of pg_dump to dump the old cluster if the intention is an upgrade not sure that is any more pain than manually hacking the dump... Stefan -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
On Thu, Jun 10, 2010 at 15:35, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: Robert Haas wrote: On Thu, Jun 10, 2010 at 9:02 AM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: I for myself would be rather annoyed if we started quoting all column names in our dumps. This is seriously hampering readability and while it is already annoying that pg_dump output is slightly different from the original DDL used this would make it far worse. It's only been proposed to make it an option, not to shove it down anyone's throat. that will pretty much defeat the purpose for most use cases i guess because people will dump with the defaults and only discover the problem after the fact. Well, if you dump in custom format, it could be useful to be able to do this on pg_restore time. Not having followed this thread in detail, but would that work? That would be a much more useful option... I do agree that the human readability of pg_dump is an asset in many situations - I have often dumped out the DDL for particular objects just to look at it, for example. However, I emphatically do NOT agree that leaving someone with a 500MB dump file (or, for some people on this list, a whole heck of a lot larger than that) that has to be manually edited to reload is a useful behavior. It's a huge pain in the neck. well that's why we recommend to use the new version of pg_dump to dump the old cluster if the intention is an upgrade not sure that is any more pain than manually hacking the dump... yeah. There are (supposedly?) a lot of *other* cases where using an old version of pg_dump won't work. At least we reserve the right for it to be. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Stefan Kaltenbrunner wrote: I do agree that the human readability of pg_dump is an asset in many situations - I have often dumped out the DDL for particular objects just to look at it, for example. However, I emphatically do NOT agree that leaving someone with a 500MB dump file (or, for some people on this list, a whole heck of a lot larger than that) that has to be manually edited to reload is a useful behavior. It's a huge pain in the neck. well that's why we recommend to use the new version of pg_dump to dump the old cluster if the intention is an upgrade not sure that is any more pain than manually hacking the dump... Or rename the identifier in the old cluster and modify the application before doing the upgrade. The only valid reason I have heard for allowing this flag (default off), is that some application stacks quote all identifiers and therefore there would be no need to ever change the name of the identifier. In fact, such stacks might already have many identifers that require quoting, like a table called select. The problem is that some of our reserved keywords change from release to release, and using the old pg_dump causes problems. It is sufficient to require people using such application stacks to use the new pg_dump? From a code perspective, the difficulting in adding such a flag is that much of the quoting happens inside the backend, not by pg_dump, and therefore there is significant code change required to add this flag. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Magnus Hagander wrote: On Thu, Jun 10, 2010 at 15:35, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: Robert Haas wrote: On Thu, Jun 10, 2010 at 9:02 AM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: I for myself would be rather annoyed if we started quoting all column names in our dumps. This is seriously hampering readability and while it is already annoying that pg_dump output is slightly different from the original DDL used this would make it far worse. It's only been proposed to make it an option, not to shove it down anyone's throat. that will pretty much defeat the purpose for most use cases i guess because people will dump with the defaults and only discover the problem after the fact. Well, if you dump in custom format, it could be useful to be able to do this on pg_restore time. Not having followed this thread in detail, but would that work? That would be a much more useful option... I don't think so because much of the quoting has to be done in the backend, and it would be hard for pg_dump to munge the dump file before sending it to the backend --- it doesn't have enough knowledge, I am afraid. It could try targeting just new keywords, but I am worried that would cause more problems than it fixes. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Robert Haas wrote: On Thu, Jun 10, 2010 at 9:35 AM, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: I do agree that the human readability of pg_dump is an asset in many situations - I have often dumped out the DDL for particular objects just to look at it, for example. However, I emphatically do NOT agree that leaving someone with a 500MB dump file (or, for some people on this list, a whole heck of a lot larger than that) that has to be manually edited to reload is a useful behavior. It's a huge pain in the neck. well that's why we recommend to use the new version of pg_dump to dump the old cluster if the intention is an upgrade not sure that is any more pain than manually hacking the dump... Maybe so, but I don't give either method high marks for convenience. Suppose I have a server running 8.2 and I'm going to wipe it and install the latest version of $DISTRIBUTION which bundles 8.4. What our current policy essentially means is that I have to get 8.4 running on the old server before I wipe it (presumably compiling by hand, since the old version of the distro doesn't ship it), or else manually frobnicate the dump after I wipe it, or else find another server someplace to install 8.4 on and run the dump there prior to the OS upgrade. This really sucks. It's a huge pain in the tail, especially for people who aren't used to compiling PG from source at the drop of a hat. that's actually a limitation of the distribution packaging. Debian (and ubuntu) have solved that issue already and I believe Devrim is working on fixing that for the rpms as well. I'm sure someone will tell me my system administration practices suck, but people do these kinds of things, in real life, all the time. Maybe if we all had an IQ of 170 and an infinite hardware budget we wouldn't, but my IQ is only 169. :-) ESXi is free, so is xen, kvm, virtualbox and whatnot :) Stefan -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
* Magnus Hagander (mag...@hagander.net) wrote: On Thu, Jun 10, 2010 at 15:35, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: that will pretty much defeat the purpose for most use cases i guess because people will dump with the defaults and only discover the problem after the fact. Well, if you dump in custom format, it could be useful to be able to do this on pg_restore time. Not having followed this thread in detail, but would that work? That would be a much more useful option... Personally, I feel that *both* would be useful, and I'd be unhappy with any implementation which didn't include both. That being said, the users that are likely to run into this problem will, imnsho, be much happier if we tell them oh, just flip option X in your pg_dump than go edit the .sql file with vi and find where the problem cases are and fix them. Obviously, we should caveat our response that this will only fix the pg_dump/restore problem and that their applications may need to be fixed. Thanks, Stephen signature.asc Description: Digital signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Bruce Momjian br...@momjian.us writes: From a code perspective, the difficulting in adding such a flag is that much of the quoting happens inside the backend, not by pg_dump, and therefore there is significant code change required to add this flag. Yeah, and not only that, but you'd need the *old* server to cooperate. Which means BTW that use the newer pg_dump is only an 80% solution. So maybe we do need to think about this. The least invasive answer that I can think of is to invent a force quoting GUC that's looked at by all the deparsing functions used by pg_dump. We have pg_dump set that once, on backend versions that support it, and then we don't have to run around touching every single deparsing function's signature (and adding extra code paths in pg_dump to deal with older versions not having such functions). But the earliest this could be of use would be a 9.1-9.2 update ... regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Stephen Frost wrote: * Magnus Hagander (mag...@hagander.net) wrote: On Thu, Jun 10, 2010 at 15:35, Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: that will pretty much defeat the purpose for most use cases i guess because people will dump with the defaults and only discover the problem after the fact. Well, if you dump in custom format, it could be useful to be able to do this on pg_restore time. Not having followed this thread in detail, but would that work? That would be a much more useful option... Personally, I feel that *both* would be useful, and I'd be unhappy with any implementation which didn't include both. That being said, the users that are likely to run into this problem will, imnsho, be much happier if we tell them oh, just flip option X in your pg_dump than go edit the .sql file with vi and find where the problem cases are and fix them. Obviously, we should caveat our response that this will only fix the pg_dump/restore problem and that their applications may need to be fixed. That is exactly what I think is to big a promise - I don't think we can actually guarantee that this will fix the dump/restore issue (well the dump might load but say the 3 lines of plpgsql using dynamic SQL will still be broken). Imho SQL is code so you need to threat it that way... This is actually one of the smaller issues that can happen when using an older dump against a new backend and given that we make no promise that this is supported at all I don't think we should pretend we do for a specific issue and in fact only a specific subset of that particular issue. Stefan -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: That is exactly what I think is to big a promise - I don't think we can actually guarantee that this will fix the dump/restore issue (well the dump might load but say the 3 lines of plpgsql using dynamic SQL will still be broken). Yeah, that's a mighty good point. We are certainly not going to try to fix the contents of function bodies. The only things we could possibly fix that we don't handle today (when using the newer pg_dump) are references in views, check constraint expressions, etc. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
* Bruce Momjian (br...@momjian.us) wrote: From a code perspective, the difficulting in adding such a flag is that much of the quoting happens inside the backend, not by pg_dump, and therefore there is significant code change required to add this flag. So, that strikes me as the main argument against adding this- code complexity and/or duplication, etc. That being said, I'm less than convinced that it's really all that big of an issue when we're talking about a quote-everything flag. That doesn't require figuring out what the reserved words are or anything along those lines that quote_ident() currently deals with. It's plain-jane search for characters that need to be quoted and handle them appropriately. Sure, if we can find a way to stick that actual source code somewhere that all the pieces which need it can get to it w/o code duplication, that'd be great, but it just doesn't feel like a hard problem to solve or one that requires a huge amount of code.. Thanks, Stephen signature.asc Description: Digital signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Bruce, * Bruce Momjian (br...@momjian.us) wrote: Well, if you dump in custom format, it could be useful to be able to do this on pg_restore time. Not having followed this thread in detail, but would that work? That would be a much more useful option... I don't think so because much of the quoting has to be done in the backend, and it would be hard for pg_dump to munge the dump file before sending it to the backend --- it doesn't have enough knowledge, I am afraid. It could try targeting just new keywords, but I am worried that would cause more problems than it fixes. We're talking about a quote-everything option, not what quote_ident() does today. I don't see why that needs to be done by the backend or why pg_dump/pg_restore doesn't have enough info to handle that. We don't change what has to be *quoted* inside a quoted identifier terribly often... Thanks, Stephen signature.asc Description: Digital signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
* Tom Lane (t...@sss.pgh.pa.us) wrote: Stefan Kaltenbrunner ste...@kaltenbrunner.cc writes: That is exactly what I think is to big a promise - I don't think we can actually guarantee that this will fix the dump/restore issue (well the dump might load but say the 3 lines of plpgsql using dynamic SQL will still be broken). Yeah, that's a mighty good point. We are certainly not going to try to fix the contents of function bodies. The only things we could possibly fix that we don't handle today (when using the newer pg_dump) are references in views, check constraint expressions, etc. Erm, I don't know that we deal with function-body problems today, even when using the newer version of pg_dump, do we? Don't we set check_function_bodies off, meaning they won't hit the problem till they try to run the function? We use $ quoting for the function bodies entirely otherwise... Thanks, Stephen signature.asc Description: Digital signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Stephen Frost sfr...@snowman.net writes: We're talking about a quote-everything option, not what quote_ident() does today. I don't see why that needs to be done by the backend or why pg_dump/pg_restore doesn't have enough info to handle that. Are you proposing to stick a SQL parser into pg_dump so that it can split apart view and expression definitions to tell what is an identifier and what isn't? BTW, it wouldn't be just one parser, but a different one for each back version supported by pg_dump, else you'll fail on exactly the cases that are of concern here. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Stephen Frost sfr...@snowman.net writes: Erm, I don't know that we deal with function-body problems today, even when using the newer version of pg_dump, do we? Right, any forward-compatibility problems arising inside functions are strictly the user's to deal with, and always have been. So Stefan's point is that we could get from maybe an 80% fix to maybe a 90% fix, after expending quite a bit of trouble. Not clear it's worth it. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
* Tom Lane (t...@sss.pgh.pa.us) wrote: Stephen Frost sfr...@snowman.net writes: Erm, I don't know that we deal with function-body problems today, even when using the newer version of pg_dump, do we? Right, any forward-compatibility problems arising inside functions are strictly the user's to deal with, and always have been. So Stefan's point is that we could get from maybe an 80% fix to maybe a 90% fix, after expending quite a bit of trouble. Not clear it's worth it. To this point, and perhaps to the other regarding VIEW definitions to some extent, while the solution would move us from 80% to 90% of things in PG that might cause a restore from an older pg_dump to fail, I think another metric we should consider is % of our user base, particularly those more junior, that would benefit. I feel that number to be 10%, and growing. Additionally, those that this would really help are the same people who don't have complex views and/or stored procedures. I'm not a huge fan of using that to argue out of dealing with view definitions (that's certainly a complex problem and I understand the issue you raise there), but I'm not seeing a path to fixing that yet. Thanks for pointing that out. Perhaps that's what we get for having those silly complex VIEW thingies that certain others only added very recently. :) Thanks again, Stephen signature.asc Description: Digital signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Stephen Frost sfr...@snowman.net writes: To this point, and perhaps to the other regarding VIEW definitions to some extent, while the solution would move us from 80% to 90% of things in PG that might cause a restore from an older pg_dump to fail, I think another metric we should consider is % of our user base, particularly those more junior, that would benefit. I feel that number to be 10%, and growing. Additionally, those that this would really help are the same people who don't have complex views and/or stored procedures. Um, I rather doubt that experience level has much of anything to do with one's probability of getting blindsided by new SQL syntax. regards, tom lane PS: unless your definition of experienced is sits on the SQL standards committee ;-) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Excerpts from Bruce Momjian's message of mié jun 09 21:35:57 -0400 2010: Alvaro Herrera wrote: Excerpts from Bruce Momjian's message of mi\xc3\xa9 jun 09 21:10:21 -0400 2010: I think users would rather have the restore fail, and know right away they have an issue, than to do the upgrade, and find out later that some of their application queries fail and they need to run around fixing them. (FYI, pg_upgrade would use the new pg_dump and would not fail.) I think it is quite a stretch to consider this a feature. How about a desireable behavior considering the alternatives? I'm with Robert Haas. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
On 10/06/10 16:21, Robert Haas wrote: I do agree that the human readability of pg_dump is an asset in many situations - I have often dumped out the DDL for particular objects just to look at it, for example. However, I emphatically do NOT agree that leaving someone with a 500MB dump file (or, for some people on this list, a whole heck of a lot larger than that) that has to be manually edited to reload is a useful behavior. It's a huge pain in the neck. Much easier to do a schema-only dump, edit that, and dump data separately. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 10/06/10 16:21, Robert Haas wrote: I do agree that the human readability of pg_dump is an asset in many situations - I have often dumped out the DDL for particular objects just to look at it, for example. However, I emphatically do NOT agree that leaving someone with a 500MB dump file (or, for some people on this list, a whole heck of a lot larger than that) that has to be manually edited to reload is a useful behavior. It's a huge pain in the neck. Much easier to do a schema-only dump, edit that, and dump data separately. That gets you out of the huge-file-to-edit problem, but the performance costs of restoring a separate-data dump are a pretty serious disadvantage. We really should do something about that. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Tom Lane wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: On 10/06/10 16:21, Robert Haas wrote: I do agree that the human readability of pg_dump is an asset in many situations - I have often dumped out the DDL for particular objects just to look at it, for example. However, I emphatically do NOT agree that leaving someone with a 500MB dump file (or, for some people on this list, a whole heck of a lot larger than that) that has to be manually edited to reload is a useful behavior. It's a huge pain in the neck. Much easier to do a schema-only dump, edit that, and dump data separately. That gets you out of the huge-file-to-edit problem, but the performance costs of restoring a separate-data dump are a pretty serious disadvantage. We really should do something about that. well that is an argument for providing not only --schema-only and --data-only but rather three options one for the table definitions, one for the data and one for all the constraints and indexes. So basically what pg_dump is currently doing anyway but just exposed as flags. Stefan -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
* Tom Lane (t...@sss.pgh.pa.us) wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Much easier to do a schema-only dump, edit that, and dump data separately. That gets you out of the huge-file-to-edit problem, but the performance costs of restoring a separate-data dump are a pretty serious disadvantage. We really should do something about that. Big +1 on that.. I *still* do it by hand much of the time these days (manually hacking out the CREATE TABLE step from the ALTER TABLE; CREATE INDEX piece). We took some steps towards improving that using custom dump formats, iirc, but the patch Simon (iirc) for adding options to pg_dump to have it split things out for the SQL-style dump never did get in (think there were dependency issues and whatnot, and I have to admit that it didn't really have the best UI/parameters). Perhaps we should have a 'multi-file' option with a 'base-file-name' parameter which then generates: pre-data DDL data post-data DDL psql script to run them in order (\i-style) ? Just my 2c. Thanks, Stephen signature.asc Description: Digital signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
* Stefan Kaltenbrunner (ste...@kaltenbrunner.cc) wrote: well that is an argument for providing not only --schema-only and --data-only but rather three options one for the table definitions, one for the data and one for all the constraints and indexes. So basically what pg_dump is currently doing anyway but just exposed as flags. There was a big/long thread on this on -hackers a while back.. Perhaps we need to go back and figure out what happened. Have to admit, that's one of the reason I like creating wiki pages for these kinds of, even admittedly small, things. Easier to find than searching the archive, ime. Of course, that'll probably change as more things get added to the wiki. :) Thanks, Stephen signature.asc Description: Digital signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Stephen Frost sfr...@snowman.net writes: Perhaps we should have a 'multi-file' option with a 'base-file-name' parameter which then generates: pre-data DDL data post-data DDL psql script to run them in order (\i-style) Actually, I was thinking that the three-file approach is just unnecessary complication. What about two files, schema and data, with the schema file including a \i for the data at the right place? This could be enabled by a single additional switch --data-file=filename, with the schema output still going where it goes now (ie, -f or stdout). regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
* Tom Lane (t...@sss.pgh.pa.us) wrote: Actually, I was thinking that the three-file approach is just unnecessary complication. What about two files, schema and data, with the schema file including a \i for the data at the right place? This could be enabled by a single additional switch --data-file=filename, with the schema output still going where it goes now (ie, -f or stdout). +1 from me, excellent solution. Thanks, Stephen signature.asc Description: Digital signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
On Thu, Jun 10, 2010 at 10:25 AM, Tom Lane t...@sss.pgh.pa.us wrote: Bruce Momjian br...@momjian.us writes: From a code perspective, the difficulting in adding such a flag is that much of the quoting happens inside the backend, not by pg_dump, and therefore there is significant code change required to add this flag. Yeah, and not only that, but you'd need the *old* server to cooperate. Which means BTW that use the newer pg_dump is only an 80% solution. So maybe we do need to think about this. The least invasive answer that I can think of is to invent a force quoting GUC that's looked at by all the deparsing functions used by pg_dump. We have pg_dump set that once, on backend versions that support it, and then we don't have to run around touching every single deparsing function's signature (and adding extra code paths in pg_dump to deal with older versions not having such functions). But the earliest this could be of use would be a 9.1-9.2 update ... That's OK. I don't have an immediate problem I need to solve; I just want to improve things for future users. The fact is, I've had this problem in the past, and it wasn't fun, so, I understand the OP's pain. But there's definitely a workaround until we get this done, it just isn't a particularly enjoyable one. The deparse_force_quoting GUC is a good idea - I bet the number of places that would need to examine the value of that GUC is extremely small. One possible objection is that such a GUC would also affect the output of tools like EXPLAIN, but I'm not sure we really need to worry about that. If someone really wants to force quoting in their EXPLAIN output, I say let 'em. If we really wanted to get sneaky we could even let the value of the GUC be a list of words to force-quote, with * meaning all. That would let a newer server talking to an older server hand over a specific list of keywords that it knows are problem cases, even if the user isn't using --quote-like-crazy. Not sure if we want to go there, though. The idea mentioned on another part of this thread of providing a way to separate schema and data dumps without tanking performance is a good one, too, but I still think this has merit even if we do that. Just because we make it easier to manually edit dump files is not a reason not to create options that render it unnecessary in the first place. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Am 10.06.2010 13:46, schrieb Kevin Grittner: I have a feeling that many here don't understand how ubiquitous such frameworks are. I got his impression, too. :-( Our programmers have no way to get a statement to the database from within the application *without* all identifiers being quoted. Since I've be confused a bit by your statement, I'll try to express it differently: These frameworks simply qoute *all* identifiers, no matter if the identifier is a keyword or not. This is done to take the burden of the programmer to take care about identifiers. So it simply does not matter when a keyword is added. I wished, pg_dump could do the same and easy admins life :-\ -- Schönen Gruß - Regards Hartmut Goebel Dipl.-Informatiker (univ.), CISSP, CSSLP Goebel Consult Spezialist für IT-Sicherheit in komplexen Umgebungen http://www.goebel-consult.de Monatliche Kolumne: http://www.cissp-gefluester.de/ Goebel Consult mit Mitglied bei http://www.7-it.de smime.p7s Description: S/MIME Cryptographic Signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Am 10.06.2010 03:35, schrieb Bruce Momjian: Robert Haas wrote: I think users would rather have the restore fail, and know right away they have an issue, than to do the upgrade, and find out later that some of their application queries fail and they need to run around fixing them. ?(FYI, pg_upgrade would use the new pg_dump and would not fail.) In a way, the fact that the restore fails can be seen as a feature --- they get the error before the go live on 8.4. ?(Yeah, I am serious.) Eeh, I've had this happen to me on earlier releases, and it didn't feel like a feature to me. YMMV, of course. Would you have preferred later application failure? Yes! Since this would at least solve one issue: migrating the data. And if the application is developed elsewhere, it should be fixed when I'm upgrading. -- Schönen Gruß - Regards Hartmut Goebel Dipl.-Informatiker (univ.), CISSP, CSSLP Goebel Consult Spezialist für IT-Sicherheit in komplexen Umgebungen http://www.goebel-consult.de Monatliche Kolumne: http://www.cissp-gefluester.de/ Goebel Consult mit Mitglied bei http://www.7-it.de smime.p7s Description: S/MIME Cryptographic Signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Am 10.06.2010 15:48, schrieb Robert Haas: Maybe so, but I don't give either method high marks for convenience. Suppose I have a server running 8.2 and I'm going to wipe it and install the latest version of $DISTRIBUTION which bundles 8.4. What our current policy essentially means is that I have to get 8.4 running on the old server before I wipe it (presumably compiling by hand, since the old version of the distro doesn't ship it), or else manually frobnicate the dump after I wipe it, or else find another server someplace to install 8.4 on and run the dump there prior to the OS upgrade. This really sucks. It's a huge pain in the tail, especially for people who aren't used to compiling PG from source at the drop of a hat. +1 I'm sure someone will tell me my system administration practices suck, but people do these kinds of things, in real life, all the time. +1 -- Schönen Gruß - Regards Hartmut Goebel Dipl.-Informatiker (univ.), CISSP, CSSLP Goebel Consult Spezialist für IT-Sicherheit in komplexen Umgebungen http://www.goebel-consult.de Monatliche Kolumne: http://www.cissp-gefluester.de/ Goebel Consult mit Mitglied bei http://www.7-it.de smime.p7s Description: S/MIME Cryptographic Signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Am 10.06.2010 17:01, schrieb Tom Lane: Um, I rather doubt that experience level has much of anything to do with one's probability of getting blindsided by new SQL syntax. Please stop expecting the one doing the upgrade has a lot of knowledge at all. He is just the one pointed out to perform the update. He is a junior admin. He doe not want (nor has time and budget) taking a course, just to upgrade da database. sarcasm Hej, let's educate the juniors! They should fall into all the pitfalls, we've fallen in. We aren't they reading all of our fine postgresql manual, subscribe to five mailing lists, become a senior and then upgrade? *Gnaa* /sarcasm Com'on guy! Is it really that hard to understand that others are no gurus and have to upgrade anyway?! Why are you fighting to make their life harder? -- Schönen Gruß - Regards Hartmut Goebel Dipl.-Informatiker (univ.), CISSP, CSSLP Goebel Consult Spezialist für IT-Sicherheit in komplexen Umgebungen http://www.goebel-consult.de Monatliche Kolumne: http://www.cissp-gefluester.de/ Goebel Consult mit Mitglied bei http://www.7-it.de smime.p7s Description: S/MIME Cryptographic Signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Am 10.06.2010 17:23, schrieb Heikki Linnakangas: Much easier to do a schema-only dump, edit that, and dump data separately. I tries this in my very case. Did not work due sequences, triggers and primary keys. I ended up editing a 500 MB file in vi. -- Schönen Gruß - Regards Hartmut Goebel Dipl.-Informatiker (univ.), CISSP, CSSLP Goebel Consult Spezialist für IT-Sicherheit in komplexen Umgebungen http://www.goebel-consult.de Monatliche Kolumne: http://www.cissp-gefluester.de/ Goebel Consult mit Mitglied bei http://www.7-it.de smime.p7s Description: S/MIME Cryptographic Signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Robert Haas wrote: On Sun, Jun 6, 2010 at 2:53 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Robert Haas robertmh...@gmail.com writes: Well as Bruce said this option won't solve the OP's problem, unless the application he's using for managing the backups do use the option. Well, that's a pretty trivial change to the backup script. ?+1 from me on providing a pg_dump option. The application still have to have been using the option in the past. Well, if your point is that it's too late to help anyone upgrading from 8.3 to 8.4, then I agree with you. But we will likely add more keywords at some point in the future, and while providing an output format that quotes everything won't fix every potential problem, it might make life easier for some people. I certainly have had times where it would have saved me hassle and aggravation. The point is that if WINDOW was not a reserved word in 8.3 but is in 8.4, then every reference to a user column of WINDOW in any 8.4 application will need to be double-quoted, and odds are the user did not do that in 8.3. I think users would rather have the restore fail, and know right away they have an issue, than to do the upgrade, and find out later that some of their application queries fail and they need to run around fixing them. (FYI, pg_upgrade would use the new pg_dump and would not fail.) In a way, the fact that the restore fails can be seen as a feature --- they get the error before the go live on 8.4. (Yeah, I am serious.) -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
On Wed, Jun 9, 2010 at 9:10 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: On Sun, Jun 6, 2010 at 2:53 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Robert Haas robertmh...@gmail.com writes: Well as Bruce said this option won't solve the OP's problem, unless the application he's using for managing the backups do use the option. Well, that's a pretty trivial change to the backup script. ?+1 from me on providing a pg_dump option. The application still have to have been using the option in the past. Well, if your point is that it's too late to help anyone upgrading from 8.3 to 8.4, then I agree with you. But we will likely add more keywords at some point in the future, and while providing an output format that quotes everything won't fix every potential problem, it might make life easier for some people. I certainly have had times where it would have saved me hassle and aggravation. The point is that if WINDOW was not a reserved word in 8.3 but is in 8.4, then every reference to a user column of WINDOW in any 8.4 application will need to be double-quoted, and odds are the user did not do that in 8.3. I think users would rather have the restore fail, and know right away they have an issue, than to do the upgrade, and find out later that some of their application queries fail and they need to run around fixing them. (FYI, pg_upgrade would use the new pg_dump and would not fail.) In a way, the fact that the restore fails can be seen as a feature --- they get the error before the go live on 8.4. (Yeah, I am serious.) Eeh, I've had this happen to me on earlier releases, and it didn't feel like a feature to me. YMMV, of course. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Excerpts from Bruce Momjian's message of mié jun 09 21:10:21 -0400 2010: I think users would rather have the restore fail, and know right away they have an issue, than to do the upgrade, and find out later that some of their application queries fail and they need to run around fixing them. (FYI, pg_upgrade would use the new pg_dump and would not fail.) I think it is quite a stretch to consider this a feature. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Robert Haas wrote: I think users would rather have the restore fail, and know right away they have an issue, than to do the upgrade, and find out later that some of their application queries fail and they need to run around fixing them. ?(FYI, pg_upgrade would use the new pg_dump and would not fail.) In a way, the fact that the restore fails can be seen as a feature --- they get the error before the go live on 8.4. ?(Yeah, I am serious.) Eeh, I've had this happen to me on earlier releases, and it didn't feel like a feature to me. YMMV, of course. Would you have preferred later application failure? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Alvaro Herrera wrote: Excerpts from Bruce Momjian's message of mi?? jun 09 21:10:21 -0400 2010: I think users would rather have the restore fail, and know right away they have an issue, than to do the upgrade, and find out later that some of their application queries fail and they need to run around fixing them. (FYI, pg_upgrade would use the new pg_dump and would not fail.) I think it is quite a stretch to consider this a feature. How about a desireable behavior considering the alternatives? -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
On Wed, Jun 9, 2010 at 9:35 PM, Bruce Momjian br...@momjian.us wrote: Robert Haas wrote: I think users would rather have the restore fail, and know right away they have an issue, than to do the upgrade, and find out later that some of their application queries fail and they need to run around fixing them. ?(FYI, pg_upgrade would use the new pg_dump and would not fail.) In a way, the fact that the restore fails can be seen as a feature --- they get the error before the go live on 8.4. ?(Yeah, I am serious.) Eeh, I've had this happen to me on earlier releases, and it didn't feel like a feature to me. YMMV, of course. Would you have preferred later application failure? YES! It's a heck of a lot easier to fix the application than it is to doctor the dump output with vi. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
On Jun 5, 2010, at 4:02 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Alvaro Herrera alvhe...@commandprompt.com writes: I don't think dumps must be human-readable is an argument to reject such a switch, as long as it's off by default. And I haven't seen any other valid argument either, so +1 from me. Well as Bruce said this option won't solve the OP's problem, unless the application he's using for managing the backups do use the option. Well, that's a pretty trivial change to the backup script. +1 from me on providing a pg_dump option. ...Robert -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Robert Haas robertmh...@gmail.com writes: Well as Bruce said this option won't solve the OP's problem, unless the application he's using for managing the backups do use the option. Well, that's a pretty trivial change to the backup script. +1 from me on providing a pg_dump option. The application still have to have been using the option in the past. -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
On Sun, Jun 6, 2010 at 2:53 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Robert Haas robertmh...@gmail.com writes: Well as Bruce said this option won't solve the OP's problem, unless the application he's using for managing the backups do use the option. Well, that's a pretty trivial change to the backup script. +1 from me on providing a pg_dump option. The application still have to have been using the option in the past. Well, if your point is that it's too late to help anyone upgrading from 8.3 to 8.4, then I agree with you. But we will likely add more keywords at some point in the future, and while providing an output format that quotes everything won't fix every potential problem, it might make life easier for some people. I certainly have had times where it would have saved me hassle and aggravation. I think as a community we can sometimes be a bit intolerant of people who don't do things exactly the right way and get themselves into trouble. I don't think we should tell people there is a theoretical way that you could have avoided falling down that pit so it's all good - we should try to build fences around the pits. There's no way we can future-proof pg_dump output completely because we don't know what we'll change in the future, but I don't think that makes it worthless to provide an option that makes it future-proof against one of the common causes of trouble. If I had a nickle for every time I read a bug report about a usability issue that has also bitten me, I'd have about two bucks. If I had another nickle for every time someone told one of those people their problem is their own fault, I would have about another four bucks. The fact that it's true doesn't mean that it's the best we can do. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Robert Haas robertmh...@gmail.com writes: I think as a community we can sometimes be a bit intolerant of people who don't do things exactly the right way and get themselves into trouble. Casting aside the sweeping generalizations for a moment ... this is about how much effort we are willing to put into making upgrades bulletproof even for those who don't follow directions. The proposed change requires a major amount of work (if you don't think so, I will merely suggest thinking about pg_get_expr and kin), and it doesn't fix anything that's very interesting except to a small subset of users who are willing to quote identifiers forever rather than rename to avoid new reserved words. The handwriting has been on the wall for WINDOW since SQL:2003, no? regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Alvaro Herrera alvhe...@commandprompt.com writes: I don't think dumps must be human-readable is an argument to reject such a switch, as long as it's off by default. And I haven't seen any other valid argument either, so +1 from me. Well as Bruce said this option won't solve the OP's problem, unless the application he's using for managing the backups do use the option. The current solution seems to be to use a packaging system that allows installing several major versions at any time, and use this ability to properly migrate data. Hint: debian does just this. http://manpages.debian.net/cgi-bin/man.cgi?query=pg_upgradecluster http://manpages.ubuntu.com/manpages/hardy/man8/pg_upgradecluster.8.html Regards, -- dim -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Am 03.06.2010 16:15, schrieb Tom Lane: Solution: pg_dump should quote *all* column-names, no matter if they are keywords or not. That was considered and rejected long ago. Readability of the dump script is something that we put a nonzero value on. Sorry, I do not understand this. I assume you mean readability for humans?! So if readability is not important, what speaks against always quoting the column names? -- Schönen Gruß - Regards Hartmut Goebel Dipl.-Informatiker (univ.), CISSP, CSSLP Goebel Consult Spezialist für IT-Sicherheit in komplexen Umgebungen http://www.goebel-consult.de Monatliche Kolumne: http://www.cissp-gefluester.de/ Goebel Consult mit Mitglied bei http://www.7-it.de smime.p7s Description: S/MIME Cryptographic Signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Am 03.06.2010 20:07, schrieb Tom Lane: Kevin Grittner kevin.gritt...@wicourts.gov writes: Hartmut Goebel h.goe...@goebel-consult.de wrote: If upgraded the rpm-packages from 8.3 to 8.4. Then postgres failed starting (something like Database version mismatch). You need to be running the old server using 8.3 software and while using pg_dump from 8.4 software. Does your packager provide some way to install the new version at a different location? If not, is there a separate machine on which you could install 8.4? In practice, if he has to redo the dump, the easiest fix is really going to be to rename the column beforehand. He's likely to end up doing that anyway rather than quoting its name forever ... Both solutions are quite complicated and require a lot of work and knowledge. Esp. since there seams to be no upgrade or migration guide available. (NB: I personally solved the problem using pg_restore | sed | pqsl. But this bug realy is about a generic problem.) Given the fact that postgres is not only used in high end environments which have a professional database admin (see below), I strongly suggest finding a solution which is easier to handle for average admins. The solution I suggested (simply quoting all column names) would AFAIK solve this problem once and forever. An example for Postgresql in a non-database-admin evironment is the three tier ERP application www.tryton.org. The Tryton admin typically is not a database guy, but a generic, average server administrator. He probably knowns about databases, SQL, etc. But he has *a lot* of work and he is happy about everything which makes his live easier. And he hates stuff which does not work, while it is commonly expected to work easy. The Tryton GUI offers backing up the database, which is simply pg_dump behind. The Tryton admin expects to be able to restore this backup after upgrade. Because it is such easy to get a database backup, he expects restore being that easy, too. The Tryton admin does not understand at first, why this doe not work. It worked when upgrading 8.1 to 8.2 and when upgrading 8.2 to 8.3. But when upgrading to 8.4 it does not work. If the admin is a Mysql-fan, he will be curing on postgres, as soon as he found out how easy the solution would have been: Would I have stayied at mysql, they are able to quote all column names if neccessary. Sh** postgres! And he will be wasting another hour (or more) working around the problem. While the solution could be *so easy*: simply quote all column names in pg_dump. (And backport to 8.0, 8.2, 8.3 :-) -- Schönen Gruß - Regards Hartmut Goebel Dipl.-Informatiker (univ.), CISSP, CSSLP Goebel Consult Spezialist für IT-Sicherheit in komplexen Umgebungen http://www.goebel-consult.de Monatliche Kolumne: http://www.cissp-gefluester.de/ Goebel Consult mit Mitglied bei http://www.7-it.de smime.p7s Description: S/MIME Cryptographic Signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
* Hartmut Goebel (h.goe...@goebel-consult.de) wrote: Am 03.06.2010 16:15, schrieb Tom Lane: That was considered and rejected long ago. Readability of the dump script is something that we put a nonzero value on. I assume you mean readability for humans?! Yes, readability for humans is important. So if readability is not important, what speaks against always quoting the column names? Quoting all column names makes the dump script much more difficult for human consumption, which is important. Thanks, Stephen -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
* Hartmut Goebel (h.goe...@goebel-consult.de) wrote: Am 04.06.2010 13:56, schrieb Stephen Frost: Quoting all column names makes the dump script much more difficult for human consumption, which is important. I don't agree with you here. But this may be a matter of personal taste. Esp. I think, functionality is much ore important than a small decrees of readability. At least pg_dump should get an option --quote-column-names, so this can be switcced on if necessary. Something like '--quote-identifiers' might be alright, so long as it's defaulted to 'off'. Of course, I don't know that it'd actually solve your problem at all- after all, keywords can and will change between major versions and even if your pg_dump quotes all identifiers, anything else using the database (eg: applications) would need to as well. Keyword changes aren't the only thing an application or other DB user needs to be concerned about when changing major versions of PG either. If you're using pg_dump for backups, then when you need to restore, it should be into the same version of PG that you took the pg_dump from. If you're using pg_dump to upgrade, use the pg_dump from the version you're upgrading *to*, and do so in a test environment first to make sure that the restore works correctly, that the applications and other DB users are happy with the new version, etc, etc, before even thinking about upgrading a production system. Note also that having multiple major versions of PG installed (eg: 8.3 and 8.4) at the same time is made easier on some platforms (Debian-based ones, specifically). Thanks, Stephen -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Am 04.06.2010 13:56, schrieb Stephen Frost: Quoting all column names makes the dump script much more difficult for human consumption, which is important. I don't agree with you here. But this may be a matter of personal taste. Esp. I think, functionality is much ore important than a small decrees of readability. At least pg_dump should get an option --quote-column-names, so this can be switcced on if necessary. -- Schönen Gruß - Regards Hartmut Goebel Dipl.-Informatiker (univ.), CISSP, CSSLP Goebel Consult Spezialist für IT-Sicherheit in komplexen Umgebungen http://www.goebel-consult.de Monatliche Kolumne: http://www.cissp-gefluester.de/ Goebel Consult mit Mitglied bei http://www.7-it.de smime.p7s Description: S/MIME Cryptographic Signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Am 04.06.2010 14:57, schrieb Stephen Frost: * Hartmut Goebel (h.goe...@goebel-consult.de) wrote: Am 04.06.2010 13:56, schrieb Stephen Frost: Quoting all column names makes the dump script much more difficult for human consumption, which is important. I don't agree with you here. But this may be a matter of personal taste. Esp. I think, functionality is much ore important than a small decrees of readability. At least pg_dump should get an option --quote-column-names, so this can be switcced on if necessary. Something like '--quote-identifiers' might be alright, so long as it's defaulted to 'off'. Of course, I don't know that it'd actually solve your problem at all- after all, keywords can and will change between major versions and even if your pg_dump quotes all identifiers, anything else using the database (eg: applications) would need to as well. The application already quotes all column names :-) It's using a generic framework which does not (and must not) rely on column names being non-keywords. If you're using pg_dump to upgrade, use the pg_dump from the version you're upgrading *to*, and do so in a test environment first to make sure that the restore works correctly, that the applications and other DB users are happy with the new version, etc, etc, before even thinking about upgrading a production system. This is correct -- in theory. In practice there are many average system administrators which need an easy upgrade path. You may call this unprofessional, but this is reality. To put it on the point: Is postgres meant for average administrators or for elite database admins? In the first case, developers should think about how to make work easier for the average ones. -- Schönen Gruß - Regards Hartmut Goebel Dipl.-Informatiker (univ.), CISSP, CSSLP Goebel Consult Spezialist für IT-Sicherheit in komplexen Umgebungen http://www.goebel-consult.de Monatliche Kolumne: http://www.cissp-gefluester.de/ Goebel Consult mit Mitglied bei http://www.7-it.de smime.p7s Description: S/MIME Cryptographic Signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Hartmut Goebel h.goe...@goebel-consult.de wrote: The application already quotes all column names :-) It's using a generic framework which does not (and must not) rely on column names being non-keywords. Same here. I suspect that this is much more commonn than many PostgreSQL developers realize; and I think it makes a reasonable case for at least an *option* to quote all identifiers emitted by pg_dump. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Kevin Grittner wrote: Hartmut Goebel h.goe...@goebel-consult.de wrote: The application already quotes all column names :-) It's using a generic framework which does not (and must not) rely on column names being non-keywords. Same here. I suspect that this is much more commonn than many PostgreSQL developers realize; and I think it makes a reasonable case for at least an *option* to quote all identifiers emitted by pg_dump. Even if we quote them in the dump, I assume applications would need to quote them too, which I doubt many do. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
On Fri, Jun 04, 2010 at 02:59:48PM -0400, Bruce Momjian wrote: Kevin Grittner wrote: Hartmut Goebel h.goe...@goebel-consult.de wrote: The application already quotes all column names :-) It's using a generic framework which does not (and must not) rely on column names being non-keywords. Same here. I suspect that this is much more commonn than many PostgreSQL developers realize; and I think it makes a reasonable case for at least an *option* to quote all identifiers emitted by pg_dump. Even if we quote them in the dump, I assume applications would need to quote them too, which I doubt many do. It seems like something that's doable by pg_dump as a default off option. TODO for 9.1? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
David Fetter da...@fetter.org wrote: It seems like something that's doable by pg_dump as a default off option. TODO for 9.1? Sounds good to me. -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
David Fetter wrote: On Fri, Jun 04, 2010 at 02:59:48PM -0400, Bruce Momjian wrote: Kevin Grittner wrote: Hartmut Goebel h.goe...@goebel-consult.de wrote: The application already quotes all column names :-) It's using a generic framework which does not (and must not) rely on column names being non-keywords. Same here. I suspect that this is much more commonn than many PostgreSQL developers realize; and I think it makes a reasonable case for at least an *option* to quote all identifiers emitted by pg_dump. Even if we quote them in the dump, I assume applications would need to quote them too, which I doubt many do. It seems like something that's doable by pg_dump as a default off option. TODO for 9.1? This is the bug report that prompted this thread: http://archives.postgresql.org/pgsql-bugs/2010-06/msg00018.php I bigger question is why wouldn't we backpatch WINDOW as quoted in pg_dump when we release back-branches? That would make the bug go away, rather than require users to use a special flag (and find out only after they were doing the reload). -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
--On 4. Juni 2010 15:19:42 -0400 Bruce Momjian br...@momjian.us wrote: That would make the bug go away, rather than require users to use a special flag (and find out only after they were doing the reload). Out of curiosity, why is this a bug now? We recommend migration procedures always to use the pg_dump of the newer version because of many reasons as clearly stated here: http://www.postgresql.org/docs/8.4/interactive/migration.html And wouldn't introducing backpatching such behavorial changes to pg_dump violate our policy in *not* to change such things in minor releases? (think of diff's against schema-only dumps and so on). Bernd -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Bernd Helmle maili...@oopsware.de writes: Out of curiosity, why is this a bug now? It isn't... And wouldn't introducing backpatching such behavorial changes to pg_dump violate our policy in *not* to change such things in minor releases? That was just an off-the-cuff idea, it has certainly not become policy (and won't, I think). regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Excerpts from Kevin Grittner's message of vie jun 04 14:53:17 -0400 2010: Same here. I suspect that this is much more commonn than many PostgreSQL developers realize; and I think it makes a reasonable case for at least an *option* to quote all identifiers emitted by pg_dump. I don't think dumps must be human-readable is an argument to reject such a switch, as long as it's off by default. And I haven't seen any other valid argument either, so +1 from me. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
The following bug has been logged online: Bug reference: 5488 Logged by: Hartmut Goebel Email address: h.goe...@goebel-consult.de PostgreSQL version: 8.3 / 8.4 Operating system: all Description:pg_dump does not quote column names - pg_restore may fail when upgrading Details: If a 8.3 table contains a column named window, the dump can not be restored into a 8.4 database. Reasons: a) window is a new keyword in 8.4 b) pg_dump does not quote column names. This is a generic problem with pg_dump. Since it does not quote all column names, pg_restore may fail whenever migrating to a new version. Solution: pg_dump should quote *all* column-names, no matter if they are keywords or not. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Hartmut Goebel h.goe...@goebel-consult.de wrote: Description:pg_dump does not quote column names - pg_restore may fail when upgrading If a 8.3 table contains a column named window, the dump can not be restored into a 8.4 database. Reasons: a) window is a new keyword in 8.4 b) pg_dump does not quote column names. Note that the documentation recommends always running pg_dump using the executable from the target version, not the source version. Are you using the pg_dump executable from 8.4? -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Hartmut Goebel h.goe...@goebel-consult.de writes: If a 8.3 table contains a column named window, the dump can not be restored into a 8.4 database. Reasons: a) window is a new keyword in 8.4 b) pg_dump does not quote column names. This is one of the cases where it's helpful to use the newer version's pg_dump. Solution: pg_dump should quote *all* column-names, no matter if they are keywords or not. That was considered and rejected long ago. Readability of the dump script is something that we put a nonzero value on. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Hartmut Goebel h.goe...@goebel-consult.de wrote: I dumped with the executable form 8.3. That's not expected to work for an upgrade to 8.4. 8.4 did not allow accessing the 8.3 database What do you mean? (What did you try and what happened?) -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Am 03.06.2010 15:43, schrieb Kevin Grittner: Note that the documentation recommends always running pg_dump using the executable from the target version, not the source version. Are you using the pg_dump executable from 8.4? I dumped with the executable form 8.3. 8.4 did not allow accessing the 8.3 database, thus I needed to dump using the 8.3 executable. -- Schönen Gruß - Regards Hartmut Goebel Dipl.-Informatiker (univ.), CISSP, CSSLP Goebel Consult Spezialist für IT-Sicherheit in komplexen Umgebungen http://www.goebel-consult.de Monatliche Kolumne: http://www.cissp-gefluester.de/ Goebel Consult mit Mitglied bei http://www.7-it.de smime.p7s Description: S/MIME Cryptographic Signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Am 03.06.2010 16:16, schrieb Kevin Grittner: 8.4 did not allow accessing the 8.3 database What do you mean? (What did you try and what happened?) If upgraded the rpm-packages from 8.3 to 8.4. Then postgres failed starting (something like Database version mismatch). So I downgraded to 8.3, pg_dump'ed there, upgraded and pg_restore'd. Since 8.4 was not willed to work in the 8.3 database files, I expected this being a correct upgrade path. -- Schönen Gruß - Regards Hartmut Goebel smime.p7s Description: S/MIME Cryptographic Signature
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Hartmut Goebel h.goe...@goebel-consult.de wrote: If upgraded the rpm-packages from 8.3 to 8.4. Then postgres failed starting (something like Database version mismatch). You need to be running the old server using 8.3 software and while using pg_dump from 8.4 software. Does your packager provide some way to install the new version at a different location? If not, is there a separate machine on which you could install 8.4? -Kevin -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
Kevin Grittner kevin.gritt...@wicourts.gov writes: Hartmut Goebel h.goe...@goebel-consult.de wrote: If upgraded the rpm-packages from 8.3 to 8.4. Then postgres failed starting (something like Database version mismatch). You need to be running the old server using 8.3 software and while using pg_dump from 8.4 software. Does your packager provide some way to install the new version at a different location? If not, is there a separate machine on which you could install 8.4? In practice, if he has to redo the dump, the easiest fix is really going to be to rename the column beforehand. He's likely to end up doing that anyway rather than quoting its name forever ... regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5488: pg_dump does not quote column names - pg_restore may fail when upgrading
On Thu, Jun 03, 2010 at 06:04:16PM +0200, Hartmut Goebel wrote: If upgraded the rpm-packages from 8.3 to 8.4. Then postgres failed starting (something like Database version mismatch). So I downgraded to 8.3, pg_dump'ed there, upgraded and pg_restore'd. pg_dump will complain if its version doesn't match the server version, but that's neither a bug nor, in this case, a bad thing. You have pg_dump's -i option which will squelch this message and allow pg_dump to continue. -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
