Can I use EXCEPTIONs in a FOR LOOP to force continuation on error?

Question

Can I use EXCEPTIONs in FOR loops to force continuation when (predictable or not) errors occur? Here is a snippet:

FOR temp_rec IN tlcursor LOOP
  tl2 := temp_rec; --the location to be updated
  --Do the Routing and UPDATE the taxilocs row.
  UPDATE taxilocs20120113 
     SET route = pgr_trsp (
        'SELECT * FROM th_2po_4pgr',
        tl1.map_id, tl1.map_pos, tl2.map_id, tl2.map_pos, false, true);
  tl1 := tl2;
END LOOP;

An example is, that during the first iteration, tl1 is not assigned. Another could be that the function pge_trsp() provides no result. If any of this occurs during execution, can I force the loop to continue?
Update!!
Here is my latest piece of code:

CREATE OR REPLACE FUNCTION fm_seqrouting() RETURNS integer AS $$
--Declarations
DECLARE 
    tlcursor SCROLL CURSOR FOR SELECT 
        oid,
        veh_id,
        date_time AS dt,
        th_2po_4pgr_id AS map_id,
        th_2po_4pgr_position AS map_pos FROM testlocs
        ORDER BY veh_id, dt;
    tl1 RECORD;
    tl2 RECORD;
    i integer;
BEGIN
--CODE to calculate routes and update table
    i := 0;
    FOR temp_rec IN tlcursor LOOP
        tl2 := temp_rec; --the location to be updated
        BEGIN --Nest the UPDATE in a block to esace errors
        --Do the Routing and UPDATE the taxilocs row.
        UPDATE taxilocs20120113 
            SET "pgRoute" = pgr_trsp (
                'SELECT * FROM th_2po_4pgr',
                tl1.map_id, tl1.map_pos, tl2.map_id, tl2.map_pos, false, true)
        WHERE taxilocs20120113.oid = tl2.oid;
        i := i + 1;
        EXCEPTION
            WHEN SQLSTATE '55000' THEN NULL;
            WHEN SQLSTATE 'XX000' THEN NULL;
            WHEN SQLSTATE '38001' THEN NULL;
        END;
        tl1 := tl2;
    END LOOP;
RETURN i;
END;
$$ LANGUAGE plpgsql;  

The truth is it is very straining memory-wise, but I am new to programming (I am not even a programmer). Any tips appreciated!

Answer 1

Yes. You can put the payload in a separate code block with exception handling:

FOR temp_rec IN tlcursor LOOP
   tl2 := temp_rec; --the location to be updated
   --Do the Routing and UPDATE the taxilocs row.
   BEGIN
      UPDATE taxilocs20120113 
      SET    route = pgr_trsp (
      'SELECT * FROM th_2po_4pgr',
      tl1.map_id, tl1.map_pos, tl2.map_id, tl2.map_pos, false, true);
   EXCEPTION WHEN OTHERS THEN
      -- keep looping
   END;
    tl1 := tl2;
END LOOP;

There is an example in the manual.

But I fail to see why you assign tl2 first (instead of tl1), which is bound to cause an exception at the first iteration of the loop. You can avoid the problem a priori by using a FOR loop and instead of an explicit cursor in combination with an enhanced query. See below.

Also, your UPDATE has no WHERE condition, which is almost certainly wrong.

And the function pgr_trsp() looks suspicious to say the least. Passing code as text reeks of SQL injection. See:

• Postgres functions vs prepared queries

Audited function in updated question

Rewriting your code to use set-based logic instead of looping might be cleaner and faster. For starter, you can simplify to something like this (still with a loop, but simplified):

CREATE OR REPLACE FUNCTION fm_seqrouting()
  RETURNS integer
  LANGUAGE plpgsql AS
$func$
DECLARE 
   r record;
BEGIN
   FOR r IN 
      SELECT oid                                -- no proper pk?
           , th_2po_4pgr_id                     AS map_id1
           , th_2po_4pgr_position               AS map_pos1
           , lead(th_2po_4pgr_id)       OVER w  AS map_id2
           , lead(th_2po_4pgr_position) OVER w  AS map_pos2
           , count(*)                   OVER () AS ct
      FROM   testlocs
      WINDOW w AS (ORDER BY veh_id, dt)
      ORDER  BY veh_id, dt              -- don't need order by columns in result
   LOOP
      BEGIN -- may be unnecessary
         UPDATE taxilocs20120113 
         SET    "pgRoute" = pgr_trsp(
                   'SELECT * FROM th_2po_4pgr'
                 , r.last_map_id, r.last_map_pos, r.map_id, r.map_pos, false, true)
         WHERE  taxilocs20120113.oid = r.oid;
      EXCEPTION
         WHEN SQLSTATE '55000' THEN NULL;
         WHEN SQLSTATE 'XX000' THEN NULL;
         WHEN SQLSTATE '38001' THEN NULL;
      END;
   END LOOP;

   RETURN r.ct;
END
$func$;

In particular, using ...

• A FOR loop with implicit cursor instead of an (unwieldy) explicit cursor.
• Window functions.

Answer 2

As Erwin shows, you can do this, but it's pretty inefficient. PostgreSQL has to create a new subtransaction for each BEGIN ... EXCEPTION block, which burns through transaction IDs and means anti-wraparound vacuum is required sooner and more often. It also bloats the internal transaction ID arrays which will slow all backends down, and increases inter-process communication costs.

If at all possible you should instead write your code to avoid the error in the first place. Recursive CTEs and writable CTEs are often useful when trying to replace PL/pgSQL loops.

Without the actual query that backs tlcursor it's hard to rephrase this into a query. I suspect it might be possible to rephrase it as an UPDATE ... FROM, but that won't work if row n depends on row n-1, which it looks like it might in this case. A recursive CTE would be appropriate for that case.

If you edit your question to show sample data, the definition of the tlcursor query, etc, then comment here I might have a play at writing a CTE to replace it.