[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.
