Thread: Questions regarding interaction of stored functions and transactions

I'm a little fuzzy on this, and I've been unable to find docs that clear
it up for me.  A pointer to a helpful doc would be just as welcome as an
outright explanation ;)

Let's take the following fictional scenerio:

BEGIN;
INSERT INTO table1 VALUES ('somestring');
INSERT INTO table1 VALUES ('anotherstring');
SELECT user_defined_function();
COMMIT;

In this case, user_defined_function() does a lot more table manipulation.
I don't want that to be done if any statement prior fails, but it seems as
if it's always done, regardless.  It seems as if the second INSERT is not
executed if the first fails, but the function is always called.

So ... I'm a little fuzzy on this.  Is there a doc that details this
behaviour?

TIA.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com

>Let's take the following fictional scenerio:
>
>BEGIN;
>INSERT INTO table1 VALUES ('somestring');
>INSERT INTO table1 VALUES ('anotherstring');
>SELECT user_defined_function();
>COMMIT;
>
>In this case, user_defined_function() does a lot more table manipulation.
>I don't want that to be done if any statement prior fails, but it seems as
>if it's always done, regardless.  It seems as if the second INSERT is not
>executed if the first fails, but the function is always called.
>
>
If any one of the statements within the transaction (including the
function) fails the entire statement will need to rollback.

If you are running 8 you can use savepoints to only have certain
parts of the entire transaction rollback and then continue forward.

Sincerely,

Joshua D. Drake



>So ... I'm a little fuzzy on this.  Is there a doc that details this
>behaviour?
>
>TIA.
>
>
>


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL Replicator -- production quality replication for PostgreSQL

Bill Moran <wmoran@potentialtech.com> writes:
> Let's take the following fictional scenerio:

> BEGIN;
> INSERT INTO table1 VALUES ('somestring');
> INSERT INTO table1 VALUES ('anotherstring');
> SELECT user_defined_function();
> COMMIT;

> In this case, user_defined_function() does a lot more table manipulation.
> I don't want that to be done if any statement prior fails, but it seems as
> if it's always done, regardless.  It seems as if the second INSERT is not
> executed if the first fails, but the function is always called.

Sorry, I don't believe a word of that.  If the first insert fails,
everything will be rejected until COMMIT.

Possibly you need to show a less fictionalized version of your problem.

            regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Bill Moran <wmoran@potentialtech.com> writes:
> > Let's take the following fictional scenerio:
>
> > BEGIN;
> > INSERT INTO table1 VALUES ('somestring');
> > INSERT INTO table1 VALUES ('anotherstring');
> > SELECT user_defined_function();
> > COMMIT;
>
> > In this case, user_defined_function() does a lot more table manipulation.
> > I don't want that to be done if any statement prior fails, but it seems as
> > if it's always done, regardless.  It seems as if the second INSERT is not
> > executed if the first fails, but the function is always called.
>
> Sorry, I don't believe a word of that.  If the first insert fails,
> everything will be rejected until COMMIT.
>
> Possibly you need to show a less fictionalized version of your problem.

You're right, Tom.  I can't get a simplified reproduction of the problem.

That means that the problem is occurring somewhere else in my program.

--
Bill Moran
Potential Technologies
http://www.potentialtech.com