On Aug1, 2011, at 13:23 , Achim Domma wrote:
> I have tables which store two integer IDs and a floating point rank. So the table MyTable might have these columns:
>
> EntityID -> int
> PropertyID -> int
> Rank -> float
>
> My algorithm needs to retrieve EntityID-Rank-Pairs for some given PropertyIDs. So I basically want to execute a
"selectEntityID, Rank from MyTable where PropertyID=123 oder by Rank desc". But I need to execute multiple of those
statementsand I don't want to load all the data into memory, but rather iterate over the results step by step, stopping
atcertain thresholds.
>
> My algorithm is somewhat nested and contains logic which I cannot express in SQL,
Are you aware that postgres supports recursive subselects via the SQL standard's WITH RECURSIVE notation? I've found
thatWITH RECURSIVE lets you express a lot of things nicely which are otherwise very difficult, or even impossible, to
expressin SQL. Whether or not WITH RECURSIVE helps depends on whether your algorithm can be stated as a kind of closure
process- i.e., a process where you continue to compute new rows from existing ones until no more new rows can found.
> but retrieving those pairs is the most basic operation I would need.
> Are cursors and option too?
It certainly seems so. Rows are computed and returned on demand when fetched through a cursor - opening the cursor does
*not*,in general, load the whole result set into memory. There are statements where parts of the result are
materializedbefore the first row is returned, though - statements which require a sorting step, for example. But in
yourcase, an index on (PropertyID, Rank DESC) should avoid the need for explicit sorting, and instead allow the
executorto read the rows in the desired output order. Thus, a cursor for your SQL statements should have a small
startupcost and an equally small (and constant) cost per row afterwards.
In fact, for your SQL statement, any hand-crafted code that you might come up with will end up being very similar to
whatthe executor does if you simply us a cursor.
> Is there a limitation for the number of open cursors? One call might open 100 cursors or so.
I don't know of any hard limit, and a couple of 100 cursors doesn't sounds unreasonable. I suggest you simply try it
out.
best regards,
Florian Pflug