Hi Forum,

We are planning to upgrade a postgres 8.0 database to postgres 9.0 (Actually already done in Dev). The application is J2EE application with Hibernate. My question are

1) Is there a list of things that needs to be taken care while upgrading(known issues).

2) Do I need to upgrade JDBC driver when I upgrade to postgres9.0.

3) If we upgrade JDBC driver will we require to upgrade hibernate dialect?

4) Any other suggestions.

Regards,
Atul Goel

________________________________
The information contained in this email is strictly confidential and for the use of the addressee only, unless otherwise indicated. If you are not the intended recipient, please do not read, copy, use or disclose to others this message or any attachment. Please also notify the sender by replying to this email or by telephone (+44 (0)20 7896 0011) and then delete the email and any copies of it. Opinions, conclusions (etc) that do not relate to the official business of this company shall be understood as neither given nor endorsed by it. IG Group Holdings plc is a company registered in England and Wales under number 01190902. VAT registration number 761 2978 07. Registered Office: Cannon Bridge House, 25 Dowgate Hill, London EC4R 2YA. Authorised and regulated by the Financial Services Authority. FSA Register number 114059.

Search Discussions

  • Atul Goel at Aug 1, 2011 at 3:17 pm
    Hi Forum,

    We are planning to upgrade a postgres 8.0 database to postgres 9.0 (Actually already done in Dev). The application is J2EE application with Hibernate. My question are

    1) Is there a list of things that needs to be taken care while upgrading(known issues).

    2) Do I need to upgrade JDBC driver when I upgrade to postgres9.0.

    3) If we upgrade JDBC driver will we require to upgrade hibernate dialect?

    4) Any other suggestions.

    Regards,
    Atul Goel

    ________________________________
    The information contained in this email is strictly confidential and for the use of the addressee only, unless otherwise indicated. If you are not the intended recipient, please do not read, copy, use or disclose to others this message or any attachment. Please also notify the sender by replying to this email or by telephone (+44 (0)20 7896 0011) and then delete the email and any copies of it. Opinions, conclusions (etc) that do not relate to the official business of this company shall be understood as neither given nor endorsed by it. IG Group Holdings plc is a company registered in England and Wales under number 01190902. VAT registration number 761 2978 07. Registered Office: Cannon Bridge House, 25 Dowgate Hill, London EC4R 2YA. Authorised and regulated by the Financial Services Authority. FSA Register number 114059.
  • Hannes Erven at Aug 1, 2011 at 7:22 pm
    Atul,

    2) Do I need to upgrade JDBC driver when I upgrade to postgres9.0.
    Yes, at least if you use BLOB types. The 9.0 server sends them in a
    format former JDBC drivers cannot understand:
    http://www.postgresql.org/docs/9.0/static/runtime-config-client.html#GUC-BYTEA-OUTPUT

    Other than that I did not experience issues, but the application I
    support was upgraded from only 8.3 to 9.0 (and is running now on 9.0 for
    many months flawlessly).


    Best regards,

    -hannes
  • Craig Ringer at Aug 1, 2011 at 11:42 pm

    On 1/08/2011 11:07 PM, Atul Goel wrote:
    Hi Forum,

    We are planning to upgrade a postgres 8.0 database to postgres 9.0
    (Actually already done in Dev). The application is J2EE application with
    Hibernate. My question are

    1)Is there a list of things that needs to be taken care while
    upgrading(known issues).
    Most of them will be taken care of for you if you update Hibernate at
    the same time, because the new dialect knows about the changed casting
    rules, etc.

    You may not need to update it at all, as Hibernate uses pretty simple
    SQL most of the time.
    2)Do I need to upgrade JDBC driver when I upgrade to postgres9.0.
    It is very strongly recommended that you do so. The old JDBC driver may
    not understand the metadata from newer versions of the server properly,
    and will have problems with bytea data.
    3)If we upgrade JDBC driver will we require to upgrade hibernate dialect?
    Not as far as I know. Personally I would be updating Hibernate as well,
    but I realize you may not have that option.
    4)Any other suggestions.
    Test. Lots. You're making a *HUGE* version jump, rather than doing small
    incremental updates, so you've got more potential pitfalls. If - as it
    sounds like - you're not updating the rest of your app at the same time,
    you're adding the complexity of mixing old and new versions of software.

    If the old version of Hibernate you're using has a bug in its dialects -
    or core - that is only triggered on newer versions of Pg, or if it
    relies on SQL that used to be accepted by old versions of Pg but is now
    rejected (say: implicit casts to text) then you'll get query errors. I'd
    want to update Hibernate at the same time so I got the benefit of all
    the bug fixes between your version and the new one, and got a version
    that will've been tested against current Pg versions.

    --
    Craig Ringer

    POST Newspapers
    276 Onslow Rd, Shenton Park
    Ph: 08 9381 3088 Fax: 08 9388 2258
    ABN: 50 008 917 717
    http://www.postnewspapers.com.au/
  • Jaime Casanova at Aug 1, 2011 at 4:03 pm

    On Mon, Aug 1, 2011 at 9:12 AM, Atul Goel wrote:
    Hi Forum,

    We are planning to upgrade a postgres 8.0 database to postgres 9.0 (Actually
    already done in Dev).
    consider that 9.0 is not the next version after 8.0, there were 4 more
    (8.1, 8.2, 8.3 and 8.4) and at least for changing from 8.2 to 8.3 you
    probably will need to fix your app
    The application is J2EE application with Hibernate. My
    question are

    1)      Is there a list of things that needs to be taken care while
    upgrading(known issues).
    they are all mentioned in realese notes... look the "Migration to
    Version X.X" in the release notes for the above mentioned versions
    2)      Do I need to upgrade JDBC driver when I upgrade to postgres9.0.
    probably but i'm not so sure about it

    --
    Jaime Casanova         www.2ndQuadrant.com
    Professional PostgreSQL: Soporte 24x7 y capacitación
  • John Cheng at Aug 1, 2011 at 5:50 pm
    I am planning on bringing our 8.3 installation up to 9.0.4. First I upgraded
    the jdbc driver on our staging environment, after 1 month on staging, we
    tested with the 9.0 driver on production. The actual database upgrade will
    be more complicated, and we are going to simulate an upgrade on a
    non-production environment first to help anticipate problems.

    Going from 8.0 to 9.0 could be a lot more complicated. But I'd recommend
    planning to upgrade pieces incrementally and of course always test before
    pushing to production.

    On Mon, Aug 1, 2011 at 9:03 AM, Jaime Casanova wrote:
    On Mon, Aug 1, 2011 at 9:12 AM, Atul Goel wrote:
    Hi Forum,

    We are planning to upgrade a postgres 8.0 database to postgres 9.0 (Actually
    already done in Dev).
    consider that 9.0 is not the next version after 8.0, there were 4 more
    (8.1, 8.2, 8.3 and 8.4) and at least for changing from 8.2 to 8.3 you
    probably will need to fix your app
    The application is J2EE application with Hibernate. My
    question are

    1) Is there a list of things that needs to be taken care while
    upgrading(known issues).
    they are all mentioned in realese notes... look the "Migration to
    Version X.X" in the release notes for the above mentioned versions
    2) Do I need to upgrade JDBC driver when I upgrade to postgres9.0.
    probably but i'm not so sure about it

    --
    Jaime Casanova www.2ndQuadrant.com
    Professional PostgreSQL: Soporte 24x7 y capacitación

    --
    Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-general


    --
    ---
    John L Cheng
  • Gianpiero Venditti at Aug 1, 2011 at 6:56 pm
    Hello, I need to write a function that sometimes return a row with only a column and sometimes return a row with two columns.

    Is it possible to do something like this with record type?

    If it's not what's the best alternative to achieve such a result?

    Thanks in advance for your help.


    Gianpiero Venditti
  • David Johnston at Aug 1, 2011 at 7:12 pm

    Is it possible to do something like this with record type?
    Yes.

    The docs, while possibly somewhat confusing, cover this need. If you have a more specific question (and possibly provide more details as to why you have this need) you will be more likely to get a detailed answer.

    David J.
  • Merlin Moncure at Aug 1, 2011 at 7:27 pm

    On Mon, Aug 1, 2011 at 1:51 PM, Gianpiero Venditti wrote:
    Hello, I need to write a function that sometimes return a row with only a
    column and sometimes return a row with two columns.

    Is it possible to do something like this with record type?

    If it's not what's the best alternative to achieve such a result?
    you can do this, but it's usually more trouble than worth:
    create function test(b bool) returns setof record as
    $$
    begin
    if b then
    return query select 1,2;
    else
    return query select 1,2,3;
    end if;
    end;
    $$ language plpgsql;

    postgres=# select * from test(true) V(a int, b int);
    a | b
    ---+---
    1 | 2
    (1 row)

    Time: 26.108 ms
    postgres=# select * from test(false) V(a int, b int, c int);
    a | b | c
    ---+---+---
    1 | 2 | 3
    (1 row)

    Doing this is bending the rules of what functions are reasonably
    allowed to do and forces some unpleasant syntax outside of the
    function call. If it is in any way sane to do so, I'd highly doing
    the two argument version always and returning null (or an additional
    flag) when you would be wanting to call the one column returning
    version. Just because a function returns two columns, there is no
    reason why you must inspect or even fetch all the columns they return:

    select col1 from func();
    select col1, col2 from func();

    merlin
  • Gianpiero Venditti at Aug 2, 2011 at 7:34 am
    First of all thanks for the quick replies, i'll describe my problem more in detail.

    I'm using postgress with the latest release of GNU Gatekeeper.

    More specifically I need a query that returns a row with exactly a single column (a text) in one case and a row with exactly two columns (a text and an integer) in the other case.

    So i can't just return everytime two columns with one null sometimes. I know this is a little strange but it's a constraint of the Gatekeeper so i have no choiche :(

    This is the way i call the query from inside the gatekeeper:

    select * from routing ( param1, param2, param3, param4 ) as ( result text, reason integer );

    is there a way to make the reason optional?


    Thanks.

    Gianpiero Venditti
  • John R Pierce at Aug 2, 2011 at 7:46 am

    On 08/02/11 12:29 AM, Gianpiero Venditti wrote:
    First of all thanks for the quick replies, i'll describe my problem
    more in detail.

    I'm using postgress with the latest release of GNU Gatekeeper.

    More specifically I need a query that returns a row with exactly a
    single column (a text) in one case and a row with exactly two columns
    (a text and an integer) in the other case.

    So i can't just return everytime two columns with one null sometimes.
    I know this is a little strange but it's a constraint of the
    Gatekeeper so i have no choiche :(

    This is the way i call the query from inside the gatekeeper:

    select * from routing ( param1, param2, param3, param4 ) as ( result
    text, reason integer );

    is there a way to make the reason optional?
    that should be two separate queries.

    "select *" shouldn't really be made from application code in the first
    place, its a convenience when you're manually querying something, but
    queries from a program should name the fields they expect so they aren't
    dependent on the table definition and field order.





    --
    john r pierce N 37, W 122
    santa cruz ca mid-left coast
  • Scott Marlowe at Aug 2, 2011 at 1:44 am

    On Mon, Aug 1, 2011 at 8:12 AM, Atul Goel wrote:
    Hi Forum,

    We are planning to upgrade a postgres 8.0 database to postgres 9.0 (Actually
    already done in Dev). The application is J2EE application with Hibernate. My
    question are

    1)      Is there a list of things that needs to be taken care while
    upgrading(known issues).
    The single greatest issue is that implicit casts have been removed.
    This means you can't do things like use substring on a date type etc
    directly or match an int to a varchar in a join without an explicit
    cast of one to the other.
  • Ognjen Blagojevic at Aug 2, 2011 at 9:30 am
    Hi Atul,
    On 1.8.2011 16:12, Atul Goel wrote:
    We are planning to upgrade a postgres 8.0 database to postgres 9.0
    (Actually already done in Dev). The application is J2EE application with
    Hibernate. My question are

    1)Is there a list of things that needs to be taken care while
    upgrading(known issues).
    In past two years, we migrated JavaEE and Java Spring applications from
    Postgres 8.1 -> 8.3 -> 8.4 -> 9.0, only bigger issue was, as Scott
    already mentioned, removal of explicit casts.

    Please read:

    http://www.postgresql.org/docs/8.3/static/release-8-3.html,
    especially section E.16.2.1.

    Also note, that postgresql.conf parameters are sligtly changed, so you
    shoud also pay attention there. For instance, max_fsm_pages is obsolete
    since 8.4.

    2)Do I need to upgrade JDBC driver when I upgrade to postgres9.0.
    Absolutely. Using older driver on new Postgres DB could bring you
    unexpected results (e.g. we got corrupted metadata, bytea colums were
    encoded badly, and so on).


    -Ognjen

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedAug 1, '11 at 2:22p
activeAug 2, '11 at 9:30a
posts13
users11
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase