Re: DBD::Pg transaction issues

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.