MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Message-ID: <16016.65329.971768.399349@mail.linux-delhi.org>
Date: Mon, 7 Apr 2003 10:01:45 +0530
To: pgsql-sql@postgresql.org
Subject: DBD::Pg transaction issues
From: Raj Mathur <raju@linux-delhi.org>

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