Postgres Pro
Downloads
Home   >   mailing lists

Thread: From 8.1 to 8.3

From 8.1 to 8.3

From
S Arvind
Date:
Our company wants to move from 8,1 to 8.3 latest. In irc they told me to check realse notes for issues while upgrading. But there are lots of release notesss. Can anyone tell some most noticable change or place-of-error while upgrading?

Arvind S


"Many of lifes failure are people who did not realize how close they were to success when they gave up."
-Thomas Edison

Re: From 8.1 to 8.3

From
Alvaro Herrera
Date:
S Arvind escribió:
> Our company wants to move from 8,1 to 8.3 latest. In irc they told me to
> check realse notes for issues while upgrading. But there are lots of release
> notesss. Can anyone tell some most noticable change or place-of-error while
> upgrading?

If you're too lazy to read them, we're too lazy to summarise them for
you ...

(Luckily for everybody, Bruce and Tom were NOT lazy enough to write them
in the first place.)

The meat of what you need to know is in the 8.2.0 and 8.3.0 notes, the
"incompatibilities" section anyhow.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: From 8.1 to 8.3

From
Joao Ferreira gmail
Date:
On Wed, 2009-04-22 at 22:12 +0530, S Arvind wrote:
> Our company wants to move from 8,1 to 8.3 latest. In irc they told me
> to check realse notes for issues while upgrading. But there are lots
> of release notesss. Can anyone tell some most noticable change or
> place-of-error while upgrading?

one I had to solve was the need for explicit casting in SQL queries that
used numeric comparison of REAL with TEXT...

yes... this used to be possible on 8.1 and is no longer on 8.3

so if your applications have such queries maybe you will bumo into some
problems

I used stuff like this: cast(instantin as numeric)

cheers

Joao

>
> Arvind S
>
>
> "Many of lifes failure are people who did not realize how close they
> were to success when they gave up."
> -Thomas Edison

Re: From 8.1 to 8.3

From
"Joshua D. Drake"
Date:
On Wed, 2009-04-22 at 12:49 -0400, Alvaro Herrera wrote:
> S Arvind escribió:
> > Our company wants to move from 8,1 to 8.3 latest. In irc they told me to
> > check realse notes for issues while upgrading. But there are lots of release
> > notesss. Can anyone tell some most noticable change or place-of-error while
> > upgrading?
>
> If you're too lazy to read them, we're too lazy to summarise them for
> you ...
>

And to actually be helpful, the number one issue people see to run into
is this one:

        Non-character data types are no longer automatically cast to
        TEXT (Peter, Tom)

        Previously, if a non-character value was supplied to an operator
        or function that requires text input, it was automatically cast
        to text, for most (though not all) built-in data types. This no
        longer happens: an explicit cast to text is now required for all
        non-character-string types. For example, these expressions
        formerly worked:

        substr(current_date, 1, 4)
        23 LIKE '2%'

        but will now draw "function does not exist" and "operator does
        not exist" errors respectively. Use an explicit cast instead:

        substr(current_date::text, 1, 4)
        23::text LIKE '2%'

        (Of course, you can use the more verbose CAST() syntax too.) The
        reason for the change is that these automatic casts too often
        caused surprising behavior. An example is that in previous
        releases, this expression was accepted but did not do what was
        expected:

        current_date < 2017-11-17

        This is actually comparing a date to an integer, which should be
        (and now is) rejected — but in the presence of automatic casts
        both sides were cast to text and a textual comparison was done,
        because the text < text operator was able to match the
        expression when no other < operator could.

        Types char(n) and varchar(n) still cast to text automatically.
        Also, automatic casting to text still works for inputs to the
        concatenation (||) operator, so long as least one input is a
        character-string type.

However Alvaro is right. You should read the entire incompatibilities
section, and of course test.

Sincerely,

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997

Testing ... please reply

