Thread: Avoiding upgrade backlash

From:
Josh Berkus
Date:

Hackers,

I'm seeing from reports on IRC that 8.3 may cause an unprecedented number
of upgrade issues for users who have old, badly-written applications.
Stefan K, for example, was reporting the necessity of re-writing 3000
lines of his PL/pgSQL code on an older app (about 10%).

While we are right to choke down abuse of implicit casting, it might be a
good idea to give users stronger warnings and instructions.

I'm thinking that we need to warn everyone about:
1) They need to use 8.3's pg_dump, not the old version, to upgrade (this is
always true but now doing it wrong will break a lot more users).
2) They need to check for bugs
3) If Robert gets his type-cast backport package together, the location of
that.

I'm thinking this warning should go (prominently) into the release notes,
the community notice, and the extended web release.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

From:
Tom Lane
Date:

Josh Berkus <> writes:
> While we are right to choke down abuse of implicit casting, it might be a
> good idea to give users stronger warnings and instructions.

> I'm thinking that we need to warn everyone about:
> 1) They need to use 8.3's pg_dump, not the old version, to upgrade (this is
> always true but now doing it wrong will break a lot more users).

Uh, what makes you think that will make any difference?

> 3) If Robert gets his type-cast backport package together, the location of
> that.

The very *last* thing I want to see is encouragement of people trying to
undo that change.

            regards, tom lane

From:
Andrew Sullivan
Date:

On Mon, Nov 12, 2007 at 02:42:30PM -0800, Josh Berkus wrote:
>
> While we are right to choke down abuse of implicit casting, it might be a
> good idea to give users stronger warnings and instructions.

I think this is true.  I think it maybe ought to be right at the top of the
notes, and maybe ought to be linked off the announcement of the sort

    Users upgrading from previous releases want to read this <a etc>
    compatibility note</a>

I think maybe how Debian handled the notes about kernel changes in the last
stable release would be a good model.  The point there is, _test well_.
We're doing what we warned you about.

> I'm thinking that we need to warn everyone about:
[&c]

This list seems to miss the main warning needed, which is this in 300 point
bold:

IMPLICIT TYPECASTS HAVE BEEN MOSTLY REMOVED.  TEST YOUR APPLICATION!

A
--
Andrew Sullivan
Old sigs will return after re-constitution of blue smoke

From:
Gregory Stark
Date:

"Andrew Sullivan" <> writes:

> IMPLICIT TYPECASTS HAVE BEEN MOSTLY REMOVED.  TEST YOUR APPLICATION!

I think you have to say that without using the technical gobbledygook
"implicit casts".

Something like:

Non-strings are no longer converted automatically to strings when used in
places where strings are expected. You must now explicitly cast non-strings to
strings if you want to apply string operations on them.

--
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!

From:
Bruce Momjian
Date:

Gregory Stark wrote:
> "Andrew Sullivan" <> writes:
>
> > IMPLICIT TYPECASTS HAVE BEEN MOSTLY REMOVED.  TEST YOUR APPLICATION!
>
> I think you have to say that without using the technical gobbledygook
> "implicit casts".
>
> Something like:
>
> Non-strings are no longer converted automatically to strings when used in
> places where strings are expected. You must now explicitly cast non-strings to
> strings if you want to apply string operations on them.

I think you need to show actual examples of problem queries.

--
  Bruce Momjian  <>        http://momjian.us
  EnterpriseDB                             http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

From:
Peter Eisentraut
Date:

Am Montag, 12. November 2007 schrieb Josh Berkus:
> I'm thinking that we need to warn everyone about:
> 1) They need to use 8.3's pg_dump, not the old version, to upgrade (this is
> always true but now doing it wrong will break a lot more users).

What difference would pg_dump make?

> 2) They need to check for bugs

What bugs?

> 3) If Robert gets his type-cast backport package together, the location of
> that.

Well, if you want to undo the changes, you don't need a backport package; you
can just change the cast's definition.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

From:
Stefan Kaltenbrunner
Date:

