From: Rajesh Kumar Mallah <mallah@trade-india.com>
Organization: Infocom Network Limited
To: Raj Mathur <raju@linux-delhi.org>, pgsql-sql@postgresql.org
Subject: Re: DBD::Pg transaction issues
Date: Mon, 7 Apr 2003 13:08:02 +0530
User-Agent: KMail/1.5
References: <16016.65329.971768.399349@mail.linux-delhi.org>
	<200304071245.28635.mallah@trade-india.com>
In-Reply-To: <200304071245.28635.mallah@trade-india.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200304071308.02157.mallah@trade-india.com>


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.