How do you create a table with Sqlalchemy within a transaction in Postgres?

Question

I'm using Sqlalchemy in a multitenant Flask application and need to create tables on the fly when a new tenant is added. I've been using Table.create to create individual tables within a new Postgres schema (along with search_path modifications) and this works quite well.

The limitation I've found is that the Table.create method blocks if there is anything pending in the current transaction. I have to commit the transaction right before the .create call or it will block. It doesn't appear to be blocked in Sqlalchemy because you can't Ctrl-C it. You have to kill the process. So, I'm assuming it's something further down in Postgres.

I've read in other answers that CREATE TABLE is transactional and can be rolled back, so I'm presuming this should be working. I've tried starting a new transaction with the current engine and using that for the table create (vs. the current Flask one) but that hasn't helped either.

Does anybody know how to get this to work without an early commit (and risking partial dangling data)?

This is Python 2.7, Postgres 9.1 and Sqlalchemy 0.8.0b2.

Just found this answer about DROPs: stackoverflow.com/questions/6778942/… If that is also true for CREATEs, then it doesn't sound like there is a way around this. — David K. Hess
– David K. Hess, Commented Apr 2, 2013 at 21:47
Related FAQ over at Sqlalchemy: sqlalchemy.org/trac/wiki/… — David K. Hess
– David K. Hess, Commented Apr 2, 2013 at 21:53
It doesn't look like your Table.create is being done in the same transaction. Try triggering the block and check the output of SELECT * FROM pg_stat_activity. — sayap
– sayap, Commented Apr 2, 2013 at 22:06
Assuming sess is the session, you can do sess.execute(CreateTable(tenantX_tableY)) instead. — sayap
– sayap, Commented Apr 2, 2013 at 22:09
Sorry, but What do you mean by "triggering the block"? Regardless, I think you are right, but I'm not sure how to get Sqlalchemy to stick the create into my current transaction because it wants a connectable to do it on. — David K. Hess
– David K. Hess, Commented Apr 2, 2013 at 22:10

sayap · Accepted Answer · 2013-04-03 21:19:06Z

4

(Copy from comment)

~~Assuming sess is the session, you can do sess.execute(CreateTable(tenantX_tableY)) instead.~~

EDIT: CreateTable is only one of the things being done when calling table.create(). Use table.create(sess.connection()) instead.

edited Apr 3, 2013 at 21:19

answered Apr 2, 2013 at 22:48

sayap

6,2852 gold badges38 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

David K. Hess Over a year ago

One thing I'm still working on. One of my tables has an enum and CreateTable isn't creating it. Any suggestions on how to handle that?

sayap Over a year ago

@DavidK.Hess Hmm.. I have no experience with enum. The project I works on has to support different RDBMS, and enum is something that I would like to use but can't.

zzzeek Over a year ago

wouldn't you want to be using table.create(sess.connection()) instead? that will do the ENUM types contained within also.

sayap Over a year ago

@zzzeek Right. I wrongly thought both sess.execute(CreateTable(table)) and table.create(sess.connection()) do the same thing, and happen to like the former's syntax better. Turn out visit_table does a lot more than just CreateTable.

David K. Hess Over a year ago

Well, that's much better and straight from the source. I removed my update about Enum's from the question.

Collectives™ on Stack Overflow

How do you create a table with Sqlalchemy within a transaction in Postgres?

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related