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
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
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. 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* 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 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 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 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 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
Am 05.06.2010 22:02, schrieb Dimitri Fontaine: > Alvaro Herrera 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 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 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
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 03.06.2010 20:07, schrieb Tom Lane: > "Kevin Grittner" writes: >> Hartmut Goebel 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
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 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
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
[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
