Hi Samo, Thanks. I tried that, but it still got the same error message.
I think I'm just going to go with the method described here http://stackoverflow.com/questions/1017463/postgresql-how-to-run-vacuum-from-code-outside-transaction-block It's not the ideal solution, but I don't really want to spend any more time on this. Thank you Samo and Diez for your help, I appreciate it. Phil On Dec 10, 12:18 pm, Samuel Václav <[email protected]> wrote: > Hi, > This should work: > > from sqlalchemy import create_engine > e = create_engine(my_connection_string) > c = e.connect() > c.execute(my_sql) > > Samo > > > > -----Original Message----- > From: [email protected] [mailto:[email protected]] On > > Behalf Of mozillalives > Sent: Thursday, December 10, 2009 3:58 PM > To: SQLElixir > Subject: [elixir] Re: Execute custom query in session > > Hi Diez, > > Thanks for your response. I'm connecting to an older server and > autovacuum is off. I tried creating a new session with autocommit = > True, but still got the same error. Here's my code > > old_sess.close() > new_sess = sessionmaker(autocommit=True) > new_sess.configure(bind=create_engine('postgres:// > user:p...@host/db', echo=True)) > sess = new_sess() > sess.execute('VACUUM FULL ANALYZE table') > > and the output I get is > > 2009-12-10 09:41:43,523 INFO sqlalchemy.engine.base.Engine. > 0x...f2ec VACUUM FULL ANALYZE table > 2009-12-10 09:41:43,523 INFO sqlalchemy.engine.base.Engine. > 0x...f2ec {} > 2009-12-10 09:41:43,523 INFO sqlalchemy.engine.base.Engine. > 0x...f2ec ROLLBACK > finishing failed run, (InternalError) VACUUM cannot run > inside a transaction block > 'VACUUM FULL ANALYZE' {} > > Any idea what I'm doing wrong? > > Thanks, > Phil > > On Dec 10, 6:59 am, "Diez B. Roggisch" <[email protected]> wrote: > > On Wednesday 09 December 2009 23:10:05 mozillalives wrote: > > > > Hi everyone, > > > I'm new to Elixir, but I've been using it for a couple of weeks now > > > and am blown away by its ease of use. Really awesome library. > > > > I'm having a little trouble running a custom query in my current setup > > > and I was wondering if I could get a hand. I connect to a postgres > > > database and, after inserting a few thousand rows, try to run 'VACUUM > > > FULL ANALYZE' using metadata.bind.execute. I get the following error > > > when I do. > > > > InternalError: (InternalError) VACUUM cannot run inside a transaction > > > block > > > > I'm guessing this is because the session is created with > > > autocommit=False, but I'm not clear how to set it to True (I'm also > > > new to SQLAlchemy). Can I alter the existing session to auto commit or > > > will I need to create a new session for this? > > > Yes, I think you should bypass Elixir for this & instead go for a separate > > session created with SQLAlchemy that is set up the way you need it. > > > May I ask why you do this vacuum? AFAIK pg since 8.x does this stuff all > by > > itself. > > > Diez > > -- > > You received this message because you are subscribed to the Google Groups > "SQLElixir" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group > athttp://groups.google.com/group/sqlelixir?hl=en. -- You received this message because you are subscribed to the Google Groups "SQLElixir" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlelixir?hl=en.
