[
https://issues.apache.org/jira/browse/AIRFLOW-139?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15548479#comment-15548479
]
Daniel Zohar commented on AIRFLOW-139:
--------------------------------------
This indeed causes problems with (for example) Redshift.
I agree special cases should be discouraged though the current implementation
definitely feels like a special case handling.
There was no issue attached to the original commit
(https://github.com/apache/incubator-airflow/commit/28da05d860147b5e0df37d998f437af6a5d4d178)
but I'm supposing it came due to
https://www.postgresql.org/docs/7.4/static/release-7-4.html.
The documentation states:
"The server-side autocommit setting was removed and *reimplemented in client
applications and languages*. Server-side autocommit was causing too many
problems with languages and applications that wanted to control their own
autocommit behavior, so autocommit was removed from the server and added to
individual client APIs as appropriate."
As far as I can see, psycopg2 supports setting autocommit and I'd be very
surprised if it didn't handle it well.
I tested it locally and I can confirm it works well with 9.5.4 with the
following code:
{code}
>>> import psycopg2
>>> conn_string = "host='127.0.0.1' dbname='db' user='user' password='pwd'"
>>> conn = psycopg2.connect(conn_string)
>>> conn.autocommit = True
>>> cursor = conn.cursor()
>>> cursor.execute('SELECT VERSION();')
>>> cursor.fetchall()
[('PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10)
4.9.2, 64-bit',)]
{code}
In all honesty, I'm not sure what was the purpose of the original fix and I
think those lines should be removed.
> Executing VACUUM with PostgresOperator
> --------------------------------------
>
> Key: AIRFLOW-139
> URL: https://issues.apache.org/jira/browse/AIRFLOW-139
> Project: Apache Airflow
> Issue Type: Bug
> Affects Versions: Airflow 1.7.0
> Reporter: Rafael
>
> Dear Airflow Maintainers,
> h1. Environment
> * Airflow version: *v1.7.0*
> * Airflow components: *PostgresOperator*
> * Python Version: *Python 3.5.1*
> * Operating System: *15.4.0 Darwin*
> h1. Description of Issue
> I am trying to execute a `VACUUM` command as part of DAG with the
> `PostgresOperator`, which fails with the following error:
> {quote}
> [2016-05-14 16:14:01,849] {__init__.py:36} INFO - Using executor
> SequentialExecutor
> Traceback (most recent call last):
> File "/usr/local/bin/airflow", line 15, in <module>
> args.func(args)
> File
> "/usr/local/Cellar/python3/3.5.1/Frameworks/Python.framework/Versions/3.5/lib/python3.5/site-packages/airflow/bin/cli.py",
> line 203, in run
> pool=args.pool,
> File
> "/usr/local/Cellar/python3/3.5.1/Frameworks/Python.framework/Versions/3.5/lib/python3.5/site-packages/airflow/models.py",
> line 1067, in run
> result = task_copy.execute(context=context)
> File
> "/usr/local/lib/python3.5/site-packages/airflow/operators/postgres_operator.py",
> line 39, in execute
> self.hook.run(self.sql, self.autocommit, parameters=self.parameters)
> File
> "/usr/local/Cellar/python3/3.5.1/Frameworks/Python.framework/Versions/3.5/lib/python3.5/site-packages/airflow/hooks/dbapi_hook.py",
> line 109, in run
> cur.execute(s)
> psycopg2.InternalError: VACUUM cannot run inside a transaction block
> {quote}
> I could create a small python script that performs the operation, as
> explained in [this stackoverflow
> entry](http://stackoverflow.com/questions/1017463/postgresql-how-to-run-vacuum-from-code-outside-transaction-block).
> However, I would like to know first if the `VACUUM` command should be
> supported by the `PostgresOperator`.
> h1. Reproducing the Issue
> The operator can be declared as follows:
> {quote}
> conn = ('postgres_default')
> t4 = PostgresOperator(
> task_id='vacuum',
> postgres_conn_id=conn,
> sql=("VACUUM public.table"),
> dag=dag
> )
> {quote}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