Peter Eisentraut wrote:
> Am Montag, 12. November 2007 schrieb Josh Berkus:
>> I'm thinking that we need to warn everyone about:
>> 1) They need to use 8.3's pg_dump, not the old version, to upgrade (this is
>> always true but now doing it wrong will break a lot more users).
>
> What difference would pg_dump make?

well the current problem is that either way pg_dump can generate dumps
that are not restorable without modification on 8.3 (like say one that
contains a VIEW that does a INTEGER = TEXT comparision which 8.3 will
refuse to create) even if dumped with 8.3.

>
>> 2) They need to check for bugs
>
> What bugs?

queries that depend on implict casts "just working" - most of those are
actually bug or maybe sloppy coding on the application side but it is
imho by FAR the most incompatible behaviour change we have done in the
last few releases.
I guess that the largest amount of these are TEXT/INTEGER casts and the
kind of app that will probably hit most are the ones that are
misbehaving anyway (think EAV style stuff) - but it is a incompatible
change nevertheless.

>
>> 3) If Robert gets his type-cast backport package together, the location of
>> that.
>
> Well, if you want to undo the changes, you don't need a backport package; you
> can just change the cast's definition.

well the point here is that we ought to be more open about the impact of
the change and i think that the release notes need to mention that (and
maybe also an example on how to change/re-add the casts)


Stefan

From:
Andrew Sullivan
Date:

On Mon, Nov 12, 2007 at 11:12:52PM -0500, Bruce Momjian wrote:
> I think you need to show actual examples of problem queries.

Good idea.

--
Andrew Sullivan
Old sigs will return after re-constitution of blue smoke

From:
"Leif B. Kristensen"
Date:

On Monday 12. November 2007, Josh Berkus wrote:
>I'm thinking that we need to warn everyone about:
>1) They need to use 8.3's pg_dump, not the old version, to upgrade

Isn't there a chicken-and-egg problem here? On my Gentoo system, I have
always been forced to uninstall the old version before upgrading to a
new version. In practice, that means that I have to do a full dump of
the databases with the current pg_dump, delete or rename the data
directory, and then restore after the upgrade.

I'd like to see a detailed howto on this ...
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

From:
Alvaro Herrera
Date:

Leif B. Kristensen wrote:
> On Monday 12. November 2007, Josh Berkus wrote:
> >I'm thinking that we need to warn everyone about:
> >1) They need to use 8.3's pg_dump, not the old version, to upgrade
>
> Isn't there a chicken-and-egg problem here? On my Gentoo system, I have
> always been forced to uninstall the old version before upgrading to a
> new version.

That's a Gentoo shortcoming, nothing more.

--
Alvaro Herrera                        http://www.advogato.org/person/alvherre
"Endurecerse, pero jamás perder la ternura" (E. Guevara)

From:
Josh Berkus
Date:

Peter,

> What difference would pg_dump make?

For many user's databases ... many more than any previous version after
7.3 ... using the old version's pg_dump *will not load* into 8.3.  Unless you
restore the casts.

> Well, if you want to undo the changes, you don't need a backport package;
> you can just change the cast's definition.

Right.  We've talked (on IRC) about putting together a package which restores
the removed casts.

Given that Gentoo, Red Hat and SuSE don't support having multiple PostgreSQL
versions on the system in their packaging, I'm thinking the "restore casts"
package is essential rather than being a nice idea.

Should I be discussing this on a different list?

--
Josh Berkus
PostgreSQL @ Sun
San Francisco

From:
Bernd Helmle
Date:

--On Dienstag, November 13, 2007 11:05:18 +0100 Stefan Kaltenbrunner
<> wrote:

>> What difference would pg_dump make?
>
> well the current problem is that either way pg_dump can generate dumps
> that are not restorable without modification on 8.3 (like say one that
> contains a VIEW that does a INTEGER = TEXT comparision which 8.3 will
> refuse to create) even if dumped with 8.3.

