pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDBD::Pg transaction issues
7+ messages / 4 participants
[nested] [flat]
* DBD::Pg transaction issues
@ 2003-04-07 04:31 Raj Mathur <raju@linux-delhi.org>
0 siblings, 1 reply; 7+ messages in thread
From: Raj Mathur @ 2003-04-07 04:31 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: DBD::Pg transaction issues
@ 2003-04-07 07:15 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Raj Mathur <raju@linux-delhi.org>
0 siblings, 2 replies; 7+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-07 07:15 UTC (permalink / raw)
To: Raj Mathur <raju@linux-delhi.org>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: DBD::Pg transaction issues
@ 2003-04-07 07:38 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 0 replies; 7+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-07 07:38 UTC (permalink / raw)
To: Raj Mathur <raju@linux-delhi.org>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: DBD::Pg transaction issues
@ 2003-04-07 08:38 Raj Mathur <raju@linux-delhi.org>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
1 sibling, 2 replies; 7+ messages in thread
From: Raj Mathur @ 2003-04-07 08:38 UTC (permalink / raw)
To: pgsql-sql
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
--
Raj Mathur raju@kandalaya.org http://kandalaya.org/
It is the mind that moves
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: DBD::Pg transaction issues
@ 2003-04-07 08:59 patrick <patrick+pgsql@boxsoft.com>
parent: Raj Mathur <raju@linux-delhi.org>
1 sibling, 0 replies; 7+ messages in thread
From: patrick @ 2003-04-07 08:59 UTC (permalink / raw)
To: Raj Mathur <raju@linux-delhi.org>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: DBD::Pg transaction issues
@ 2003-04-07 14:27 Rajesh Kumar Mallah <mallah@trade-india.com>
parent: Raj Mathur <raju@linux-delhi.org>
1 sibling, 1 reply; 7+ messages in thread
From: Rajesh Kumar Mallah @ 2003-04-07 14:27 UTC (permalink / raw)
To: Raj Mathur <raju@linux-delhi.org>; pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: DBD::Pg transaction issues
@ 2003-04-09 12:46 Chris Gamache <cgg007@yahoo.com>
parent: Rajesh Kumar Mallah <mallah@trade-india.com>
0 siblings, 0 replies; 7+ messages in thread
From: Chris Gamache @ 2003-04-09 12:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-04-09 12:46 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-07 04:31 DBD::Pg transaction issues Raj Mathur <raju@linux-delhi.org>
2003-04-07 07:15 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-04-07 07:38 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-04-07 08:38 ` Raj Mathur <raju@linux-delhi.org>
2003-04-07 08:59 ` patrick <patrick+pgsql@boxsoft.com>
2003-04-07 14:27 ` Rajesh Kumar Mallah <mallah@trade-india.com>
2003-04-09 12:46 ` Chris Gamache <cgg007@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox