Re: how to call stored procedures that are writes

Ross Boylan <ross(at)biostat(dot)ucsf(dot)edu> writes:
> I hadn't appreciated that little wrinkle til I saw it in the FAQ. It
> does "raise" a related question.
> Is trapping errors (BEGIN/EXCEPTION in plpgsql) relatively cheap or
> expensive? That is, is it better to
> 1) SELECT to see if an object exists; INSERT if not. or
> 2) INSERT the object, trapping errors if it already exists (assuming
> constraints on the table prevent duplicates)?

An exception block is a fairly expensive thing, because it's basically a
subtransaction. My gut feel is that it's better to have a fast path
that avoids using one; but you'd really be best advised to measure both
ways for your particular situation, if you have a case where it's worth
your trouble to worry about which is faster.

> I gather that trapped exceptions do not abort the transaction in
> plpgsql; I think they do if I trap them in python using psycopg2.

Right, because in one case you're failing only a subtransaction.
If you only look for the error on the client side, it's too late
as far as the server is concerned.

regards, tom lane