Re: DBD::Pg transaction issues

There's no need to break how a transaction behaves. It behaves the way it
behaves for good reason!

Besides, if you want speed, don't read the text file line-by-line and insert at
each line. Instead, generate a tab-delimited file and use the psql copy command
to insert your data into a temporary table. Then execute "insert into table
(fields) from temp_table where criteria;" within a transaction. If any
dependancies fail in the operation the whole transaction will have to be rolled
back. The advantage is having TWO operations rather than n (rows) operations.
You save yourself the trouble of having to check whether the new rows'
dependancies are in place before an insert. Use the tools PostgreSQL provides
you with (foreign keys, views, triggers, inheritance, schemas...) and you will
have your elegant solution. Don't do with perl what can be better accomplished
with PostgreSQL.

HTH,

CG


--- Rajesh Kumar Mallah <mallah@trade-india.com> wrote:
> 
> 
> Dear Raju ,
> 
> If you are expecting the below:
> 
> >
> > So is there no way to prevent PostgreSQL (or DBI) from aborting the
> > current transaction if an operation on the database fails?
> >
> 
> i *think* u cannot be expecting:
> 
> >
> > That's not an alternative, since I need to commit either all the
> > record in the file or none of them.
> >
> 
> Sorry for the ignorance if i am not getting ur problem :-)
> 
> 
> Regds
> mallah.
> 
> 
> 
> On Monday 07 Apr 2003 2:08 pm, Raj Mathur wrote:
> > Hi Rajesh,
> >
> > >>>>> "Rajesh" == Rajesh Kumar Mallah <mallah@trade-india.com> writes:
> >
> >     Rajesh> Dear Raju,
> >
> >     Rajesh> All subsequest statements (DML/DDL/SELECT) are ignored
> >     Rajesh> after the first ERROR caused by any statement in the
> >     Rajesh> transaction. the current transaction must be rollbacked
> >     Rajesh> and new one started.
> >
> >     Rajesh> I can think of two approaches:
> >
> >     Rajesh> a) Prevent the error situation from arising may be by
> >     Rajesh> first querying if the reference exists.
> >
> > Which is what I'm doing now; it's an inelegant and inefficient
> > solution.
> >
> >     Rajesh> b) commit when the operation succeds and rollback when
> >     Rajesh> there is ERROR from sql.
> >
> > That's not an alternative, since I need to commit either all the
> > record in the file or none of them.
> >
> >     Rajesh> In perl-DBI eval is gnerally used for catching such SQLs
> >     Rajesh> without aborting the program.
> >
> > Am using eval's, but the transaction gets aborted in any case.  The
> > eval is only useful for my program to trap the error.  I'm trying to
> > find out how I can prevent the transaction mechanism from treating
> > errors on the database as logical errors.
> >
> > So is there no way to prevent PostgreSQL (or DBI) from aborting the
> > current transaction if an operation on the database fails?
> >
> > Regards,
> >
> > -- Raju
> >
> >     Rajesh> while (true ) {
> >
> >     Rajesh> eval { <DBD::Pg operations here> }; if ($@) { $dbh ->
> >     Rajesh> commit(); } else { $dbh -> rollback(); } }
> >
> >     Rajesh> in both cases you loose on performance in forfer case to
> >     Rajesh> to checking overhead and in laters repeated comitting.
> >
> >     Rajesh> regds mallah.
> >
> >     Rajesh> 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.
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org


__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com