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" <achojar(at)airfacts(dot)com>
To: "'Joao Ferreira'" <jmcferreira(at)critical-links(dot)com>, <pgsql-general-owner(at)postgresql(dot)org>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: thanks for the "testing" replies ; now my first question - Logs say update done but not actually done or committed into database
Date: 2009-04-22 18:15:22
Message-ID: 009f01c9c376$4e6b4560$eb41d020$@com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 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?

Thanks!
Atul

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Joao Ferreira
Sent: Wednesday, April 22, 2009 1:34 PM
To: Atul Chojar
Cc: pgsql-general(at)postgresql(dot)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 to questions
>
> Thanks!
> Atul
>
>
>
> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Joshua D. Drake
> Sent: Wednesday, April 22, 2009 12:56 PM
> To: Alvaro Herrera
> Cc: S Arvind; pgsql-general(at)postgresql(dot)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(at)jabber(dot)postgresql(dot)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(at)postgresql(dot)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(at)postgresql(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Christine Penner 2009-04-22 18:21:00 Re: Error installing Postgres
Previous Message Raymond O'Donnell 2009-04-22 18:14:38 Re: Error installing Postgres