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 [EMAIL PROTECTED] http://kandalaya.org/
It is the mind that moves
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
