Re: transaction problem using cursors

"Pit M." <fmi-soft(at)gmx(dot)de> writes:

> The goal is to maintain a valid cursor that can be used to FETCH other records.
> It would be ideal to skip this record an continue fetching the next record ->
> but how can i achieve this?

Well ideally the answer would be to wrap each FETCH in a savepoint and roll
back to the savepoint if it fails. However I just tried it and this doesn't
work. So I think the answer is you can't, sorry.

One alternative would be to store the result set in a temporary table with an
index on it. Then you could execute a separate query for each record you want
to look up and wrap that query in a savepoint if you want to do it in a
transaction and recover from errors.

It wouldn't have the same performance profile as using a cursor. It would use
more resources in most cases and the lookups will be slower. Though if you
keep this data around for a long time you might actually be better off since
you could use a real non-temporary table and not be forced to keep around
transactions for long periods of time tying up other resources.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com