Fwd: upgrading from V8.3.4 to V9.2.4

We are going to be upgrading our postgres version from 8.3.4 to 9.2.4 in
the near future.

Can anyone give me a short list of "gotchas" concerning problems we
might step into?

We have a number of functions written in PL/pgSQL. Will they work in
9.2.4 without modification?

Thanks in advance to the Community for your help.

Paul TIlles

Paul Tilles <paul(dot)tilles(at)noaa(dot)gov> writes:
> We are going to be upgrading our postgres version from 8.3.4 to 9.2.4 in
> the near future.

> Can anyone give me a short list of "gotchas" concerning problems we
> might step into?

You'll need to review the release notes for 8.4, 9.0, 9.1, and 9.2
to see the compatibility gotchas for each of those releases
(see the "Migration to Version X.X" heading in each major release).

http://www.postgresql.org/docs/9.2/static/release.html

regards, tom lane

On 04/03/2013 10:14 AM, Paul Tilles wrote:
> We are going to be upgrading our postgres version from 8.3.4 to 9.2.4 in
> the near future.
>
> Can anyone give me a short list of "gotchas" concerning problems we
> might step into?
>
> We have a number of functions written in PL/pgSQL. Will they work in
> 9.2.4 without modification?

Starting at 8.3.x means you have cleared the type casting changes. The
issue I remember moving functions from 8.3 --> had to do with the
changes in the string escaping, see here for a start:

http://www.postgresql.org/docs/9.2/interactive/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE

>
> Thanks in advance to the Community for your help.
>
> Paul TIlles
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

On Wed, Apr 3, 2013 at 10:02 PM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com> wrote:
> On 04/03/2013 10:14 AM, Paul Tilles wrote:
>>
>> We are going to be upgrading our postgres version from 8.3.4 to 9.2.4 in
>> the near future.
>>
>> Can anyone give me a short list of "gotchas" concerning problems we
>> might step into?
>>
>> We have a number of functions written in PL/pgSQL. Will they work in
>> 9.2.4 without modification?
>
>
> Starting at 8.3.x means you have cleared the type casting changes. The issue
> I remember moving functions from 8.3 --> had to do with the changes in the
> string escaping, see here for a start:
>
> http://www.postgresql.org/docs/9.2/interactive/sql-syntax-lexical.html#SQL-SYNTAX-STRINGS-ESCAPE
>
>

Yeah, there were also some subtle breakage around keywords used as
variable naming when plpgsql was port to use the core lexer. Ideally
you'll have some kind of test suite / regression you can run to verify
all of this; if not you maybe you can set up some replication between
old/new servers (we use mimeo for that when sever versions are this
far apart) and point your app to both and see what happens.

Robert Treat
conjecture: xzilla.net
consulting: omniti.com

Robert Treat <rob(at)xzilla(dot)net> wrote:

> Yeah, there were also some subtle breakage around keywords used
> as variable naming when plpgsql was port to use the core lexer.

One more: from a Java client access to bytea columns will break if
you don't also use the latest JDBC driver jar.

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Beside all notes, i recommend you to use pg_upgrade, to avoid a complete
backup/restore in your transition.

http://www.postgresql.org/docs/9.2/static/pgupgrade.html

On Fri, Apr 5, 2013 at 1:30 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:

> Robert Treat <rob(at)xzilla(dot)net> wrote:
>
> > Yeah, there were also some subtle breakage around keywords used
> > as variable naming when plpgsql was port to use the core lexer.
>
> One more: from a Java client access to bytea columns will break if
> you don't also use the latest JDBC driver jar.
>
> --
> Kevin Grittner
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Forgot to include the list! Sorry!
If you have a non-standard socket file location pg_upgrade will not work
for this upgrade.

On Fri, Apr 5, 2013 at 9:37 AM, Leonardo Carneiro <chesterman86(at)gmail(dot)com>wrote:

> Beside all notes, i recommend you to use pg_upgrade, to avoid a complete
> backup/restore in your transition.
>
> http://www.postgresql.org/docs/9.2/static/pgupgrade.html
>
>
> On Fri, Apr 5, 2013 at 1:30 PM, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
>
>> Robert Treat <rob(at)xzilla(dot)net> wrote:
>>
>> > Yeah, there were also some subtle breakage around keywords used
>> > as variable naming when plpgsql was port to use the core lexer.
>>
>> One more: from a Java client access to bytea columns will break if
>> you don't also use the latest JDBC driver jar.
>>
>> --
>> Kevin Grittner
>> EnterpriseDB: http://www.enterprisedb.com
>> The Enterprise PostgreSQL Company
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>
>