Thread: Using transactions with plpythonu

Does anyone have an example of using transactions with plpythonu ?

I want to create a function that receives data from a client app, and
then updates three different tables but I want to ensure they all get
updated before I commit.

In my testing so far, it seems that each call to plpy.execute('INSERT
INTO ....') is committed immediately.
Any hints or suggestions or is there a wiki page someone could point me
to ?

Thanks.

On Sun, Jan 21, 2007 at 04:56:23AM -0800, imageguy wrote:
> Does anyone have an example of using transactions with plpythonu ?
>
> In my testing so far, it seems that each call to plpy.execute('INSERT
> INTO ....') is committed immediately.

What happens if you do:

plpy.execute("BEGIN");

before and after

plpy.execute("COMMIT");

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Martijn van Oosterhout wrote:
> On Sun, Jan 21, 2007 at 04:56:23AM -0800, imageguy wrote:
> > Does anyone have an example of using transactions with plpythonu ?
> >
> > In my testing so far, it seems that each call to plpy.execute('INSERT
> > INTO ....') is committed immediately.
>
> What happens if you do:
>
> plpy.execute("BEGIN");
>
> before and after
>
> plpy.execute("COMMIT");
>
> Have a nice day,
> --
> Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> > From each according to his ability. To each according to his ability to litigate.
>
> --4ZLFUWh1odzi/v6L
> Content-Type: application/pgp-signature
> Content-Disposition: inline;
>     filename="signature.asc"
> Content-Description: Digital signature
> X-Google-AttachSize: 190

> What happens if you do:
>
> plpy.execute("BEGIN");
>
> before and after
>
> plpy.execute("COMMIT");
>
> Have a nice day,
> --
Thanks for your reply Martin.
After making my post I decide not to be so lazy and set up a test
environment.
After try exactly what you suggested, I got the following error code
SPI_ERROR_TRANSACTION

A quick search of the documentation and this error code explaination
is;
if any command involving transaction manipulation was attempted (BEGIN,
COMMIT, ROLLBACK, SAVEPOINT, PREPARE TRANSACTION, COMMIT PREPARED,
ROLLBACK PREPARED, or any variant thereof)

So... unless I am missing something, I would suggest you CANNOT us
plpython (or perhaps any other pl language ??) to process transactions
... of course I am a complete newbie, so I am hoping there is someone
more senior on this board that could point the way ??

Thanks again.

G.

21 Jan 2007 06:53:15 -0800, imageguy <imageguy1206@gmail.com>:
> > What happens if you do:
> >
> > plpy.execute("BEGIN");
> >
> > before and after
> >
> > plpy.execute("COMMIT");
> >
> > Have a nice day,
> > --
> Thanks for your reply Martin.
> After making my post I decide not to be so lazy and set up a test
> environment.
> After try exactly what you suggested, I got the following error code
> SPI_ERROR_TRANSACTION
>
> A quick search of the documentation and this error code explaination
> is;
> if any command involving transaction manipulation was attempted (BEGIN,
> COMMIT, ROLLBACK, SAVEPOINT, PREPARE TRANSACTION, COMMIT PREPARED,
> ROLLBACK PREPARED, or any variant thereof)
>
> So... unless I am missing something, I would suggest you CANNOT us
> plpython (or perhaps any other pl language ??) to process transactions
> ... of course I am a complete newbie, so I am hoping there is someone
> more senior on this board that could point the way ??

I have been a newbie for a long time. What if you wrap your commands
in a function? A function is a transaction. The function can be
written in plpython if there is the need. I think that would solve
your problem.

Regards,
--
Clodoaldo Pinto Neto

"imageguy" <imageguy1206@gmail.com> writes:
> In my testing so far, it seems that each call to plpy.execute('INSERT
> INTO ....') is committed immediately.

On what do you base that (erroneous) conclusion?

The fact that a transaction can see its own updates does not mean they
are committed.

            regards, tom lane

"imageguy" <imageguy1206@gmail.com> writes:
> So... unless I am missing something, I would suggest you CANNOT us
> plpython (or perhaps any other pl language ??) to process transactions

I think the point you are missing is that every function already runs
within a transaction.  You can't issue BEGIN/COMMIT from within a
function because that would represent destroying the transaction that
supports your execution of the function.

AFAICT you are worried about whether several different updates issued by
your function will all be committed atomically.  They will be; you don't
need to, and indeed can't, do anything to adjust that.  If there was
some other issue you had, you need to be more specific...

            regards, tom lane

Tom Lane wrote:
> "imageguy" <imageguy1206@gmail.com> writes:
> > So... unless I am missing something, I would suggest you CANNOT us
> > plpython (or perhaps any other pl language ??) to process transactions
>
> I think the point you are missing is that every function already runs
> within a transaction.  You can't issue BEGIN/COMMIT from within a
> function because that would represent destroying the transaction that
> supports your execution of the function.

Tom, I think this is what I was missing, and of course this makes
sense.

> AFAICT you are worried about whether several different updates issued by
> your function will all be committed atomically.  They will be; you don't
> need to, and indeed can't, do anything to adjust that.  If there was
> some other issue you had, you need to be more specific...
>
You are exactly right on this.  I was concered about the atomicity of
the updates.
In the test environment I setup, it appeared to me that each "INSERT"
was being committed immediately becuase and if the next transaction
failed it seemed I could still see the the previous transaction after
the function ended (ie. using pgAdminIII)

After reading your post, I will reset my "test" environment.  Clearly I
had a flaw somewhere or didn't understand the results I was seeing.

Thanks so much for your guidance on this. I will post back here once I
have completed the retesting.

Thanks.
Geoff.

Tom Lane wrote:
> "imageguy" <imageguy1206@gmail.com> writes:
> > In my testing so far, it seems that each call to plpy.execute('INSERT
> > INTO ....') is committed immediately.
>
> On what do you base that (erroneous) conclusion?
>
> The fact that a transaction can see its own updates does not mean they
> are committed.
>
Tom, I newbie and have only been using postgresql for about 2-3 weeks,
so I hope you will forgive my for jumping to an erroneous conclusion.
As stated in a previous post I will rework my test enviroment so I have
a better understanding functions and transactions.

Thanks again help.

Geoff.