Multiple append failure and Transactions

[pantonis]

We are building a data warehouse and we have dozens of fact tables that need to be appended.

Problem is if one of the fact table inserts fails we want to rollback all other fact tables. We have tried to do it using transactions and we get "The storage engine for the table doesn't support SAVEPOINT" 

We understand that CS does not support ACID transactions the way other engines do, but we are stuck and we don't know how to handle the scenario above which is a very common one.

Does anyone knows how this can be handled in MCS?

thank you in advance

[Roland Noland]

Hey,

Could you share the high-level overview of what you are doing in a transaction?

Regards,

Roman

пт, 12 мая 2023 г. в 12:20, pantonis <anton...@gmail.com>:

--
You received this message because you are subscribed to the Google Groups "MariaDB ColumnStore" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mariadb-columns...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mariadb-columnstore/b4792957-ed5d-4e2e-9505-52c6feb16058n%40googlegroups.com.

[pantonis]

I'm doing the following:

every n time calculating fact tables

then

Insert Fact1

Insert Fact2

Insert Fact3

Insert Fact4

Insert Fact5

Insert Fact6

......

Insert Fact 99

Update LastProcessingPeriod

Transaction Commit

The reason we are doing this is that in case of failure of any of the fact table, to be able to recalculate the entire period based on the LastProcessingPeriod and to avoid expensive manual interventions or automated deletions of fact tables that completed successfully as MCS slow deletes.

Thank you

[Serguey Zefirov]

How insert can fail? What is typical failure scenario with your
inserts? Is it a constraint-based failure?

I am asking to better understand what is going on. A quick attempt to
reproduce your problem as I understand it failed.

[pantonis]

Network issue, timeout etc...

[drrtuy]

Hm, I was under impression that ROLLBACK w/o savepoint works for MCS.

MariaDB [test]> create table cs4(i bigint)engine=columnstore;
Query OK, 0 rows affected (0.500 sec)

MariaDB [test]> create table cs3(i bigint)engine=columnstore;
Query OK, 0 rows affected (0.455 sec)

MariaDB [test]> begin;
Query OK, 0 rows affected (0.000 sec)

MariaDB [test]> insert into cs3 values(42);
Query OK, 1 row affected (0.245 sec)

MariaDB [test]> insert into cs4 values(42);
Query OK, 1 row affected (0.163 sec)

MariaDB [test]> rollback;
Query OK, 0 rows affected (0.312 sec)

MariaDB [test]> select * from cs3;

Empty set (0.172 sec)

I assume that you are using a named savepoint in the transaction?

Regards,

Roman

пятница, 12 мая 2023 г. в 16:19:37 UTC+3, pantonis: