[Postgres-xl-general] plpython vs plpgsql

Pavan Deolasee pavan.deolasee at gmail.com
Tue Nov 18 10:25:15 PST 2014


On Tue, Nov 18, 2014 at 10:22 PM, Aaron Jackson <ajackson at revionics.com>
wrote:

>      I started tinkering with some of my functions in plpython rather
> than plpgsql and I was hit with the "Internal subtransactions not supported
> in Postgres-XL" error that comes out of BeginInternalSubTransaction.
>  What's interesting is that the statement that failed succeeded when
> performed under plpgsql.  Here's better pseudo code to describe the
> difference...
>
>  CREATE OR REPLACE FUNCTION SomeFunction("args" INT)
> RETURNS void AS
> $$
> BEGIN
> CREATE TEMPORARY TABLE _myTempTable(id INT);
> END
> $$ LANGUAGE plpgsql;
>
>  Now the version for plpython...
>
>  CREATE OR REPLACE FUNCTION SomeFunction("args" INT)
> RETURNS void AS
> $$
> BEGIN
> pypl.execute("CREATE TEMPORARY TABLE _myTempTable(id INT);")
> END
> $$ LANGUAGE plpgsql;
>
>  So, I'm just curious - are all plpython executed statements wrapped in a
> transactional enclosure?
>
> I haven't studied plpython before, but a quick look suggests that at least
all execute() calls are enclosed in a subtransaction. I don't know the
details in plpython case, but typically subtransactions are used to recover
from failures without requiring to abort the top level transaction. So if
Postgres throws an error and caller is in the top level transaction
context, then it will lead to transaction abort.

For the same reason, plpgsql wraps BEGIN ... END block in a
 subtransactions if the block is using EXCEPTION. In that case, if there is
a failure inside BEGIN .. END block and if the exception is handled, then
just the subtransaction is aborted, but rest of the procedure can still
proceed.

BTW, subtransactions are same as SAVEPOINT. PostgreSQL uses internal
subtransactions in certain procedural languages.

Hope this helps.

Thanks,
Pavan

  *Aaron*
>
>
> ------------------------------------------------------------------------------
> Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
> with Interactivity, Sharing, Native Excel Exports, App Integration & more
> Get technology previously reserved for billion-dollar corporations, FREE
>
> http://pubads.g.doubleclick.net/gampad/clk?id=157005751&iu=/4140/ostg.clktrk
> _______________________________________________
> Postgres-xl-general mailing list
> Postgres-xl-general at lists.sourceforge.net
> https://lists.sourceforge.net/lists/listinfo/postgres-xl-general
>
>


-- 
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.postgres-xl.org/pipermail/postgres-xl-general-postgres-xl.org/attachments/20141118/194ea4c1/attachment.htm>


More information about the postgres-xl-general mailing list