Greetings, Alchemists, what's the best way to work with temp tables on Postgres?
It's fairly easy to have one created:
tmp_foo = Table('tmp_foo',
metadata,
Column('id', Integer, unique=True),
Column('bar', Integer),
prefixes=['TEMPORARY'])
tmp_foo.create()
The problem is that if I am not sure that the table was created, I
can't use it. The following:
tmp_foo.create(checkfirst=True)
does not work. It issues the following SQL that won't find a match
for temp tables:
select relname from pg_class c
join pg_namespace n on n.oid=c.relnamespace
where n.nspname=current_schema() and lower(relname)=%(name)s
One work around would be to use "ON COMMIT DROP" but I don't now how
to do that since Table() has no `suffixes` parameter.
Any help on that one?
While I'm at it, I might as well state the high level problem that
pushed me to use temp tables. I'm using Xapian to do full text
indexing. Xapian is good to give me a list of document ids that I can
then retrieve from the database but if I want to apply additional
criteria, I have to do the filtering on the database side. On way to
do that is with a huge IN clause, the other is with a temp table. I
like the temp table because I can also use it to order by Xapian
ranking and do the paging on the alchemy side. I could also duplicate
all the criteria on the Xapian side but I want to avoid that if
possible.
Any suggestions for either problems?
--
Yannick Gingras
http://ygingras.net
http://confoo.ca -- track coordinator
http://montrealpython.org -- lead organizer
signature.asc
Description: This is a digitally signed message part.
