2

I'm trying to run VACUUM via SQLAlchemy, and it does not work, because it's in transaction block.

with engine.begin() as connection:
    S = orm.session.sessionmaker()()
    pd.io.sql.execute('DROP TABLE IF EXISTS %s' % table_name, connection)
    df.to_sql(table_name, connection, chunksize=1000)
    S.commit()
    S.close()

engine.execute('VACUUM')

As you see, I do manually commit and close the session, but still there's an exception:

File "../__init__.py", line 115, in write_file engine.execute('VACUUM')
...
cursor.execute(statement, parameters)
sqlalchemy.exc.InternalError: (psycopg2.InternalError) VACUUM cannot run inside a transaction block
[SQL: 'VACUUM']

I tried all ways, and read through other cases and recommendations, but everyone seems to recommend autocommit which I can't use in this case.

How can I make VACUUM work here?

culebrón
  • 34,265
  • 20
  • 72
  • 110
  • As far as I know pandas will create its own session with given engine, which it will commit and close after executing given command. Could it interfere with your manual commit? – Primer Nov 04 '17 at 09:31
  • https://stackoverflow.com/questions/3931951/is-it-possible-to-issue-a-vacuum-analyze-tablename-from-psycopg2-or-sqlalche – Vao Tsun Nov 04 '17 at 09:43
  • @Primer may be. I see there are 2 connection objects, but I could not find a commit handle in the one for pandas. – culebrón Nov 04 '17 at 15:22
  • @VaoTsun The answers in that question again recommend autocommit. But I want either drop & create, or nothing. If there's a mistake in the data, I want to roll back without dropping. As I understand, with autocommit it's impossible because `drop` will be committed immediately. – culebrón Nov 04 '17 at 15:23
  • `vacuum` cant be part of transaction. so you can open transaction, perform drop and commit, then set autocommit to true and do vacuum in autocommit and then set autocommit to false again - right?.. – Vao Tsun Nov 04 '17 at 16:43

0 Answers0