On Mon, 2006-05-01 at 14:11 -0400, Chris Darroch wrote:
> Sounds good -- if you see an error you can handle, you clear
> the transaction's error state back to "ok", and apr_dbd lets you
> proceed. Seems very reasonable to me; thanks!
Perhaps. However, it would seem that not all databases behave the same
on error mid-transaction. Compare SQLite3 with PostgreSQL:
----------------------------------
sqlite> create table test (a text primary key);
sqlite> begin;
sqlite> insert into test values ('a');
sqlite> insert into test values ('a');
SQL error: column a is not unique
sqlite> end;
sqlite> select * from test;
a <-- ONE ROW INSERTED!
----------------------------------
----------------------------------
postgres=# create table test (a text primary key);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_pkey" for table "test"
CREATE TABLE
postgres=# begin;
BEGIN
postgres=# insert into test values ('a');
INSERT 0 1
postgres=# insert into test values ('a');
ERROR: duplicate key violates unique constraint "test_pkey"
postgres=# end;
ROLLBACK
postgres=# select * from test;
a
---
(0 rows) <-- NOTHING INSERTED!
----------------------------------
In other words, once PostgreSQL encounters an error mid-transaction, it
would seem that one needs a savepoint to "fix things" before anything
gets committed. Clearing APR DBD error, doesn't help with PostgreSQL -
the transaction will still be rolled back by the database. MySQL 5
behaves like SQLite3. Not sure what Oracle does - I can check when I get
to work later on (but I'm guessing it would be similar to PostgreSQL).
Maybe there is a way to tell PostgreSQL not to fail - not sure...
--
Bojan
