The FreeBSD Diary

The FreeBSD Diary (TM)

Providing practical examples since 1998

If you buy from Amazon USA, please support us by using this link.

 New Topic  |  Go to Top  |  Go to Topic  |  Search  |  Log In   Newer Topic  |  Older Topic 
 Other things to consider with a Postgres 7.2 upgrade
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.

Reply To This Message
 Forum List  |  Threaded View   Newer Topic  |  Older Topic 

 Forum List  |  Need a Login? Register Here 
 User Login
 User Name:
 Remember my login:
 Forgot Your Password?
Enter your email address or user name below and a new password will be sent to the email address associated with your profile.
How to get the most out of the forum