From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Ross Boylan <ross(at)biostat(dot)ucsf(dot)edu> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: how to call stored procedures that are writes |
Date: | 2012-05-31 06:28:51 |
Message-ID: | 211.1338445731@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Jonatan Reiners | 2012-05-31 14:03:59 | Re: ERROR: invalid input syntax for integer |
Previous Message | Ross Boylan | 2012-05-31 06:26:26 | permissions |