Skip site navigation (1) Skip section navigation (2)

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 <plk(dot)zuber(at)gmail(dot)com>
To: Atul Chojar <achojar(at)airfacts(dot)com>
Cc: Joao Ferreira <jmcferreira(at)critical-links(dot)com>, 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 20:17:08
Message-ID: 92869e660904221317s48aa1566h3882c01dcee2dd5@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
2009/4/22 Atul Chojar <achojar(at)airfacts(dot)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(dot)rembialkowski(at)gmail(dot)com
http://filip.rembialkowski.net/

In response to

Responses

pgsql-general by date

Next:From: Filip RembiałkowskiDate: 2009-04-22 20:58:04
Subject: Re: Help request to improve function performance
Previous:From: Bruce MomjianDate: 2009-04-22 19:11:55
Subject: Re: Error installing Postgres

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group