On Nov 22, 2010, at 12:42 PM, Jon Nelson wrote:
> I have an app where I think I'm going to want to use a TEMPORARY TABLE
> like this:
>
> CREATE TEMPORARY TABLE results AS SELECT .;
>
> and then do two things:
>
> 1. I need a select count(1) from that table
> 2. I need to be able to iterate over the rows in that table
>
> The reason I am using a TEMPORARY table is that the result might be
> quite large - potentially much too large for the client.
> Right now I'm not using a temporary table - I am declaring a cursor
> and a window function (COUNT(*) OVER ()...) and then removing the
> resulting count row from the results before passing them on for
> further processing. Sadly, I need the total result size before I
> return any results. :-(
>
> Anyway, the questions I have are concerned with table reflection:
>
> 1. is table reflection thread safe?
its threadsafe since its just metadata.tables[key] = new_table, though there
could be a bit of a "dogpile" effect if multiple threads are all looking for
the same table.
> 2. the only way I've found for reflecting a temporary table is this sequence:
> a. create table
> b. acquire temporary table namespace with: select nspname from
> pg_namespace n where n.oid = pg_my_temp_schema();
> c. reflect with schema=nspname from above
> 3. When I'm done, I always issue a ROLLBACK. What can I do to make
> sure the MetaData "forgets" about this temporary table?
You should use a different MetaData object that is local to your transaction.
Its also likely a lot easier to use a Table object and emit the CREATE
statement from that, I don't really use reflection for anything that isn't
ad-hoc. If the Table has a ForeignKey to a table in your main MetaData, the
ForeignKey gets a Column object instead of a string (i.e.
ForeignKey(mytable.c.target)).To emit the CREATE, subclass
sqlalchemy.schema.CreateTable and use @compiles, see
http://www.sqlalchemy.org/docs/core/compiler.html#changing-the-default-compilation-of-existing-constructs
as well as http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views for an
example. I would likely get the original "CREATE TABLE" string from the given
compiler (compiler.visit_create_table(element)) and just regexp the phrase
"TEMP" into it.
--
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.
