Re: DBD::Pg transaction issues - Mailing list pgsql-sql

Hi Rajesh,

>>>>> "Rajesh" == Rajesh Kumar Mallah <mallah@trade-india.com> writes:
   Rajesh> Dear Raju,
   Rajesh> All subsequest statements (DML/DDL/SELECT) are ignored   Rajesh> after the first ERROR caused by any
statementin the   Rajesh> transaction. the current transaction must be rollbacked   Rajesh> and new one started.
 
   Rajesh> I can think of two approaches:
   Rajesh> a) Prevent the error situation from arising may be by   Rajesh> first querying if the reference exists.

Which is what I'm doing now; it's an inelegant and inefficient
solution.
   Rajesh> b) commit when the operation succeds and rollback when   Rajesh> there is ERROR from sql.

That's not an alternative, since I need to commit either all the
record in the file or none of them.
   Rajesh> In perl-DBI eval is gnerally used for catching such SQLs   Rajesh> without aborting the program.

Am using eval's, but the transaction gets aborted in any case.  The
eval is only useful for my program to trap the error.  I'm trying to
find out how I can prevent the transaction mechanism from treating
errors on the database as logical errors.

So is there no way to prevent PostgreSQL (or DBI) from aborting the
current transaction if an operation on the database fails?

Regards,

-- Raju
   Rajesh> while (true ) {
   Rajesh> eval { <DBD::Pg operations here> }; if ($@) { $dbh ->   Rajesh> commit(); } else { $dbh -> rollback(); } }
   Rajesh> in both cases you loose on performance in forfer case to   Rajesh> to checking overhead and in laters
repeatedcomitting.
 
   Rajesh> regds mallah.
   Rajesh> On Monday 07 Apr 2003 10:01 am, Raj Mathur wrote:   >> Hi,   >>    >> Am using DBD::Pg with PostgreSQL
7.2.3,Perl 5.8.0.  I have a   >> set of updates to a table which has (a) references to another   >> table and (b)
possiblyduplicates.   >>    >> The data is in a text file, which is read record by record and   >> appended to the
databasetable.  A transactions spans the   >> complete reading of the text file.   >>    >> If the cross-reference
fieldin the file doesn't exist in the   >> referenced table I want to ignore the record.   >>    >> If the record
alreadyexists in the table I want to perform   >> some updates to the existing data.   >>    >> The problem is that the
firstrecord in the text file that has   >> an invalid reference, or one that already exists, causes the   >>
transactionto abort and all subsequent updates from the file   >> to fail.  Is there any way to tell DBI/PostgreSQL
thatit   >> should continue the transaction until the program directs it to   >> commit/rollback?   >>    >> Tried the
followingso far:   >>    >> Set RaiseError to null.  No effect.   >>    >> Currently manually checking for
duplicates/missingreferenced   >> records and taking appropriate action when found.  Pretty   >> inelegant.   >>    >>
Pseudocode:  >>    >> open text file begin transaction while read text record write   >> into table if write failed due
toduplicate read existing   >> record update values in existing record rewrite record else if   >> write failed due to
missingreference ignore record else mark   >> file as bad   >>    >> if file not bad commit else rollback   >>    >>
Hopethis is the right list to be asking on.   >>    >> Regards,   >>    >> -- Raju
 

-- 
Raj Mathur                raju@kandalaya.org      http://kandalaya.org/                     It is the mind that moves