Can't run transaction

[bubnenkoff]

I need to insert in DB list of single INSERT statement as single transaction. Like:

Future<dynamic> sqlInsert(Map body) async {
  try {      
        await connection.transaction( (ctx) async {
            await ctx.query("""INSERT INTO "public"."t1" ( "purchaseNumber")  VALUES ( '123');"""); // 
            await ctx.query("""INSERT INTO "public"."t1" ( "purchaseNumber")  VALUES ( '321');"""); //
            await ctx.query("""COMMIT"""); //
         } 
       
       ).timeout(Duration(seconds: 10));

    }
    on PostgreSQLException catch(e)
    {
      writeLog(e.message);
    }    

  }

But I am getting error:

Unhandled exception: TimeoutException after 0:00:10.000000: Future not completed

Same result on:

        connection.transaction( (ctx) {
            ctx.query("""INSERT INTO "public"."сontract_procedure" ( "purchaseNumber")  VALUES ( '1111111111');"""); // to add ; to the end of every query
            ctx.query("""INSERT INTO "public"."сontract_procedure" ( "purchaseNumber")  VALUES ( '11111111211');"""); // to add ; to the end of every query
            ctx.query("""COMMIT"""); // to add ; to the end of every query     
         } 

What I am missing?

[isoos]

@bubnenkoff: you don't need to call COMMIT in the transaction((ctx) {}) callback. Unspecified callbacks will call commit when the queries return without issues, or, if you need manual abort on the transaction, you can call ctx.cancelTransaction().

[bubnenkoff]

@isoos Do you mean I need to add some callback (that currently not in code)?

[isoos]

You already do have a callback: (ctx) {} is a function that gets called when the transaction is ready.

[bubnenkoff]

@isoos Thanks, but how to detect it was error in transaction?

PostgreSQLSeverity.error : Query failed prior to execution. This query's transaction encountered an error earlier in the transaction that prevented this query from executing.

it's not exception, so I can't handle it.

How to from next method check if all ok, or there was an error?

await connection.transaction( (ctx) {
   for (var s in body['sql'].split(';')) {
        ctx.query(s + ";");  
    }
    // was it complete?
 } 

[isoos]

use async and await:

• (ctx) async {
• await ctx.query(s);

[bubnenkoff]

And try-catch inside it? Like:

        await connection.transaction( (ctx) async {
           for (var s in body['sql'].split(';')) {
              // print(s + ";");
              try {
                  await ctx.query(s + ";"); 
              } // catch
               
            }
            
         } 

[isoos]

Depends on what you want. You should be able to get the exception propagated to the connection.transaction block too, if you execute them in sequence (== with await).

[bubnenkoff]

Big thanks! Could you look at next code and say if I understand all that you said right:

  Future<dynamic> sqlInsert(Map body) async {
  try {
      print("Trying to inserting data");
      print("sql: ${body['sql']}");
        // comma separated inserts as list
        await connection.transaction( (ctx) async {
           for (var s in body['sql']) {
              try {
                  await ctx.query(s); 
              } catch (e) {
                print("cancelTransaction was called");
                connection.cancelTransaction();
              } 
            }  
         });

    }
    on PostgreSQLException catch(e)
    {
       print("There is some duplicates. Removing them in new transaction");
        await connection.transaction( (ctx) async {
           for (var s in body['sql-remove']) { // sql-remove have DELETE queries
              try {
                  await ctx.query(s); 
              } catch (e) {
                // something wrong with removing
              } 
            }  
         });

      writeLog("sqlInsert ", e.message);
    }    

    catch (e) {
        writeLog("11sqlInsert ", e.message);
    }

     return 0; 

  }

Or it should be:

        try { 
            await connection.transaction( (ctx) async {
              for (var s in body['sql']) {
                   await ctx.query(s); 
                }  
              });
          } catch (e) {
              print("cancelTransaction was called");
              await connection.cancelTransaction();
              } 

?