Thread: Locking vs. Exceptions

Hi,

The documentation says that function blocks with exceptions are far costlier than without one.

So if I need to implement an INSTEAD OF trigger (after checking for unique constraint violations) which way should I go ?

1. Get a table lock
2. Use 'Select ... For Update' (which could be used to lock only the desired recordsets)
3. Use Exceptions

Any advice / experiences or even pointers would be helpful.

Thanks
Robins Tharakan

Robins wrote:
> Hi,
>
> The documentation says that function blocks with exceptions are far
> costlier than without one.
>

I recommend against using exceptions.  There is a memory leak in the
exception handler that will cause headaches if it is called many times
in the transaction.

In plpgsql, I would use:

SELECT ... FOR UPDATE;
IF FOUND THEN
    UPDATE ...;
ELSE
    INSERT ...;
END IF;


If you have multiple transactions doing this process at the same time,
you'll need explicit locking of the table to avoid a race condition.

--
Benjamin Minshall <minshall@intellicon.biz>
Senior Developer -- Intellicon, Inc.
http://www.intellicon.biz