From: | Gregory Stark <stark(at)enterprisedb(dot)com> |
---|---|
To: | "Pit M(dot)" <fmi-soft(at)gmx(dot)de> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: transaction problem using cursors |
Date: | 2007-06-11 17:06:47 |
Message-ID: | 87lkeqxvx4.fsf@oxford.xeocode.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
"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
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Crawford | 2007-06-11 17:08:19 | Re: When should I worry? |
Previous Message | Steve Crawford | 2007-06-11 17:05:06 | Re: When should I worry? |