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 19:57:15 +0530
User-Agent: KMail/1.5
References: <16016.65329.971768.399349@mail.linux-delhi.org>
	<200304071245.28635.mallah@trade-india.com>
	<16017.14581.156730.440857@mail.linux-delhi.org>
In-Reply-To: <16017.14581.156730.440857@mail.linux-delhi.org>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200304071957.15018.mallah@trade-india.com>


Dear Raju ,

If you are expecting the below:

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

i *think* u cannot be expecting:

>
> That's not an alternative, since I need to commit either all the
> record in the file or none of them.
>

Sorry for the ignorance if i am not getting ur problem :-)


Regds
mallah.


On Monday 07 Apr 2003 2:08 pm, Raj Mathur wrote:
> 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 statement in 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 repeated comitting.
>
>     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) 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.