Re: Upgrading 7.2.4 to 7.4.1 - Mailing list pgsql-admin

I don't know of any complete list, but I'm sure that there are some clues in
the changelogs.

In the meantime... I'd like to throw in one "gotcha" that set me back some time
when I recently upgraded from 7.2 to 7.4 : You could never use aggregates in
where clauses before. That's what HAVING is for. However, you used to be able
to use aggregates in subselects in WHERE clauses. That won't work anymore. :)
In every single case where this was a problem I was able to rewrite the query
to achieve the same results. If I had written it properly in the first place, I
wouldn't have had the problem at all.

Plpgsql seems to be more restrictive about typecasts and reserved keywords. I
didn't spend much time reseqarching how it is more restrictive. I hastily added
some explicit casting and changed some variable names in some of my frunctions
to get them to work.

I didn't have any problem with schema, but I just have the one public schema. I
dumped my data, as I was instructed to by the PgGurus, with the pg_dump from
7.4. That may be the key to your dumping woes... My only complaint, and this is
on the low-priority list to be fixed, is that functions are not dumped in
dependant order. So, I dumped the database schema seperately from the data, and
inserted it several times until all my dependancies were satisfied. Then I
inserted the data and then ran contrib/adddepend to add the missing sequence
and index dependancies.

The pg_atoi problems and the automapping of ''::int4 to 0 will be a pain to
fix. I vaguely remember being given a warning of this problem about the time
that 7.2 was released. The PgGurus said to make the changes now, for they'll
get you when you want to upgrade to 7.3.

If you wanted to begin the rewrite process in 7.2, you could develop a
procedure of altering the SQL in your application's code, then ALTER TABLE ...
ALTER COLUMN ... SET DEFAULT=0; UPDATE TABLE ... SET ... = 0 WHERE ... = '';
for each table affected. The "current_timestamp + 'n days'::interval" could be
addressed the same way. I think the "somevalue/7 + 1" can be addressed by
better SQL using some explicit casting a la "somevalue/7 + 1::float" (best
way), or the creation of your own operator (IMO, a band-aid that should be only
used until method 1 can be implimented).

CG

--- Rigmor Ukuhe <rigmor.ukuhe@finestmedia.com> wrote:
> > I've got an old 7.2.4 DB which I hoped to upgrade to 7.4.1.
> > The old syntax used for creating tables had lines like
> > ..
> > id integer DEFAULT '',
> >
> > which translates to this error:
> >
> > pg_restore: [archiver (db)] could not execute query: ERROR:  invalid
> > input syntax for integer: ""
> >
> > I found out that integer with '' as default is not acceptable after 7.3.
> >   Also, some complaints were given with the data, particulary numerous
> > "\N" lines when restoring (this can be worked out by using -d switch
> > when dumping).
> >
> > Also, schemas are not going to be created (some 7.3 documents
> > gave this up).
> >
> > Thus, must I update every DB creation script by hand and try to restore
> > only the data?
>
> Not only dbase create scripts, but most likly you have to do serious app
> level reviews to get things working.
> As you mentioned, if you used stuff like: WHERE intField = '' (in 7.2.4 ''
> was converted to 0 (zero), in 7.4.1 you get pg_atoi error) etc. you are in
> trouble. There is also other things, for example in our application, we used
> statements like:
> now() + intValue (in 7.2.4 it added given amount of days to timestamp), in
> 7.4.1 it is not permitted anymore.
> And in 7.2.4 statements like: somevalue/7 + 1 worked, IIRC in 7.4.1 it
> complains about "no operator float + integer" or something like that.
>
> We had to stop migration to 7.4.1 because of all this, so if anyone knows
> how to do it relatively fast or/and painless, or knows where is complete
> list of this kind of changes/gotchas, please let me know.
>
> Rigmor Ukuhe
>
>
> >
> >
> > Regards,
> > Jarmo
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 8: explain analyze is your friend
> > ---
> > Incoming mail is certified Virus Free.
> > Checked by AVG anti-virus system (http://www.grisoft.com).
> > Version: 6.0.564 / Virus Database: 356 - Release Date: 19.01.2004
> >
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.564 / Virus Database: 356 - Release Date: 19.01.2004
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org


__________________________________
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html