On 04/11/10 20:43, Hannu Krosing wrote:
> 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).
Nice, lots of input before I was able to read my email :o)
I'm planning to make plpython work just like plperl with regards to
trapping errors from SPI. As Tom noticed, messing with the error
handling mechanism of Python is not a good way of implementing this.
So, basically each plpy.execute() would be internally executed inside a
subtransaction and if SPI would return an error, it would be transformed
into a Python exception and returned to the Python runtime, which will
then handle it as it would handle and exception coming form a C extension.
It would be interesting to provide an explicit way to start
subtransactions, like Hannu proposed:
with plpy.subtransaction(): plpy.execute("select foo()") plpy.execute("select bar()") plpy.execute("select baz()")
(of course that would only work for Python 2.6+, where with blocks were
introduced, we'd have to also provide the primitive functions of
plpy.enter_subxact() and plpy.commit_subxact() (names took at random))
It would set a flag somewhere and start an explicit subtransaction -
after that plpy.execute() would just go ahead and call SPI. This way you
would be sure that you executed a bunch of statements atomically.
Implementing that iss not very high on my priority list, though, as you
can always just wrap foo() bar() and baz() in a separate stored
procedure and call that, thus achieving atomicity (or am I wrong here?).
Cheers,
Jan
PS: I'm wondering if there's any noticable slowdown from always starting
a subxact before doing SPI. Plperl users seemed not to notice, so I
guess I shouldn't worry.
J