AFAIK pg_dump and its related dump routines takes care to dump this in a
proper way with explicit casts attached. Do you have an example showing a
possible misbehavior?

--
  Thanks

                    Bernd

From:
Tom Lane
Date:

Josh Berkus <> writes:
> Peter,
>> What difference would pg_dump make?

> For many user's databases ... many more than any previous version after
> 7.3 ... using the old version's pg_dump *will not load* into 8.3.  Unless you
> restore the casts.

Please provide a concrete example.

> Should I be discussing this on a different list?

If you are looking for code changes, neither docs nor advocacy are
suitable forums.

            regards, tom lane

From:
"Joshua D. Drake"
Date:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Tue, 13 Nov 2007 11:39:26 -0500
Tom Lane <> wrote:

> Josh Berkus <> writes:
> > Peter,
> >> What difference would pg_dump make?
> 
> > For many user's databases ... many more than any previous version
> > after 7.3 ... using the old version's pg_dump *will not load* into
> > 8.3.  Unless you restore the casts.
> 
> Please provide a concrete example.
> 
> > Should I be discussing this on a different list?
> 
> If you are looking for code changes, neither docs nor advocacy are
> suitable forums.

Josh wasn't looking for code changes. He was insuring that we were loud
about the rather significant problems that upgrading to 8.3, "may"
cause.

Joshua D. Drake

> 
>             regards, tom lane
> 
> ---------------------------(end of
> broadcast)--------------------------- TIP 4: Have you searched our
> list archives?
> 
>                http://archives.postgresql.org
> 


- -- 

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
            UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHOdWAATb/zqfZUUQRAjG4AJ9WZgxRABLBu/E6gid1t1Jg1pDxcQCfanaB
MNH1nlHU6lEO+z+IJ6mxLvE=
=2Tga
-----END PGP SIGNATURE-----

From:
Tom Lane
Date:

Peter Eisentraut <> writes:
> Am Montag, 12. November 2007 schrieb Josh Berkus:
>> 3) If Robert gets his type-cast backport package together, the location of
>> that.

> Well, if you want to undo the changes, you don't need a backport
> package; you can just change the cast's definition.

It's actually not going to be that easy, because most of those casts
aren't even in pg_cast anymore: they have been subsumed into the
CoerceViaIO mechanism.  You'd need to resurrect the individual cast
functions before you could put entries back, too.

Another little problem is that you're likely to break as much stuff as
you fix.  An example in CVS HEAD:

regression=# select 42 || 'foo';
 ?column?
----------
 42foo
(1 row)

regression=# select 42 like 'foo';
ERROR:  operator does not exist: integer ~~ unknown
LINE 1: select 42 like 'foo';
                  ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

OK, let's "fix" that by making int->text implicit again:

regression=# create function inttotext(int) returns text as $$
regression$# begin return $1; end$$ language plpgsql strict immutable;
CREATE FUNCTION
regression=# create cast (int as text) with function inttotext(int)
regression-# as implicit;
CREATE CAST

Now LIKE works:

regression=# select 42 like 'foo';
 ?column?
----------
 f
(1 row)

but || not so much:

regression=# select 42 || 'foo';
ERROR:  operator is not unique: integer || unknown
LINE 1: select 42 || 'foo';
                  ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

            regards, tom lane

From:
"Leif B. Kristensen"
Date:

On Tuesday 13. November 2007, Alvaro Herrera wrote:
>Leif B. Kristensen wrote:
>> On Monday 12. November 2007, Josh Berkus wrote:
>> >I'm thinking that we need to warn everyone about:
>> >1) They need to use 8.3's pg_dump, not the old version, to upgrade
>>
>> Isn't there a chicken-and-egg problem here? On my Gentoo system, I
>> have always been forced to uninstall the old version before
>> upgrading to a new version.
>
>That's a Gentoo shortcoming, nothing more.

I've started a thread about it in the Portage & Programming section of
forums.gentoo.org:

http://forums.gentoo.org/viewtopic-t-615494.html

