Re: Writing results while loop ongoing?

On Wed, Sep 4, 2013 at 6:40 AM, James David Smith
<james.david.smith@gmail.com> wrote:
> Hi there,
>
> Having some problems today. Hopefully someone can help. The situation is...
>
> I have a query that takes data from one of my tables, sends it to an
> API, gets some data back, and then writes it to a results table.
>
> The data comes from a table called 'stage_cleaned'
> The query is called 'create_route_geometry_mapquest'
> The results table is called 'route_geom'
>
> The problem that I have been having is that if I send say 50 requests
> to the API, and all but the 49th are completed fine, none of my
> results are wrote to the results table. It's all or nothing. To fix
> this I've tried to put the function within a loop and identified when
> the errors occur and put them as an exception. It now works fine and
> the functions always goes through the entire data. However it only
> write the data to the results table at the end. Is this normal? Could
> it not write the data to the results table after it's made each
> request? Thoughts? Query below:

This is a fundamental principle of SQL: transaction completion is 'all
at once'.  This allows for a lot of simplifications in terms of data
management that I think you'll come to appreciate over time.  But
occasionally it can be annoying if you're trying to move as much
coding into the sever as possible (which is sensible).

"Stored Procedures" (especially) and "Autonomous Transactions" are two
hypothetical features that should allow for server side coding while
being able to manipulate transaction states independently of function
call execution.  These features are much discussed and sadly not
likely to show up soon.  In the meantime, you have two options:

1. Move the controlling code out of the server and into a client side
language.  If you're like me, you should find this distasteful and
silly.
2. Abuse dblink.  With dblink, you can connect the database to itself
and issue transactions. That can commit before the controlling
transaction resolves.  This method has certain issues and annoyances
but can generally be made to work.  Autonomous Transactions is
basically a formalization of this technique.

http://www.postgresql.org/docs/9.2/static/dblink.html

merlin