Solved

SqlExecutor atomic transaction by '"commit".


In what way can I control the behaviour of SqlExecutor concerning transactions. I'd like to perform several inserts and updates as a single transaction (by putting the "commit"-command at the end). But this commit always results in the warning "there is no transaction in progress".

Database = PostgreSQL

icon

Best answer by david_r 21 March 2018, 16:11

View original

11 replies

Userlevel 4

Try something like the following in a SQLExecutor:

FME_SQL_DELIMITER @

begin transaction;

insert into ...;
insert into ...;
delete from ...;
delete from ...;

commit transaction;
@

The important bit is to re-define the FME SQL delimiter to a character that isn't used by your SQL commands. If you set FME_SQL_DELIMITER to a semi-colon, then FME will split up your all commands and send them to your backend database in separate transactions, which is why the database says that there's no active transaction when "commit transaction" comes.

Userlevel 3
Badge +13

Try something like the following in a SQLExecutor:

FME_SQL_DELIMITER @

begin transaction;

insert into ...;
insert into ...;
delete from ...;
delete from ...;

commit transaction;
@

The important bit is to re-define the FME SQL delimiter to a character that isn't used by your SQL commands. If you set FME_SQL_DELIMITER to a semi-colon, then FME will split up your all commands and send them to your backend database in separate transactions, which is why the database says that there's no active transaction when "commit transaction" comes.

@halo_rivm make sure to end with the delimiter. Check out our article on splitting SQL statements here

 

Userlevel 4
@halo_rivm make sure to end with the delimiter. Check out our article on splitting SQL statements here

 

Good call. Fixed the code above.

Try something like the following in a SQLExecutor:

FME_SQL_DELIMITER @

begin transaction;

insert into ...;
insert into ...;
delete from ...;
delete from ...;

commit transaction;
@

The important bit is to re-define the FME SQL delimiter to a character that isn't used by your SQL commands. If you set FME_SQL_DELIMITER to a semi-colon, then FME will split up your all commands and send them to your backend database in separate transactions, which is why the database says that there's no active transaction when "commit transaction" comes.

@david_r: Very helpfull! Also the link @TiaAtSafe gave: learned a lot.

Thanks!

Try something like the following in a SQLExecutor:

FME_SQL_DELIMITER @

begin transaction;

insert into ...;
insert into ...;
delete from ...;
delete from ...;

commit transaction;
@

The important bit is to re-define the FME SQL delimiter to a character that isn't used by your SQL commands. If you set FME_SQL_DELIMITER to a semi-colon, then FME will split up your all commands and send them to your backend database in separate transactions, which is why the database says that there's no active transaction when "commit transaction" comes.

sql-statement-in-sqlexecutor.txt

 

The solution presented looks very logical and understandable.

 

But it didn't work (yet).

 

I stil get the warning, in fact, now one warning extra.

 

A part of the logging:

 

-------------------------------------------------

 

PostgreSQL 9.3.18, compiled by Visual C++ build 1600, 64-bit

 

SQLExecutorLoadDepositions(QueryFactory): 5 input features read

 

WARNING: there is already a transaction in progress

 

NOTICE: COPY tmp_results FROM 'D:\aerius\M18\Laden calculatieresultaten\03_calculatie_csv_bestanden\NietVerfijnd\Depositie\N2k\er-gcnsector_3811_all_receptors_20171003_calculation_year-2015_substance-NH3_sector-1800_resulttype-deposition.csv' DELIMITER ';' CSV HEADER Starting @ Fri Mar 23 10:40:55.349000 2018 CET

 

NOTICE: COPY tmp_results FROM 'D:\aerius\M18\Laden calculatieresultaten\03_calculatie_csv_bestanden\NietVerfijnd\Depositie\N2k\er-gcnsector_3811_all_receptors_20171003_calculation_year-2015_substance-NH3_sector-1800_resulttype-deposition.csv' DELIMITER ';' CSV HEADER Done @ Fri Mar 23 10:40:57.427000 2018 CET

 

WARNING: there is no transaction in progress

 

WARNING: there is already a transaction in progress

 

NOTICE: COPY tmp_results FROM 'D:\aerius\M18\Laden calculatieresultaten\03_calculatie_csv_bestanden\NietVerfijnd\Depositie\N2k\er-gcnsector_3811_all_receptors_20171003_calculation_year-2015_substance-NOX_sector-1800_resulttype-deposition.csv' DELIMITER ';' CSV HEADER Starting @ Fri Mar 23 10:42:10.992000 2018 CET

 