From
"Atul Chojar"
Date:
Could someone reply to this email? I am testing my subscription; joined over 2 months ago, but never get any response
toquestions 

Thanks!
Atul



-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Joshua D. Drake
Sent: Wednesday, April 22, 2009 12:56 PM
To: Alvaro Herrera
Cc: S Arvind; pgsql-general@postgresql.org
Subject: Re: [GENERAL] From 8.1 to 8.3

On Wed, 2009-04-22 at 12:49 -0400, Alvaro Herrera wrote:
> S Arvind escribió:
> > Our company wants to move from 8,1 to 8.3 latest. In irc they told me to
> > check realse notes for issues while upgrading. But there are lots of release
> > notesss. Can anyone tell some most noticable change or place-of-error while
> > upgrading?
>
> If you're too lazy to read them, we're too lazy to summarise them for
> you ...
>

And to actually be helpful, the number one issue people see to run into
is this one:

        Non-character data types are no longer automatically cast to
        TEXT (Peter, Tom)

        Previously, if a non-character value was supplied to an operator
        or function that requires text input, it was automatically cast
        to text, for most (though not all) built-in data types. This no
        longer happens: an explicit cast to text is now required for all
        non-character-string types. For example, these expressions
        formerly worked:

        substr(current_date, 1, 4)
        23 LIKE '2%'

        but will now draw "function does not exist" and "operator does
        not exist" errors respectively. Use an explicit cast instead:

        substr(current_date::text, 1, 4)
        23::text LIKE '2%'

        (Of course, you can use the more verbose CAST() syntax too.) The
        reason for the change is that these automatic casts too often
        caused surprising behavior. An example is that in previous
        releases, this expression was accepted but did not do what was
        expected:

        current_date < 2017-11-17

        This is actually comparing a date to an integer, which should be
        (and now is) rejected — but in the presence of automatic casts
        both sides were cast to text and a textual comparison was done,
        because the text < text operator was able to match the
        expression when no other < operator could.

        Types char(n) and varchar(n) still cast to text automatically.
        Also, automatic casting to text still works for inputs to the
        concatenation (||) operator, so long as least one input is a
        character-string type.

However Alvaro is right. You should read the entire incompatibilities
section, and of course test.

Sincerely,

Joshua D. Drake

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.287 / Virus Database: 270.12.1/2070 - Release Date: 04/22/09 08:49:00

Re: Testing ... please reply

From
Raymond O'Donnell
Date:
On 22/04/2009 18:21, Atul Chojar wrote:
> Could someone reply to this email? I am testing my subscription;
> joined over 2 months ago, but never get any response to questions

Receiving you loud and clear!

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Re: Testing ... please reply

From
Joao Ferreira
Date:
Coming loud and clear !

joao



