Обсуждение: DBD::Pg transaction issues
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
Dear Raju,
All subsequest statements (DML/DDL/SELECT) are ignored
after the first ERROR caused by any statement
in the transaction. the current transaction must be rollbacked
and new one started.
I can think of two approaches:
a) Prevent the error situation from arising may be by first querying if the reference exists.
b) commit when the operation succeds and rollback when there is ERROR from sql.
In perl-DBI eval is gnerally used for catching such SQLs
without aborting the program.
while (true ) {
eval { <DBD::Pg operations here>
};
if ($@) {$dbh -> commit();
} else {$dbh -> rollback();
}
}
in both cases you loose on performance in forfer
case to to checking overhead and in laters repeated
comitting.
regds
mallah.
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.
oops there was a silly mistake in $@ part.
> if ($@) {
> $dbh -> commit();
> } else {
> $dbh -> rollback();
> }
shud have been
> if ($@) {
> $dbh -> rollback();
> } else {
> $dbh -> commit();
> }
On Monday 07 Apr 2003 12:45 pm, Rajesh Kumar Mallah wrote:
> Dear Raju,
>
> All subsequest statements (DML/DDL/SELECT) are ignored
> after the first ERROR caused by any statement
> in the transaction. the current transaction must be rollbacked
> and new one started.
>
> I can think of two approaches:
>
> a) Prevent the error situation from arising
> may be by first querying if the reference exists.
>
> b) commit when the operation succeds and rollback when there
> is ERROR from sql.
>
> In perl-DBI eval is gnerally used for catching such SQLs
> without aborting the program.
>
>
> while (true ) {
>
> eval {
> <DBD::Pg operations here>
> };
> if ($@) {
> $dbh -> commit();
> } else {
> $dbh -> rollback();
> }
> }
>
> in both cases you loose on performance in forfer
> case to to checking overhead and in laters repeated
> comitting.
>
> regds
> mallah.
>
> 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.
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
statementin 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
repeatedcomitting.
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)
possiblyduplicates. >> >> The data is in a text file, which is read record by record and >> appended to the
databasetable. A transactions spans the >> complete reading of the text file. >> >> If the cross-reference
fieldin the file doesn't exist in the >> referenced table I want to ignore the record. >> >> If the record
alreadyexists in the table I want to perform >> some updates to the existing data. >> >> The problem is that the
firstrecord in the text file that has >> an invalid reference, or one that already exists, causes the >>
transactionto abort and all subsequent updates from the file >> to fail. Is there any way to tell DBI/PostgreSQL
thatit >> should continue the transaction until the program directs it to >> commit/rollback? >> >> Tried the
followingso far: >> >> Set RaiseError to null. No effect. >> >> Currently manually checking for
duplicates/missingreferenced >> 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
toduplicate read existing >> record update values in existing record rewrite record else if >> write failed due to
missingreference ignore record else mark >> file as bad >> >> if file not bad commit else rollback >> >>
Hopethis is the right list to be asking on. >> >> Regards, >> >> -- Raju
--
Raj Mathur raju@kandalaya.org http://kandalaya.org/ It is the mind that moves
On Mon, Apr 07, 2003 at 02:08:13PM +0530, Raj Mathur wrote: > 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? One thing you may consider is to insert your new records into a temp table first. Then when your program decides whether it should "commit" then move the values from the temp table to your target table, otherwise discard the entries in the temp table. Hope this helps, sidster -- They who would sacrifice freedom for security will have neither. -Ben Franklin
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.
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