From: Rajesh Kumar Mallah <mallah@trade-india.com>
To: Raj Mathur <raju@linux-delhi.org>
To: pgsql-sql@postgresql.org
Subject: Re: DBD::Pg transaction issues
Date: Mon, 7 Apr 2003 19:57:15 +0530
Message-ID: <200304071957.15018.mallah@trade-india.com> (raw)
In-Reply-To: <16017.14581.156730.440857@mail.linux-delhi.org>
References: <16016.65329.971768.399349@mail.linux-delhi.org>
<200304071245.28635.mallah@trade-india.com>
<16017.14581.156730.440857@mail.linux-delhi.org>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mallah@trade-india.com, raju@linux-delhi.org
Subject: Re: DBD::Pg transaction issues
In-Reply-To: <200304071957.15018.mallah@trade-india.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox