From: Raj Mathur <raju@linux-delhi.org>
To: pgsql-sql@postgresql.org
Subject: DBD::Pg transaction issues
Date: Mon, 7 Apr 2003 10:01:45 +0530
Message-ID: <16016.65329.971768.399349@mail.linux-delhi.org> (raw)
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
--
Raj Mathur raju@kandalaya.org http://kandalaya.org/
It is the mind that moves
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: raju@linux-delhi.org
Subject: Re: DBD::Pg transaction issues
In-Reply-To: <16016.65329.971768.399349@mail.linux-delhi.org>
* 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