On Wed, 2009-04-22 at 13:21 -0400, Atul Chojar wrote:
> Could someone reply to this email? I am testing my subscription; joined over 2 months ago, but never get any response
toquestions 
>
> Thanks!
> Atul
>
>
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Joshua D. Drake
> Sent: Wednesday, April 22, 2009 12:56 PM
> To: Alvaro Herrera
> Cc: S Arvind; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] From 8.1 to 8.3
>
> On Wed, 2009-04-22 at 12:49 -0400, Alvaro Herrera wrote:
> > S Arvind escribió:
> > > Our company wants to move from 8,1 to 8.3 latest. In irc they told me to
> > > check realse notes for issues while upgrading. But there are lots of release
> > > notesss. Can anyone tell some most noticable change or place-of-error while
> > > upgrading?
> >
> > If you're too lazy to read them, we're too lazy to summarise them for
> > you ...
> >
>
> And to actually be helpful, the number one issue people see to run into
> is this one:
>
>         Non-character data types are no longer automatically cast to
>         TEXT (Peter, Tom)
>
>         Previously, if a non-character value was supplied to an operator
>         or function that requires text input, it was automatically cast
>         to text, for most (though not all) built-in data types. This no
>         longer happens: an explicit cast to text is now required for all
>         non-character-string types. For example, these expressions
>         formerly worked:
>
>         substr(current_date, 1, 4)
>         23 LIKE '2%'
>
>         but will now draw "function does not exist" and "operator does
>         not exist" errors respectively. Use an explicit cast instead:
>
>         substr(current_date::text, 1, 4)
>         23::text LIKE '2%'
>
>         (Of course, you can use the more verbose CAST() syntax too.) The
>         reason for the change is that these automatic casts too often
>         caused surprising behavior. An example is that in previous
>         releases, this expression was accepted but did not do what was
>         expected:
>
>         current_date < 2017-11-17
>
>         This is actually comparing a date to an integer, which should be
>         (and now is) rejected — but in the presence of automatic casts
>         both sides were cast to text and a textual comparison was done,
>         because the text < text operator was able to match the
>         expression when no other < operator could.
>
>         Types char(n) and varchar(n) still cast to text automatically.
>         Also, automatic casting to text still works for inputs to the
>         concatenation (||) operator, so long as least one input is a
>         character-string type.
>
> However Alvaro is right. You should read the entire incompatibilities
> section, and of course test.
>
> Sincerely,
>
> Joshua D. Drake
>
> --
> PostgreSQL - XMPP: jdrake@jabber.postgresql.org
>    Consulting, Development, Support, Training
>    503-667-4564 - http://www.commandprompt.com/
>    The PostgreSQL Company, serving since 1997
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.287 / Virus Database: 270.12.1/2070 - Release Date: 04/22/09 08:49:00
>
>

Re: thanks for the "testing" replies ; now my first question - Logs say update done but not actually done or committed into database

From
"Atul Chojar"
Date:
Thanks to everyone who replied to the test email!

Now for my real question:-

We are facing a strange problem in our 8.2.7 database.

There is a bash shell script that does:-

sql=”select distinct to_char(date_of_issue, ‘YYYYMM’) from yan.int_prod_s_master order by 1;”
YYYYMM=`/usr/local/pgsql/bin/psql -U postgres -h payday -d sandbox -t -c “$sql”`
for x in $YYYYMM
do
  $scriptdir/USCS_production_updates.sh $x >>$logdir/USCS_production_updates.log 2>&1
done

The $scriptdir/USCS_production_updates.sh script does updates like:-

YYYYMM=$1
database=”us_audit”
db_user=”postgres”
db_host=”nutrageous”
psql_cmd=”/usr/local/pgsql/bin/psql -U ${db_user} -h ${db_host} -d ${database} -e “;
sql=”
update int_prod_manual_price_${YYYYMM} mp
  set …
from int_prod_s_master_${YYYYMM} sm
where …
and not exists ( select 1 from int_prod_stop_${YYYYMM} where …)
and …;
“;
$psql_cmd -c “$SQL”

When these scripts run, the USCS_production_updates.log shows the correct update statement, with values of YYYYMM
substitutedin the table names, and message like “UPDATE 1025” from postgres indicating 1025 rows got updated. 

However, none of these updates actually get applied in the database. Auto commit is on in the database, but it seems
theupdates do not get committed. 

The system logs also show no errors.

Any ideas why above update is not working?

Thanks!
Atul




-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Joao Ferreira
Sent: Wednesday, April 22, 2009 1:34 PM
To: Atul Chojar
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Testing ... please reply


Coming loud and clear !

joao