I hope that someone more knowledgeable than me will be able to
contribute in bringing this issue to the attention of the Gentoo
developers, and hopefully work out a solution. Sure, there are other
Gentoo users than me around here?
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

From:
"Leif B. Kristensen"
Date:

On Wednesday 14. November 2007, Leif B. Kristensen wrote:
>On Tuesday 13. November 2007, Alvaro Herrera wrote:
>>Leif B. Kristensen wrote:
>>> On Monday 12. November 2007, Josh Berkus wrote:
>>> >I'm thinking that we need to warn everyone about:
>>> >1) They need to use 8.3's pg_dump, not the old version, to upgrade
>>>
>>> Isn't there a chicken-and-egg problem here? On my Gentoo system, I
>>> have always been forced to uninstall the old version before
>>> upgrading to a new version.
>>
>>That's a Gentoo shortcoming, nothing more.
>
>I've started a thread about it in the Portage & Programming section of
>forums.gentoo.org:
>
>http://forums.gentoo.org/viewtopic-t-615494.html
>
>I hope that someone more knowledgeable than me will be able to
>contribute in bringing this issue to the attention of the Gentoo
>developers, and hopefully work out a solution. Sure, there are other
>Gentoo users than me around here?

FYI, I've filed a bug report on bugs.gentoo.org:

