Why does "passthrough for remote_user;" cause the following error (ASA 11.0.1.3113)? COMMIT/ROLLBACK not allowed within atomic operation SQLCODE=-267, ODBC 3 State="42000" Here's the simplified situation: create table table1 ( id integer not null default autoincrement, column1 integer, primary key (id)); create table table2 ( id integer not null default autoincrement, column1 integer, primary key (id)); Only table2 is part of a SQL Remote subscription create trigger "trigger1" after update of column1 order 1 on table1 referencing new as new_val for each row begin passthrough for remote_user; execute immediate 'update table2 set column1 = ' || new_val.column1 || ' where id = ' || new_val.id; passthrough stop; end; If I now execute "update table1 set column1 = 10 where id = 2" I get the error -267. If the trigger only does the execute immediate, it works just fine. If the trigger does a "passthrough ONLY for remote_user" it returns the same error. So it almost looks like the passthrough call is causing this error!? |
Passthrough operations, in this respect, are very similar to DDL (which are also not permitted inside of atomic operations) and are probably internally classified that way. If I were to classify the PASSTHROUGH statement, I would have classified it as DCL "Data Control Language" in that it affects the SQL Remote "stream" in ways that are not classic DDL or DML. As such they should not be permitted inside of triggers. Either way ... this is not unexpected. So be 'a shortened tree' no more. There are other 'non-transactional' aspects to the use of PassThrough statements as well which can affect other scenarios. [there is a reason there are lots of known caveats affixed to this feature ... and why you always test before deploying] |
The Help should say "Side effects - Automatic commit" for the SQL Remote PASSTHROUGH statement. Here is a reproducible that proves the point: BEGIN UPDATE parent SET non_key_1 = 123 WHERE key_1 = 1; COMMIT; SELECT @@VERSION, 'First SELECT', non_key_1 FROM parent WHERE key_1 = 1; UPDATE parent SET non_key_1 = 456 WHERE key_1 = 1; PASSTHROUGH FOR site0001; PASSTHROUGH STOP; ROLLBACK; SELECT @@VERSION, 'Second SELECT', non_key_1 FROM parent WHERE key_1 = 1; END; @@VERSION,'First SELECT',non_key_1 '9.0.2.3951','First SELECT','123' @@VERSION,'Second SELECT',non_key_1 '9.0.2.3951','Second SELECT','456' 1
The docs should be updated. The PASSTHROUGH command has had a COMMIT as a side effect since September 20th, 1995 at 5:25pm (Eastern Time).
(24 Mar '14, 13:43)
Reg Domaratzki
Replies hidden
1
...no doubt it was in celebration of the 10th anniversary of the capital gains tax being introduced in Australia :)
(24 Mar '14, 15:32)
Breck Carter
|
In case PASSTHROUGH mode is not allowed within a trigger (as Nick has stated), you may use other approaches to force indirect DML updates on replicated tables, say
By the way: Are you sure the UPDATE within the trigger is not replicated by default? If so, does this change when running DBREMOTE -t?
(21 Mar '14, 12:48)
Volker Barth
|
I get the same behavior in 9.0.2.3951... I could upgrade my SQL Remote test setup to version 11 but that would not change the fact that I'm stumped :)
Is this construction (passthrough mode within a trigger) something you have used successfully before? (In other words: I do not know whether it should work at all, i.e. I'm stumped, too:))
Haven't tried this before. There are some limitations listed in the help
Maybe the limit on batch statements could explain this behaviour:
Batch statements (a group of statements surrounded with a BEGIN and END) are not replicated in passthrough mode. If you try to use batch statements in passthrough mode, an error occurs