On January 29, 2010, Michael Bayer wrote: > > 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. > > from sqlalchemy.schema import CreateTable > from sqlalchemy.ext.compiler import compiles > > @compiles(CreateTable) > def check_temporary(create, compiler, **kw): > table = create.element > ret = compiler.visit_create_table(create) > if 'TEMPORARY' in table._prefixes: > ret += "ON COMMIT DROP" > return ret
Very nice, thanks! > > 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 usually go with the IN clause but I wonder if its possible to > write PG stored procedures that can get to xapian as well (since you > can write them in python or any other language). I would not be too hard to make it run on in PL/Python but PL/Python is a non-safe language so it's a bit of a pain to have new versions of the proc deployed since you need to admin in PG to update it. This is why I'd rather go with a solution on the client side. When you go with the big IN, you sort by full text ranking on the Python side? This forces you to fetch the full result set to have the desired page. My experience is that the IN solution get unbearably slow quite fast. With 40000+ results from Xapian, it can take several seconds to get my results with IN. I doubt that any sane human will go through all those 40k results so it's probably safe to only send the first fer thousands full text ids to the database but our requirements call for an accurate page count. The more I think about it, the more it looks like the stored proc in PL/Python is the only same way to do it. Thank again for all the infos. -- 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.