Airflow PostgreSQL操作符事务和提交

huangapple go评论72阅读模式
英文:

airflow postgresoperator transaction and commits

问题

From the documentation of airflow:

https://airflow.apache.org/docs/apache-airflow-providers-postgres/stable/_api/airflow/providers/postgres/operators/postgres/index.html

关于 autocommit 的说明如下:

autocommit (bool) – 如果为 True,每个 命令 都会自动提交。(默认值:False)

所以,如果我有两个 命令(语句),如下:

my_operator = PostgresOperator(
task_id="mytask",
postgres_conn_id="myconn",
autocommit=True,
sql="""

SELECT * FROM FILMS;

DELETE FROM films USING producers
WHERE producer_id = producers.id AND producers.name = 'foo'""";

那么这是否会被视为两个具有两个提交的事务?还是一个事务有两个提交?(我猜这甚至不可能)。关键是如果其中一个语句失败,是否会以原子方式回滚所有内容?

谢谢。

英文:

From the documentation of airflow:

https://airflow.apache.org/docs/apache-airflow-providers-postgres/stable/_api/airflow/providers/postgres/operators/postgres/index.html

Says this about autocommit:

autocommit (bool) – if True, each command is automatically committed. (default value: False)

So if I have two commands(statements), as such:

my_operator = PostgresOperator(
    task_id="mytask",
    postgres_conn_id="myconn",
    autocommit=True,
    sql="""

SELECT * FROM FILMS;

DELETE FROM films USING producers
  WHERE producer_id = producers.id AND producers.name = 'foo'""";

Does that mean this will be treated as two transactions with two commits ? Or one transation with two commits ? (guess this is not even possible). the point is if one of the statements fail will everything rollback in an atomic way ?

Thanks.

答案1

得分: 1

The postgres provider uses psycopg2 as you can see in the source code:

from psycopg2.sql import SQL, Identifier

And if we refer to the autocommit documentation:

> It is possible to set the connection in autocommit mode: this way all the commands executed will be immediately committed and no rollback is possible...

So if you put a SELECT 1/0 in between your first and second action, I believe the first action will still be applied.

But the best way to make sure is to test it, replace the first action with an INSERT and then modify the delete to cancel it out. Then put a failing operation in between, run it, and go see the state of your DB.

英文:

The postgres provider uses psycopg2 as you can see in the source code:

from psycopg2.sql import SQL, Identifier

And if we refer to the autocommit documentation:

> It is possible to set the connection in autocommit mode: this way all the commands executed will be immediately committed and no rollback is possible...

So if you put a SELECT 1/0 in between your first and second action, I believe the first action will still be applied.

But the best way to make sure is to test it, replace the first action with an INSERT and the modify the delete to cancel it out. Then put a failing operation in between, run it and go see the state of your DB.

huangapple
  • 本文由 发表于 2023年6月12日 14:02:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/76453961.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定