Questions regarding interaction of stored functions and transactions

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