On Thu, 2010-11-04 at 11:07 -0600, Alex Hunsaker wrote:
> On Thu, Nov 4, 2010 at 03:54, Hannu Krosing <hannu@2ndquadrant.com> wrote:
> >> > try:
> >> > plpy.execute("insert into foo values(1)")
> >> > except plpy.UniqueViolation, e:
> >> > plpy.notice("Ooops, you got yourself a SQLSTATE %d", e.sqlstate)
> >>
> >> Are you sure that having each try/except use a subtransaction is the
> >> right way to do it ?
>
> I assumed the try was purely so you could 'catch' things. And did not
> mean run it in a subtransaction (without the try block it still runs
> in one).
So your plan was to have some savepoint before each execute ?
How would one rollback the latest transaction ?
Or is it something else you mean by "subtransaction" ?
> Personally, I was looking more at:
>
> >> > except plpy.UniqueViolation, e:
> >> > plpy.notice("Ooops, you got yourself a SQLSTATE %d", e.sqlstate)
>
> Which to me says if SPI has an error we get a nice error object back,
> that also lets you do the normal exception catching dance (if thats
> what you are in to...) and translates IMO better to how plpgsql works
> ("exception when unique_violation").
I see. "exception when unique violation" in plpgsql does automatic
rollback to block start (matching BEGIN) so I assumed that your
try/except sample is designed to do something similar
> > Another objection
> >
> >> I'd like to make it more explicit and use
> >>
> >> with plpy.subtransaction():
> >> do your stuff
>
> Sounds more like a savepoint?
Yeah. SAVEPOINT command is how you start a "subtransaction" in plain
SQL.
--
-------
Hannu Krosing
PostgreSQL Infinite Scalability and Preformance Consultant
PG Admin Book: http://www.2ndQuadrant.com/books/