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

On 4 September 2013 19:48, Merlin Moncure <mmoncure@gmail.com> wrote:
> 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

Hi Merlin,

Thanks for the reply again.

Yes, you are right. I started off looking for ways to write out my
results mid-query, but actually it's errors making the function fail
that are my main concern. They're kind of related. If I could block
all the errors, and just get the function to keep running that would
accomplish the same result for me i.e. not losing data from queries
than ran successfully.

Regarding dropping pg_sleep , this is here so as not to overload the
API servers that I am running the query against. The second pg_sleep
in particular is there as I believe that the generic PL/R error I get
sometimes is due to the server being overloaded. If I run the same
data a few minutes later it works fine. Hence when I get the PL/R
error, I put the query on pause for 60 seconds before it tries to
start again.

Yes, I do have control over the R code. Tips on how to refine the
error would be appreciated. I wrote the R code and PL/R function by
scratching together examples from a few places, with some help from
Mark on the PostGIS mailing list, and then some heavy tweaking myself.
What makes it frustrating is that if I run the row of data that I get
an error with 5 minutes later it works fine. Also if I run it just in
R.

Thanks

James