Error trying to use session.execute

[Faheem Mitha]

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:pqxxrocks@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:pqxxrocks@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 drop_constraint_if_exists (t text, k text,
s text = NULL) returns void as $$\nBEGIN\nIF s IS NOT NUL\
L\nTHEN\nEXECUTE 'alter table ' || quote_ident(s) || '.' || quote_ident(t)
|| ' drop constraint ' || quote_ident(k);\nELSE\nEXECUTE\
'alter table ' || quote_ident(t) || ' drop constraint ' ||
quote_ident(k);\nEND IF;\nEXCEPTION WHEN undefined_object THEN\nEND;\n$\
$ LANGUAGE plpgsql;\n" {}

[Michael Bayer]

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.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[Faheem Mitha]

On Sat, 12 Jun 2010 19:58:28 -0400, Michael Bayer
<mik...@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.

[Faheem Mitha]

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.

[Michael Bayer]

On Jun 13, 2010, at 4:03 AM, Faheem Mitha wrote:

On Sat, 12 Jun 2010 19:58:28 -0400, Michael Bayer
<mik...@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.

[Faheem Mitha]

On Sun, 13 Jun 2010 11:20:58 -0400, Michael Bayer <mik...@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.