Re: DBD::Pg transaction issues - Mailing list pgsql-sql
From | Rajesh Kumar Mallah |
---|---|
Subject | Re: DBD::Pg transaction issues |
Date | |
Msg-id | 200304071308.02157.mallah@trade-india.com Whole thread Raw |
In response to | Re: DBD::Pg transaction issues (Rajesh Kumar Mallah <mallah@trade-india.com>) |
List | pgsql-sql |
oops there was a silly mistake in $@ part. > if ($@) { > $dbh -> commit(); > } else { > $dbh -> rollback(); > } shud have been > if ($@) { > $dbh -> rollback(); > } else { > $dbh -> commit(); > } On Monday 07 Apr 2003 12:45 pm, Rajesh Kumar Mallah wrote: > Dear Raju, > > All subsequest statements (DML/DDL/SELECT) are ignored > after the first ERROR caused by any statement > in the transaction. the current transaction must be rollbacked > and new one started. > > I can think of two approaches: > > a) Prevent the error situation from arising > may be by first querying if the reference exists. > > b) commit when the operation succeds and rollback when there > is ERROR from sql. > > In perl-DBI eval is gnerally used for catching such SQLs > without aborting the program. > > > while (true ) { > > eval { > <DBD::Pg operations here> > }; > if ($@) { > $dbh -> commit(); > } else { > $dbh -> rollback(); > } > } > > in both cases you loose on performance in forfer > case to to checking overhead and in laters repeated > comitting. > > regds > mallah. > > 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) > > possibly duplicates. > > > > The data is in a text file, which is read record by record and > > appended to the database table. A transactions spans the complete > > reading of the text file. > > > > If the cross-reference field in the file doesn't exist in the > > referenced table I want to ignore the record. > > > > If the record already exists in the table I want to perform some > > updates to the existing data. > > > > The problem is that the first record in the text file that has an > > invalid reference, or one that already exists, causes the transaction > > to abort and all subsequent updates from the file to fail. Is there > > any way to tell DBI/PostgreSQL that it should continue the transaction > > until the program directs it to commit/rollback? > > > > Tried the following so far: > > > > Set RaiseError to null. No effect. > > > > Currently manually checking for duplicates/missing referenced 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 to duplicate > > read existing record > > update values in existing record > > rewrite record > > else if write failed due to missing reference > > ignore record > > else > > mark file as bad > > > > if file not bad > > commit > > else > > rollback > > > > Hope this is the right list to be asking on. > > > > Regards, > > > > -- Raju -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.