[sqlalchemy] Re: Error trying to use session.execute
On Sun, 13 Jun 2010 11:20:58 -0400, Michael Bayer mike...@zzzcomputing.com wrote: When an exception occurs in a transaction while Postgresql, you in most cases must issue a rollback() (that is what (InternalError) current transaction is aborted, commands ignored until end of transaction block means, thats a PG message). Therefore you cannot attempt an operation with PG inside a transaction, have it fail, ignore the failure, then continue in the same transaction. You need to roll the transaction back and start a new one, or use an autocommit mode which accomplishes the same thing. As far as 2, I'm not sure what transactionalized execution is. It means a statement is executed while a transaction is in progress. Each= subsequent statement occurs within the same transaction as the previous, until a rollback() or commit() is issued. This is the opposite of autocommit, where each statement occurs in a distinct transaction. Hi Mike, Thanks for the clarifications. I figured out that the failure was the problem, but didn't understand exactly why. So pg doesn't automatically roll back the transaction, apparently. Regards, Faheem. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
[sqlalchemy] Re: Error trying to use session.execute
[This message has also been posted.] On Sat, 12 Jun 2010 19:58:28 -0400, Michael Bayer mike...@zzzcomputing.com wrote: this pattern: try: foo() except: pass will get you killed every time. the difference in result is between the session's default of transactionalized execution and the Connection's default of autocommit execution. Hi Mike, Thanks for the quick reply, as usual. I'm going to assume comment 1 and comment 2 are unrelated. If they aren't, please correct me. As regards 1, I assume you mean try... pass... is a bad idea. I agree, but there is no CREATE LANGUAGE IF EXISTS in PostgreSQL, so CREATE LANGUAGE exits with an error if the language already exists. So, what alternative do you suggest? I guess catching the exception within pg itself is an option - I've done it in at least one other case. The advantage with that approach is that one can then finetune the catching of the exception more precisely. However, I don't know if this is relevant to what you mean. As far as 2, I'm not sure what transactionalized execution is. I tried Googling for this, and came up, for example, with http://www.sqlalchemy.org/docs/dbengine.html#using-transactions-with-connection but this didn't help me understand what the problem was. If the problem is that the transaction does not commit immediately, why should that cause an error? I remembered that text() has the autocommit=True option, and tried using it with create_drop_constraint_if_exists_function, but it didn't help. Regards, Faheem. On Jun 12, 2010, at 3:30 PM, Faheem Mitha wrote: Hi, While rearranging some Python code using SQLAlchemy, I managed to get this puzzling error. The oddest bit is that using conn.execute works, while session.connect doesn't. Below, the code that doesn't work, the code that works, and last, the traceback for the code that doesn't work. If anyone could explain to me what I'm missing, I'd appreciate it. Regards, Faheem. ** from sqlalchemy.sql import text create_plpgsql = text( CREATE LANGUAGE plpgsql; ) create_drop_constraint_if_exists_function = text( CREATE OR REPLACE FUNCTION drop_constraint_if_exists (t text, k text, s text = NULL) returns void as $$ BEGIN IF s IS NOT NULL THEN EXECUTE 'alter table ' || quote_ident(s) || '.' || quote_ident(t) || ' drop constraint ' || quote_ident(k); ELSE EXECUTE 'alter table ' || quote_ident(t) || ' drop constraint ' || quote_ident(k); END IF; EXCEPTION WHEN undefined_object THEN END; $$ LANGUAGE plpgsql; ) * this does not work * from sqlalchemy.orm import sessionmaker dbstring = postgres://snp:pqxxro...@localhost:5432/affy6_faheem from sqlalchemy import create_engine db = create_engine(dbstring) Session = sessionmaker(bind=db) session = Session() try: session.execute(create_plpgsql) except: pass session.execute(create_drop_constraint_if_exists_function) * this works * rrom sqlalchemy.orm import sessionmaker dbstring = postgres://snp:pqxxro...@localhost:5432/affy6_faheem from sqlalchemy import create_engine db = create_engine(dbstring) conn = db.connect() try: conn.execute(create_plpgsql) except: pass conn.execute(create_drop_constraint_if_exists_function) Traceback (most recent call last): File stdin, line 34, in module File /usr/lib/pymodules/python2.5/sqlalchemy/orm/session.py, line 753, in execute clause, params or {}) File /usr/lib/pymodules/python2.5/sqlalchemy/engine/base.py, line 824, in execute return Connection.executors[c](self, object, multiparams, params) File /usr/lib/pymodules/python2.5/sqlalchemy/engine/base.py, line 874, in _execute_clauseelement return self.__execute_context(context) File /usr/lib/pymodules/python2.5/sqlalchemy/engine/base.py, line 896, in __execute_context self._cursor_execute(context.cursor, context.statement, context.parameters[0], context=context) File /usr/lib/pymodules/python2.5/sqlalchemy/engine/base.py, line 950, in _cursor_execute self._handle_dbapi_exception(e, statement, parameters, cursor, context) File /usr/lib/pymodules/python2.5/sqlalchemy/engine/base.py, line 931, in _handle_dbapi_exception raise exc.DBAPIError.instance(statement, parameters, e, connection_invalidated=is_disconnect) sqlalchemy.exc.InternalError: (InternalError) current transaction is aborted, commands ignored until end of transaction block \nCREATE OR REPLACE FUNCTION
[sqlalchemy] Re: Error trying to use session.execute
On Sun, 13 Jun 2010 13:33:39 +0530, Faheem Mitha fah...@email.unc.edu wrote: I'm going to assume comment 1 and comment 2 are unrelated. If they aren't, please correct me. As regards 1, I assume you mean try... pass... is a bad idea. I agree, but there is no CREATE LANGUAGE IF EXISTS in PostgreSQL, so CREATE LANGUAGE exits with an error if the language already exists. So, what alternative do you suggest? I guess catching the exception within pg itself is an option - I've done it in at least one other case. The advantage with that approach is that one can then finetune the catching of the exception more precisely. However, I don't know if this is relevant to what you mean. As far as 2, I'm not sure what transactionalized execution is. I tried Googling for this, and came up, for example, with http://www.sqlalchemy.org/docs/dbengine.html#using-transactions-with-connection but this didn't help me understand what the problem was. If the problem is that the transaction does not commit immediately, why should that cause an error? I remembered that text() has the autocommit=True option, and tried using it with create_drop_constraint_if_exists_function, but it didn't help. [Following up to myself] Adding session.commit() after try: session.execute(create_plpgsql) except: pass makes this work. I'm still not sure what is happening here, but possibly session.execute(create_plpgsql) needs a chance to clean up after itself before another transaction? If that is right, then your two comments were in fact related. :-) I note that setting autocommit=True on create_plpgsql doesn't work, presumably because it errors out, and therefore cannot be committed. Regardless, a expert explanation would be appreciated. Would using something like http://wiki.postgresql.org/wiki/CREATE_OR_REPLACE_LANGUAGE be a reasonable solution to this? Then the try except would not be necessary. Thanks, Faheem. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] Re: Error trying to use session.execute
On Jun 13, 2010, at 4:03 AM, Faheem Mitha wrote: On Sat, 12 Jun 2010 19:58:28 -0400, Michael Bayer mike...@zzzcomputing.com wrote: this pattern: try: foo() except: pass will get you killed every time. the difference in result is between the session's default of transactionalized execution and the Connection's default of autocommit execution. Hi Mike, Thanks for the quick reply, as usual. I'm going to assume comment 1 and comment 2 are unrelated. If they aren't, please correct me. As regards 1, I assume you mean try... pass... is a bad idea. I agree, but there is no When an exception occurs in a transaction while Postgresql, you in most cases must issue a rollback() (that is what (InternalError) current transaction is aborted, commands ignored until end of transaction block means, thats a PG message).Therefore you cannot attempt an operation with PG inside a transaction, have it fail, ignore the failure, then continue in the same transaction.You need to roll the transaction back and start a new one, or use an autocommit mode which accomplishes the same thing. As far as 2, I'm not sure what transactionalized execution is. It means a statement is executed while a transaction is in progress. Each subsequent statement occurs within the same transaction as the previous, until a rollback() or commit() is issued. This is the opposite of autocommit, where each statement occurs in a distinct transaction. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.