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

Search Discussions

  • Benjamin Minshall at Nov 2, 2006 at 11:51 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedNov 2, '06 at 12:46p
activeNov 2, '06 at 11:51p
posts2
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Benjamin Minshall: 1 post Robins: 1 post

People

Translate

site design / logo © 2021 Grokbase