Author: Mark Stosberg
Date: 23-02-02 18:52
Here are some other things to consider with a Postgres 7.2 upgrade:
First, the "README" file mentions several incompatibilities with past versions. My first order of business was to check existing application code for these. In addition the ones listed in the "README" file, I also found that "interval" suffered the same problem as the "timestamp" function. This command goes most of the way to find most of those problems on my system, including checking for "= NULL", "'current'", "timestamp()", and "interval()". Grepping for the "limit" syntax change was harder. I'll leave that as an exercise for the reader.
locate .{pm,pl,cgi,sql} | grep '/home' | xargs egrep "= NULL|'current'|timestamp\s*\(|'interval\s*\("
For very large dump files, you may be interested in compressing and decompressing the the dump file on the fly as you handle. For example:
pg_dumpall | gzip > filename.dmp.gz
Here's a problem I ran into while importing, and my solution:
- problem: 'current' appears in dump from legacy applications
- solution: perl -pi.bak -e "s/'current'/CURRENT_TIMESTAMP/" file.dmp
For those who plan to upgrade to Postgres 7.2 at any point in the future, you can go ahead and start writing compatible code with these tips:
* Avoid "= NULL". Use "IS NULL" instead.
* Avoid interval() and timestamp() functions, as well as casting by using the "::" notation. Use
the SQL standard method instead:
CAST(expression AS type)
* When using "LIMIT" with an OFFSET, always use the syntax "LIMIT n OFFSET n"
* Also be on the lookout for: If an input string is larger than the field size, Postgres will return an error in 7.2, instead of silently truncating as before.
|
|
