Re: Writing results while loop ongoing?
От | Merlin Moncure |
---|---|
Тема | Re: Writing results while loop ongoing? |
Дата | |
Msg-id | CAHyXU0y=paQK7B3DPYw=1WO9MfbND4vDEGsThbnMK3fqStbWYQ@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: Writing results while loop ongoing? (James David Smith <james.david.smith@gmail.com>) |
Ответы |
Re: Writing results while loop ongoing?
|
Список | 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
В списке pgsql-novice по дате отправления: