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
valuesin 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