"This is not quite a bug, but rather an approaching challenge. See this
thread on forums.gentoo.org
[http://forums.gentoo.org/viewtopic-t-615494.html], in which I was
asked to file a bug report. Concerning the upcoming PostgreSQL upgrade
from 8.2 to 8.3, it's imperative that Gentoo implements an adequate
transition procedure. Blocking the old version will create
a 'chicken-and-egg' situation where you are left incapable of upgrading
your existing databases. According to the developers, it's important
that a dump of the old database should be done with the new 8.3 version
of pg_dump. See this thread
[http://archives.postgresql.org/pgsql-advocacy/2007-11/msg00051.php] on
pgsql-advocacy for some details."

The bug id is [https://bugs.gentoo.org/show_bug.cgi?id=199142].
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

From:
Peter Eisentraut
Date:

Am Mittwoch, 14. November 2007 schrieb Leif B. Kristensen:
> >from 8.2 to 8.3, it's imperative that Gentoo implements an adequate
> transition procedure. Blocking the old version will create
> a 'chicken-and-egg' situation where you are left incapable of upgrading
> your existing databases. According to the developers, it's important
> that a dump of the old database should be done with the new 8.3 version
> of pg_dump.

Note that this is actually not true on both accounts.  Firstly, no developer
has ever claimed that, and second it's factually false.

It has always been "better" to use the newer pg_dump, but never "important" or
imperative.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

From:
"Leif B. Kristensen"
Date:

On Wednesday 14. November 2007, Peter Eisentraut wrote:
>Am Mittwoch, 14. November 2007 schrieb Leif B. Kristensen:
>> >from 8.2 to 8.3, it's imperative that Gentoo implements an adequate
>>
>> transition procedure. Blocking the old version will create
>> a 'chicken-and-egg' situation where you are left incapable of
>> upgrading your existing databases. According to the developers, it's
>> important that a dump of the old database should be done with the
>> new 8.3 version of pg_dump.
>
>Note that this is actually not true on both accounts.  Firstly, no
> developer has ever claimed that, and second it's factually false.
>
>It has always been "better" to use the newer pg_dump, but never
> "important" or imperative.

Here is what Josh said when he started this thread:

On Monday 12. November 2007, Josh Berkus wrote:
>I'm thinking that we need to warn everyone about:
>1) They need to use 8.3's pg_dump, not the old version, to upgrade

Repeat: "They need to use 8.3's pg_dump, not the old version, to
upgrade"

Do you mean to say that Josh is lying?
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

From:
Peter Eisentraut
Date:

Am Mittwoch, 14. November 2007 schrieb Leif B. Kristensen:
> Repeat: "They need to use 8.3's pg_dump, not the old version, to
> upgrade"
>
> Do you mean to say that Josh is lying?

Uh, well, at least his statements are not verified at this point.  Certainly
your statement from the Gentoo forum posting "The 8.2 pg_dump won't do
anything about those implicit casts, but the 8.3 version will take care of a
lot of potential problems with them." would be sheer magic and certainly
don't correspond to anything implemented in pg_dump.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

From:
"Leif B. Kristensen"
Date:

On Wednesday 14. November 2007, Peter Eisentraut wrote:

> Certainly your statement from the Gentoo forum posting "The 8.2
> pg_dump won't do anything about those implicit casts, but the 8.3
> version will take care of a lot of potential problems with them."
> would be sheer magic and certainly don't correspond to anything
> implemented in pg_dump.

That may not have been explicitly said in the current discussion, but it
has certainly been my impression that so is the case. My apologies if
this is an unwarranted assumption.

If somebody would dispel my ignorance and explain _why_ we should use
the 8.3 pg_dump, I'd be very thankful.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

From:
Greg Smith
Date:

On Wed, 14 Nov 2007, Leif B. Kristensen wrote:

> The bug id is [https://bugs.gentoo.org/show_bug.cgi?id=199142].

First off, they're already working on this problem.  Installing multiple
versions of PostgreSQL at the same time is called "slotting" by them, and
an outline of the project is at
http://overlays.gentoo.org/proj/postgresql/wiki/ExperimentalFAQ

What I expect will happen to you is that your bug will be "resolved" by
marking it a duplicate of https://bugs.gentoo.org/show_bug.cgi?id=42894
which, as you can see from its history, has been lingering around since
this class of problem was first reported during the 7.3->7.4 upgrade.
That was early in 2004 and I don't see any progress since about a year
ago, maybe somebody is still working on it.

A quick look through the PostgreSQL-related bug reports for Gentoo
suggests multiple serious issues that haven't been resolved in years;
another is https://bugs.gentoo.org/show_bug.cgi?id=44468 where the logging
interface is not quite right.  That one people keep reporting, no progress
has been made even though it's really simple to fix--far simpler than
getting support for multiple versions at once.

I wouldn't anticipate a quick response here; the resources Gentoo is
devoting to PostgreSQL appear minimal.  I'd suggest your time would be
better spent learning how to manually install PostgreSQL yourself rather
than expecting that the official packaging will fix this for you.  It's
not that difficult to do.

--
* Greg Smith  http://www.gregsmith.com Baltimore, MD

From:
Jon Sime
Date:

Leif B. Kristensen wrote:
> On Tuesday 13. November 2007, Alvaro Herrera wrote:
>> Leif B. Kristensen wrote:
>>> On Monday 12. November 2007, Josh Berkus wrote:
>>>> I'm thinking that we need to warn everyone about:
>>>> 1) They need to use 8.3's pg_dump, not the old version, to upgrade
>>> Isn't there a chicken-and-egg problem here? On my Gentoo system, I
>>> have always been forced to uninstall the old version before
>>> upgrading to a new version.
>> That's a Gentoo shortcoming, nothing more.
>
> I've started a thread about it in the Portage & Programming section of
> forums.gentoo.org:
>
> http://forums.gentoo.org/viewtopic-t-615494.html
>
> I hope that someone more knowledgeable than me will be able to
> contribute in bringing this issue to the attention of the Gentoo
> developers, and hopefully work out a solution. Sure, there are other
> Gentoo users than me around here?

Aye, there's at least one more (at least on my development machine).

I wanted to point out that Gentoo does already have a solid way of
handling this -- you just have to use an overlay. If you have "layman"
installed, check out the "postgresql-testing" and
"postgresql-experimental" overlays.

I use the latter and have 8.1.10, 8.2.5 and 8.3beta1 all running happily
on the same machine without having had to do anything other than add
that overlay and set each instance to use a different port. Each release
is very nicely slotted and they all get along just peachy.

-Jon

--
Senior Systems Developer
Media Matters for America
http://mediamatters.org/