PostgreSQL - Functions/transactions

Hi - posted this to a PostgreSQL function but not got a response yet, wondered if anyone else has run into this one

I always used stored procedures to house my business logic with MSSQL and plan to continue that with my PostgreSQL 9.3 installation and plpgsql functions.

So far so good but have run into an issue I hope someone can help with.

I have a function that writes information to a table then calls a second function to send an email based on this newly created row. This second is a generic function used by quite a few others. Problem is that the email function can’t see data written to the tables from the first function.

I’m pretty sure that it’s a transaction issue but can’t seem to find a way around this. I would have thought that any functions called within other functions would be part of the same transaction and see data accordingly but this doesn’t seem to be the case.

Basically any data written to a table is not available to any functions called after that INSERT/UPDATE even though they are still part of the same transaction.

Whereas, any data written to a table is written to a table is available to the following SQL statements in the same transaction so long as it’s within the same function.

Any ideas what I’m missing here?

Hope I’ve explained this ok! Any and all help greatly appreciated.

Could you use a trigger on the table to invoke the second function?

Yep - could do, I have a whole bunch of triggers to handle auditing. Not ideal though

It’s also a fundamentals question. I’m going to have to change the way I develop my functions if I can’t get round this issue. Seems very strange to me that data inserted in one transaction isn’t available to other functions even though they share a transaction.

I must be missing something here.

Hmm.

create table test (i integer);
create function f_getcount() returns bigint as
$$
select count(*) from test $$
language sql;
create function f_test() returns bigint as
$$ insert into test values (1);
select f_getcount();
$$ language sql
start transaction;
select * from f_getcount();

returns 0. So apparently functions cannot see always see changes done within the same transaction. Wasn’t aware of that myself.

Oops, no the last line should of course be
select * from f_test();

which returns 1 as expected.

What do your functions look like?

Can’t you commit and call your function?

If you can’t commit, check if a SAVEPOINT makes your data accessible.

Rick - Not from within the function itself as each function is a transaction in it’s own right. Savepoints not available in plpgsql functions.

Think I may be unto something though, an Exception block effectively builds a subtransaction. That may be the way forward, we shall see.

Also, to commit the transaction go back to Xojo and call the second function means two trips to the DB and in a webapp I want to avoid that. Plus, I want transaction handling t be done at the database function level, Xojo shouldn’t get involved.

Post your findings here later. Curious about this behavior and workarounds. :wink:

embarrassed to say it - but I should :slight_smile:

Complete and total user error…

Everything was behaving as it should transaction wise, I had a malformed variable assignment statement that was blowing everything out the water.

Sorry for the confusion!

Thanks for sharing, don’t feel embarrassed, it was an interesting subject. :wink:

Thought that may post doesn’t really help others learn from my mistake. Here’s what caused the problem in my function and my headaches.

This statement works and what I was using for variable assignment (no select or perform just this)

Variable = column from table

I therefore used this type of statement for assigning multiple variables values and this doesn’t work, this was the cause of the problems

Variable = column, variable2 = column2 from table

This gives the error (2 columns returned by the query), though this error wasn’t reporting back to my function call

so assigning 1 variable like the first statement worked but assigning 2 variables as per the 2nd statement doesn’t. Turns out I should be using a SELECT INTO statement as follows

SELECT INTO variable 1, variable 2 column1, column2 FROM table;

so my final query should have been

SELECT INTO userusername, userfirstname, usersurname, useremailaddress ws.username, p.firstname, p.surname, e.emailaddress FROM public.tblprofile AS p INNER JOIN public.tblemailaddress AS e ON p.profileid = e.profileid AND e.primaryemail = TRUE INNER JOIN public.tblwebsiteuser AS ws ON p.websiteuserid = ws.websiteuserid WHERE p.websiteuserid = p_websiteuserid;

All working now.

Thanks
Pat

Yes, this syntax is SELECT INTO var-list column-list FROM query

Between var-list and column list a space or new line is expected to separate both blocks. :wink:

( I wrote this just to clarify a bit more, as you forgot to remove the spaces in “Variable 1, Variable 2” in your example. :wink: )