NOTICE: COPY tmp_results FROM 'D:\aerius\M18\Laden calculatieresultaten\03_calculatie_csv_bestanden\NietVerfijnd\Depositie\N2k\er-gcnsector_3811_all_receptors_20171003_calculation_year-2015_substance-NOX_sector-1800_resulttype-deposition.csv' DELIMITER ';' CSV HEADER Done @ Fri Mar 23 10:42:13.178000 2018 CET

 

WARNING: there is no transaction in progress

 

:

 

:

 

-------------------------------------------------

 

 

Try something like the following in a SQLExecutor:

FME_SQL_DELIMITER @

begin transaction;

insert into ...;
insert into ...;
delete from ...;
delete from ...;

commit transaction;
@

The important bit is to re-define the FME SQL delimiter to a character that isn't used by your SQL commands. If you set FME_SQL_DELIMITER to a semi-colon, then FME will split up your all commands and send them to your backend database in separate transactions, which is why the database says that there's no active transaction when "commit transaction" comes.

@david_r, @TiaAtSafe: All warnings disappeared when I removed the "begin;" and "commit;". But this does not feel right. I get the feeling (I have to figure out a way to test!) that after every command a autocommit takes place. And this is not what I want. When I google I get the impression that it's not possible in PostgreSQL to commit (and thus to rollback) all statements up until previous commit. Instead the savepoint-mechanism is suggested. If this is the case, I think coming from Oracle to PostgreSQL is a bit of a setback.

 

 

Try something like the following in a SQLExecutor:

FME_SQL_DELIMITER @

begin transaction;

insert into ...;
insert into ...;
delete from ...;
delete from ...;

commit transaction;
@

The important bit is to re-define the FME SQL delimiter to a character that isn't used by your SQL commands. If you set FME_SQL_DELIMITER to a semi-colon, then FME will split up your all commands and send them to your backend database in separate transactions, which is why the database says that there's no active transaction when "commit transaction" comes.

@david_r, 
@TiaAtSafe: I tested the commit/rollback mechanism in a postgreSQL client. Then the rollback funtions like it should: It rollbacks up until the last executed commit. So it looks like an FME-thing. Therefore I reported an error (call C135638).

 

Badge +10

Hi, anyone found a response on the ticket C135638 of @halo_rivm​ ?

Userlevel 2
Badge +13

Hi, anyone found a response on the ticket C135638 of @halo_rivm​ ?

Hello @Helmoet de Zwijger​ , unfortunately this hasn’t been resolved yet. The ticket is FMEENGINE-9273 - autocommits after each new SQL statement. If you have a workspace with multiple statements in a SQLExecutor, you could try breaking them up so it only runs a single statement at a time, but it still may commit after each.. Not totally sure if there's a workaround for this as of now.

Is your question regarding postgres or is this regarding a different database? Best, Kailin

Badge +10

Hi @kailinatsafe thanks for answering.​ This is indeed for our postgres database. However, we also use Oracle and Snowflake databases. 😊 The main reason why I was wondering is the use of FME_SQL_DELIMITOR. There is obviously a difference between:

FME_SQL_DELIMITER ;
drop table first_table;
create table first_table as select * from other_table;
insert into first_table (colum1) values (value1);
select * from first_table;

and

FME_SQL_DELIMITER @
drop table first_table;
create table first_table as select * from other_table;
insert into first_table (colum1) values (value1);
select * from first_table;
@

The question is if the table first_table would be there if one of the following statements fails and if this depends on the character following FME_SQL_DELIMITER .

Userlevel 2
Badge +13

Hi @kailinatsafe thanks for answering.​ This is indeed for our postgres database. However, we also use Oracle and Snowflake databases. 😊 The main reason why I was wondering is the use of FME_SQL_DELIMITOR. There is obviously a difference between:

FME_SQL_DELIMITER ;
drop table first_table;
create table first_table as select * from other_table;
insert into first_table (colum1) values (value1);
select * from first_table;

and

FME_SQL_DELIMITER @
drop table first_table;
create table first_table as select * from other_table;
insert into first_table (colum1) values (value1);
select * from first_table;
@

The question is if the table first_table would be there if one of the following statements fails and if this depends on the character following FME_SQL_DELIMITER .

Hello @Helmoet de Zwijger​ , I think the first statement will run one after another, commiting after each. The second statement in your response I think will run as a single 'block' but each line will run individually. The SQL_DELIMITER was originally added for a SQL statement that are too long to be contained on a single line, for example the following would likely fail without SQL_DELIMITER present:

 

SELECT * from Table 1
INNER JOIN table 2 on
table1.attr1 = table2.attr1

 

Would you be able to clarify your question for me? Did you have a more specific question about either of those statements? If you haven't come across this article yet, it does a really good job at explaining SQL_DELIMITER. Best, Kailin.

 

Reply