joeknize-bc opened a new issue, #30010:
URL: https://github.com/apache/airflow/issues/30010
### Apache Airflow Provider(s)
snowflake
### Versions of Apache Airflow Providers
This started with apache-airflow-providers-snowflake==4.0.0 and is still an
issue with 4.0.4
### Apache Airflow version
2.5.1
### Operating System
Debian GNU/Linux 11 (bullseye)
### Deployment
Astronomer
### Deployment details
This is affecting both local and hosted deployments
### What happened
We are testing out several updated packages, and one thing that broke was
the SnowflakeOperator when it was executing a stored procedure. The specific
error points to autocommit being set to False:
`Stored procedure execution error: Scoped transaction started in stored
procedure is incomplete and it was rolled back.`
Whereas this used to work in version 3.2.0:
```
copy_data_snowflake = SnowflakeOperator(
task_id=f'copy_{table_name}_snowflake',
sql=query,
)
```
In order for it to work now, we have to specify autocommit=True:
```
copy_data_snowflake = SnowflakeOperator(
task_id=f'copy_{table_name}_snowflake',
sql=query,
autocommit=True,
)
```
[The
code](https://github.com/apache/airflow/blob/599c587e26d5e0b8fa0a0967f3dc4fa92d257ed0/airflow/providers/snowflake/operators/snowflake.py#L45)
still indicates that the default is True, but I believe [this
commit](https://github.com/apache/airflow/commit/ecd4d6654ff8e0da4a7b8f29fd23c37c9c219076#diff-e9f45fcabfaa0f3ed0c604e3bf2215fed1c9d3746e9c684b89717f9cd75f1754L98)
broke it.
### What you think should happen instead
The default for autocommit should revert to the previous behavior, matching
the documentation.
### How to reproduce
In Snowflake:
```
CREATE OR REPLACE TABLE PUBLIC.FOO (BAR VARCHAR);
CREATE OR REPLACE PROCEDURE PUBLIC.FOO()
RETURNS VARCHAR
LANGUAGE SQL
AS $$
INSERT INTO PUBLIC.FOO VALUES('bar');
$$
;
```
In Airflow, this fails:
```
copy_data_snowflake = SnowflakeOperator(
task_id='call_foo',
sql="call public.foo()",
)
```
But this succeeds:
```
copy_data_snowflake = SnowflakeOperator(
task_id='call_foo',
sql="call public.foo()",
autocommit=True,
)
```
### Anything else
It looks like this may be an issue with stored procedures specifically. If I
instead do this:
```
copy_data_snowflake = SnowflakeOperator(
task_id='call_foo',
sql="INSERT INTO PUBLIC.FOO VALUES('bar');",
)
```
The logs show that although autocommit is confusingly set to False, a
`COMMIT` statement is executed:
```
[2023-03-09, 18:43:09 CST] {cursor.py:727} INFO - query: [ALTER SESSION SET
autocommit=False]
[2023-03-09, 18:43:09 CST] {cursor.py:740} INFO - query execution done
[2023-03-09, 18:43:09 CST] {cursor.py:878} INFO - Number of results in first
chunk: 1
[2023-03-09, 18:43:09 CST] {sql.py:375} INFO - Running statement: INSERT
INTO PUBLIC.FOO VALUES('bar');, parameters: None
[2023-03-09, 18:43:09 CST] {cursor.py:727} INFO - query: [INSERT INTO
PUBLIC.FOO VALUES('bar');]
[2023-03-09, 18:43:09 CST] {cursor.py:740} INFO - query execution done
[2023-03-09, 18:43:09 CST] {sql.py:384} INFO - Rows affected: 1
[2023-03-09, 18:43:09 CST] {snowflake.py:380} INFO - Rows affected: 1
[2023-03-09, 18:43:09 CST] {snowflake.py:381} INFO - Snowflake query id:
01aad76b-0606-feb5-0000-26b511d0ba02
[2023-03-09, 18:43:09 CST] {cursor.py:727} INFO - query: [COMMIT]
```
### Are you willing to submit PR?
- [X] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of
Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
