pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Locking vs. Exceptions
2+ messages / 2 participants
[nested] [flat]

* Locking vs. Exceptions
@ 2006-11-02 12:45  Robins <tharakan@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Robins @ 2006-11-02 12:45 UTC (permalink / raw)
  To: 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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Locking vs. Exceptions
@ 2006-11-02 23:17  Benjamin Minshall <minshall@intellicon.biz>
  parent: Robins <tharakan@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Benjamin Minshall @ 2006-11-02 23:17 UTC (permalink / raw)
  To: robins@pobox.com; +Cc: 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@intellicon.biz>
Senior Developer -- Intellicon, Inc.
http://www.intellicon.biz



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2006-11-02 23:17 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-11-02 12:45 Locking vs. Exceptions Robins <tharakan@gmail.com>
2006-11-02 23:17 ` Benjamin Minshall <minshall@intellicon.biz>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox