Re: Writing results while loop ongoing? - Mailing list pgsql-novice

On Wed, Sep 4, 2013 at 12:48 PM, James David Smith
<james.david.smith@gmail.com> wrote:
> On 4 September 2013 14:35, Kevin Grittner <kgrittn@ymail.com> wrote:
>> James David Smith <james.david.smith@gmail.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?

yes, but that's not the question you asked. dblink is mechanic to
write out the data 'mid transaction' -- not necessarily error control.
 if all you care about is error suppression in the loop, then
exception block should fit the bill.    Be advised that since you're
calling pl/r any side effects there  (such as writing to a file) may
not necessarily be rolled back with the transaction.

I would consider dropping the sleep call unless there is a good reason
for it to be there and would consider putting the contents of the
error message in the RAISE, which I would additionally consider
raising to 'warning' (which puts stronger emphasis on it entering the
log).

Also, I would strongly consider indenting your code.  For pl/pgsql, I
typically go with two space indents and absolutely forbid the use of
tabs because they blow up psql when pasting.

BTW, that R error is a fairly generic error.  Do you have control over
the R code? I can give you some tips on how to trace it down.

merlin

> ------------------------------------------------------------------------
> 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;
>
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice