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(dot)goebel(at)goebel-consult(dot)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.

"Hartmut Goebel" <h(dot)goebel(at)goebel-consult(dot)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

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

"Hartmut Goebel" <h(dot)goebel(at)goebel-consult(dot)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

>Hartmut Goebel <h(dot)goebel(at)goebel-consult(dot)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

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

Hartmut Goebel <h(dot)goebel(at)goebel-consult(dot)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

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Hartmut Goebel <h(dot)goebel(at)goebel-consult(dot)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

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

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

Am 03.06.2010 20:07, schrieb Tom Lane:
> "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
>> Hartmut Goebel <h(dot)goebel(at)goebel-consult(dot)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

* Hartmut Goebel (h(dot)goebel(at)goebel-consult(dot)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

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

* Hartmut Goebel (h(dot)goebel(at)goebel-consult(dot)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

Am 04.06.2010 14:57, schrieb Stephen Frost:
> * Hartmut Goebel (h(dot)goebel(at)goebel-consult(dot)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

Hartmut Goebel <h(dot)goebel(at)goebel-consult(dot)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

Kevin Grittner wrote:
> Hartmut Goebel <h(dot)goebel(at)goebel-consult(dot)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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

On Fri, Jun 04, 2010 at 02:59:48PM -0400, Bruce Momjian wrote:
> Kevin Grittner wrote:
> > Hartmut Goebel <h(dot)goebel(at)goebel-consult(dot)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 <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)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

David Fetter <david(at)fetter(dot)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

David Fetter wrote:
> On Fri, Jun 04, 2010 at 02:59:48PM -0400, Bruce Momjian wrote:
> > Kevin Grittner wrote:
> > > Hartmut Goebel <h(dot)goebel(at)goebel-consult(dot)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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

--On 4. Juni 2010 15:19:42 -0400 Bruce Momjian <bruce(at)momjian(dot)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

Bernd Helmle <mailings(at)oopsware(dot)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

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 <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)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

On Jun 5, 2010, at 4:02 PM, Dimitri Fontaine <dfontaine(at)hi-media(dot)com>
wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)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

Robert Haas <robertmhaas(at)gmail(dot)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

On Sun, Jun 6, 2010 at 2:53 PM, Dimitri Fontaine <dfontaine(at)hi-media(dot)com> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)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

Robert Haas <robertmhaas(at)gmail(dot)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

Robert Haas wrote:
> On Sun, Jun 6, 2010 at 2:53 PM, Dimitri Fontaine <dfontaine(at)hi-media(dot)com> wrote:
> > Robert Haas <robertmhaas(at)gmail(dot)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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

On Wed, Jun 9, 2010 at 9:10 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Robert Haas wrote:
>> On Sun, Jun 6, 2010 at 2:53 PM, Dimitri Fontaine <dfontaine(at)hi-media(dot)com> wrote:
>> > Robert Haas <robertmhaas(at)gmail(dot)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

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 <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

On Wed, Jun 9, 2010 at 9:35 PM, Bruce Momjian <bruce(at)momjian(dot)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

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

Am 05.06.2010 22:02, schrieb Dimitri Fontaine:
> Alvaro Herrera <alvherre(at)commandprompt(dot)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

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

Bruce,

* Bruce Momjian (bruce(at)momjian(dot)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

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

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

On Thu, Jun 10, 2010 at 9:02 AM, Stefan Kaltenbrunner
<stefan(at)kaltenbrunner(dot)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

Robert Haas wrote:
> On Thu, Jun 10, 2010 at 9:02 AM, Stefan Kaltenbrunner
> <stefan(at)kaltenbrunner(dot)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

On Thu, Jun 10, 2010 at 15:35, Stefan Kaltenbrunner
<stefan(at)kaltenbrunner(dot)cc> wrote:
> Robert Haas wrote:
>>
>> On Thu, Jun 10, 2010 at 9:02 AM, Stefan Kaltenbrunner
>> <stefan(at)kaltenbrunner(dot)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/

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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

Magnus Hagander wrote:
> On Thu, Jun 10, 2010 at 15:35, Stefan Kaltenbrunner
> <stefan(at)kaltenbrunner(dot)cc> wrote:
> > Robert Haas wrote:
> >>
> >> On Thu, Jun 10, 2010 at 9:02 AM, Stefan Kaltenbrunner
> >> <stefan(at)kaltenbrunner(dot)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 <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

On Thu, Jun 10, 2010 at 9:35 AM, Stefan Kaltenbrunner
<stefan(at)kaltenbrunner(dot)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.

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. :-)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

Robert Haas wrote:
> On Thu, Jun 10, 2010 at 9:35 AM, Stefan Kaltenbrunner
> <stefan(at)kaltenbrunner(dot)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

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

* Magnus Hagander (magnus(at)hagander(dot)net) wrote:
> On Thu, Jun 10, 2010 at 15:35, Stefan Kaltenbrunner
> <stefan(at)kaltenbrunner(dot)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

Bruce Momjian <bruce(at)momjian(dot)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

Stephen Frost wrote:
> * Magnus Hagander (magnus(at)hagander(dot)net) wrote:
>> On Thu, Jun 10, 2010 at 15:35, Stefan Kaltenbrunner
>> <stefan(at)kaltenbrunner(dot)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 30000 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

Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)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 30000 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

* Bruce Momjian (bruce(at)momjian(dot)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

Bruce,

* Bruce Momjian (bruce(at)momjian(dot)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

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)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 30000 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

Stephen Frost <sfrost(at)snowman(dot)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

Stephen Frost <sfrost(at)snowman(dot)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

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Stephen Frost <sfrost(at)snowman(dot)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

Stephen Frost <sfrost(at)snowman(dot)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" ;-)

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 <alvherre(at)commandprompt(dot)com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

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

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)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

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

Tom Lane wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)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

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)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

* Stefan Kaltenbrunner (stefan(at)kaltenbrunner(dot)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

Stephen Frost <sfrost(at)snowman(dot)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

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)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

On Thu, Jun 10, 2010 at 10:25 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Bruce Momjian <bruce(at)momjian(dot)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

On 2010-06-10, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Thu, Jun 10, 2010 at 9:02 AM, Stefan Kaltenbrunner
><stefan(at)kaltenbrunner(dot)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.

Don't edit manually, use sed instead.

On 2010-06-10, Stefan Kaltenbrunner <stefan(at)kaltenbrunner(dot)cc> wrote:
> Tom Lane wrote:
>> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)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.

You can extract those parts from a schema-only (or full) dump using sed

or you can just edit the schema-only dump and insert

\i datadump.sql

in the apropriate spot.

Top posting, sorry for that.

--
dim

Le 10 juin 2010 à 03:40, Robert Haas <robertmhaas(at)gmail(dot)com> a écrit :

> On Wed, Jun 9, 2010 at 9:35 PM, Bruce Momjian <bruce(at)momjian(dot)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.
>

On Fri, Jun 11, 2010 at 2:46 PM, Dimitri Fontaine
<dfontaine(at)hi-media(dot)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

Stephen Frost <sfrost(at)snowman(dot)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

Robert Haas <robertmhaas(at)gmail(dot)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

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