Re: why does plperl cache functions using just a bool for is_trigger

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/