On Wed, 2009-04-22 at 13:21 -0400, Atul Chojar wrote:
> Could someone reply to this email? I am testing my subscription; joined over 2 months ago, but never get any response
toquestions 
>
> Thanks!
> Atul
>
>
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Joshua D. Drake
> Sent: Wednesday, April 22, 2009 12:56 PM
> To: Alvaro Herrera
> Cc: S Arvind; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] From 8.1 to 8.3
>
> On Wed, 2009-04-22 at 12:49 -0400, Alvaro Herrera wrote:
> > S Arvind escribió:
> > > Our company wants to move from 8,1 to 8.3 latest. In irc they told me to
> > > check realse notes for issues while upgrading. But there are lots of release
> > > notesss. Can anyone tell some most noticable change or place-of-error while
> > > upgrading?
> >
> > If you're too lazy to read them, we're too lazy to summarise them for
> > you ...
> >
>
> And to actually be helpful, the number one issue people see to run into
> is this one:
>
>         Non-character data types are no longer automatically cast to
>         TEXT (Peter, Tom)
>
>         Previously, if a non-character value was supplied to an operator
>         or function that requires text input, it was automatically cast
>         to text, for most (though not all) built-in data types. This no
>         longer happens: an explicit cast to text is now required for all
>         non-character-string types. For example, these expressions
>         formerly worked:
>
>         substr(current_date, 1, 4)
>         23 LIKE '2%'
>
>         but will now draw "function does not exist" and "operator does
>         not exist" errors respectively. Use an explicit cast instead:
>
>         substr(current_date::text, 1, 4)
>         23::text LIKE '2%'
>
>         (Of course, you can use the more verbose CAST() syntax too.) The
>         reason for the change is that these automatic casts too often
>         caused surprising behavior. An example is that in previous
>         releases, this expression was accepted but did not do what was
>         expected:
>
>         current_date < 2017-11-17
>
>         This is actually comparing a date to an integer, which should be
>         (and now is) rejected — but in the presence of automatic casts
>         both sides were cast to text and a textual comparison was done,
>         because the text < text operator was able to match the
>         expression when no other < operator could.
>
>         Types char(n) and varchar(n) still cast to text automatically.
>         Also, automatic casting to text still works for inputs to the
>         concatenation (||) operator, so long as least one input is a
>         character-string type.
>
> However Alvaro is right. You should read the entire incompatibilities
> section, and of course test.
>
> Sincerely,
>
> Joshua D. Drake
>
> --
> PostgreSQL - XMPP: jdrake@jabber.postgresql.org
>    Consulting, Development, Support, Training
>    503-667-4564 - http://www.commandprompt.com/
>    The PostgreSQL Company, serving since 1997
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.287 / Virus Database: 270.12.1/2070 - Release Date: 04/22/09 08:49:00
>
>


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
No virus found in this incoming message.
Checked by AVG - www.avg.com
Version: 8.5.287 / Virus Database: 270.12.1/2070 - Release Date: 04/22/09 08:49:00

Re: thanks for the "testing" replies ; now my first question - Logs say update done but not actually done or committed into database

From
Filip Rembiałkowski
Date:


2009/4/22 Atul Chojar <achojar@airfacts.com>

We are facing a strange problem in our 8.2.7 database.

There is a bash shell script that does:-

sql=”select distinct to_char(date_of_issue, ‘YYYYMM’) from yan.int_prod_s_master order by 1;”
YYYYMM=`/usr/local/pgsql/bin/psql -U postgres -h payday -d sandbox -t -c “$sql”`
for x in $YYYYMM
do
 $scriptdir/USCS_production_updates.sh $x >>$logdir/USCS_production_updates.log 2>&1
done

The $scriptdir/USCS_production_updates.sh script does updates like:-

YYYYMM=$1
database=”us_audit”
db_user=”postgres”
db_host=”nutrageous”
psql_cmd=”/usr/local/pgsql/bin/psql -U ${db_user} -h ${db_host} -d ${database} -e “;
sql=”
update int_prod_manual_price_${YYYYMM} mp
 set …
from int_prod_s_master_${YYYYMM} sm
where …
and not exists ( select 1 from int_prod_stop_${YYYYMM} where …)
and …;
“;
$psql_cmd -c “$SQL”

