Lists: | pgsql-performance |
---|
From: | Robins <tharakan(at)gmail(dot)com> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | Locking vs. Exceptions |
Date: | 2006-11-02 12:45:53 |
Message-ID: | 36af4bed0611020445s445e3fe8o344f529190a8dfea@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
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
From: | Benjamin Minshall <minshall(at)intellicon(dot)biz> |
---|---|
To: | robins(at)pobox(dot)com |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Locking vs. Exceptions |
Date: | 2006-11-02 23:17:47 |
Message-ID: | 454A7C9B.40207@intellicon.biz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-performance |
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(at)intellicon(dot)biz>
Senior Developer -- Intellicon, Inc.
http://www.intellicon.biz