Re: Writing results while loop ongoing?

On 4 September 2013 14:35, Kevin Grittner <kgrittn(at)ymail(dot)com> wrote:
> James David Smith <james(dot)david(dot)smith(at)gmail(dot)com> wrote:
>
>> the functions always goes through the entire data. However it
>> only write the data to the results table at the end. Is this
>> normal?
>
> It is normal that the work of a transaction is not visible until
> and unless that transaction commits. Execution of a function is
> always part of a single transaction.
>
>> Could it not write the data to the results table after it's made
>> each request?
>
> It does; but the data does not become visible outside the
> transaction writing the data unless the transaction commits.
>
> http://en.wikipedia.org/wiki/ACID#Atomicity
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company

Thanks for the help Merlin and Kevin. I think I understand. dblink
looks like it might be the way to go, but to honest it looks quite
complicated! I think what I might do instead is try to catch the
errors in EXCEPTION clauses. For example the main reason my queries
fail is:

ERROR: R interpreter expression evaluation error
SQL state: 22000

So if I change my query to below, then it should just push on anyway I
think? Have I constructed it correctly?

James

------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION routing_loop() RETURNS VOID AS $$
DECLARE
record_number RECORD;
BEGIN
FOR record_number IN SELECT id FROM stage_cleaned WHERE google_mode =
'walking' AND route_geom IS NULL ORDER BY id LIMIT 5
LOOP
LOOP
BEGIN

PERFORM create_route_geometry_mapquest(
google_origin::text,
google_destination::text,
google_mode::text,
id::text
),
Notice('did stage cleaned id number ' || id ),
pg_sleep(1)
FROM stage_cleaned
WHERE route_geom IS NULL
AND google_mode = 'walking'
AND id = record_number.id
ORDER BY id;

EXIT;

EXCEPTION
WHEN SQLSTATE '22000' THEN
RAISE NOTICE 'There is an error, but hopefully I will continue anyway';
PERFORM pg_sleep(60);
END;
END LOOP;
END LOOP;
RETURN;
END;
$$ LANGUAGE plpgsql;