When these scripts run, the USCS_production_updates.log shows the correct update statement, with values of YYYYMM substituted in the table names, and message like “UPDATE 1025” from postgres indicating 1025 rows got updated.

However, none of these updates actually get applied in the database. Auto commit is on in the database, but it seems the updates do not get committed.

The system logs also show no errors.

Any ideas why above update is not working?


hi,

1. if you run the very same UPDATE manually, does it work?
2. do you have any triggers on these tables? it's possible that they prevent updates from happening.
3. if you have table with same name but in different schema, and specific search_path, this could be a reason too.
 
HTH

PS. you don't have to post such questions to list owner :)


--
Filip Rembiałkowski
JID,mailto:filip.rembialkowski@gmail.com
http://filip.rembialkowski.net/

How to pass parameters into a sql script ?

From
"Atul Chojar"
Date:

We are unable to pass parameters into any sql script. Could anyone look at the test below and give any suggestions? PostgreSQL version is 8.2.7, running on Linux.

 

Test Script

========

$ cat chk_param.sql

select ''''||:p_date::char(8)||'''';

select count(*) from prod_debit_payments_unapplied where when_received = (select ''''||:p_date::char(8)||'''');

select count(*) from prod_debit_payments_unapplied where when_received = '20081023';

 

Test Results

=========

$ psql -d us_audit -e -1 -v p_date='20081023' -f chk_param.sql

Timing is on.

select ''''||20081023::char(8)||'''';

  ?column? 

------------

 '20081023'

(1 row)

 

Time: 1.474 ms

select count(*) from prod_debit_payments_unapplied where when_received = (select ''''||20081023::char(8)||'''');--same results with direct assignment and to_date

 count

-------

     0

(1 row)

 

Time: 36.290 ms

select count(*) from prod_debit_payments_unapplied where when_received = '20081023';

 count

-------

   193

(1 row)

 

Time: 17.722 ms

 

Thanks!

atul

AirFacts, Inc. 8120 Woodmont Ave., Suite 700 Bethesda, MD 20814 Tel: 301-760-7315

Attachment

Re: How to pass parameters into a sql script ?

From
Osvaldo Kussama
Date:
2009/5/28 Atul Chojar <achojar@airfacts.com>:
> We are unable to pass parameters into any sql script. Could anyone look at
> the test below and give any suggestions? PostgreSQL version is 8.2.7,
> running on Linux.
>
>
>
> Test Script
>
> ========
>
> $ cat chk_param.sql
>
> select ''''||:p_date::char(8)||'''';
>
> select count(*) from prod_debit_payments_unapplied where when_received =
> (select ''''||:p_date::char(8)||'''');
>
> select count(*) from prod_debit_payments_unapplied where when_received =
> '20081023';
>
>
>
> Test Results
>
> =========
>
> $ psql -d us_audit -e -1 -v p_date='20081023' -f chk_param.sql
>
> Timing is on.
>
> select ''''||20081023::char(8)||'''';
>
>   ?column?
>
> ------------
>
>  '20081023'
>
> (1 row)
>
>
>
> Time: 1.474 ms
>
> select count(*) from prod_debit_payments_unapplied where when_received =
> (select ''''||20081023::char(8)||'''');--same results with direct assignment
> and to_date
>
>  count
>
> -------
>
>      0
>
> (1 row)
>
>
>
> Time: 36.290 ms
>
> select count(*) from prod_debit_payments_unapplied where when_received =
> '20081023';
>
>  count
>
> -------
>
>    193
>
> (1 row)
>
>
>
> Time: 17.722 ms
>


bdteste=# select '20081023' =  ''''||20081023::char(8)||'''';
 ?column?
----------
 f
(1 registro)

bdteste=# select '20081023', length('20081023'),
''''||20081023::char(8)||'''', length(''''||20081023::char(8)||'''');
 ?column? | length |  ?column?  | length
----------+--------+------------+--------
 20081023 |      8 | '20081023' |     10
(1 registro)

Osvaldo