Обсуждение: Postgres 8.0 upgrade to 9.0

On Mon, Aug 1, 2011 at 9:12 AM, Atul Goel <Atul.Goel@iggroup.com> 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

>
> 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
providemore details as to why you have this need) you will be more likely to get a detailed answer. 

David J.

On Mon, Aug 1, 2011 at 1:51 PM, Gianpiero Venditti <stratio@hotmail.it> 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

On Mon, Aug 1, 2011 at 8:12 AM, Atul Goel <Atul.Goel@iggroup.com> 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.

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

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