Re: disabling autocommit

>>>>> "MVM" == Matt Van Mater <nutter_(at)hotmail(dot)com> writes:

MVM> My problem is this: I enforce unique rows for all data, and
MVM> occasionally there is an error where I try to insert a duplicate
MVM> entry. I expect to see these duplicate entries and depend on the DB
MVM> to enforce the row uniqueness. When I just run the insert statements
MVM> without the begin and commit keywords the insert only fails for that
MVM> single insert, but If I disable autocommit then all the inserts fail
MVM> because of one error.

I have a situation like this, but with foreign key dependencies.
Occasionally someone will perform some action that is tracked by my
system, but they will use a very old stale link that has no associated
record with it in the database any more, so I should ignore logging
that action.

What I do is make it opportunistic. First I try to insert my batch of
log records within a transaction. If the tx fails for a FK violation,
I then run that same batch again, but I do a select prior to each
insert to ensure that the FK violation won't occur.

In something like 1 out of 200 batches do I need to retry with the
explicit integrity checks on.

However, if your expected norm is to encounter duplicates, then try
just doing the select prior to insert always.

In PG 8.0, I expect to be able to deal with this with the nested
transactions.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera(at)kciLink(dot)com Rockville, MD +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/