Thread: Stable functions problem

Hi all,
after several weeks away I downloaded today the 8.0rc1 and I
tested it with our application.

I'm having a bounce of errors because IMMUTABLE and STABLE
attributes for some of my functions. Let me explain with an example,
what I do is  ( plpgsql )


my_id_user = sp_id_user( a_login );

IF  my_id_user > 0 THEN    RETURN -5;  -- error code for existing user
END IF;

INSERT INTO users ( login ) VALUES ( a_login );

my_id_user = sp_id_user( a_login );


now here I can continue my function using the my_id_user, unfortunatelly
that sp_id_user is declared as IMMUTABLE this mean that at the second call
of sp_id_user my_id_user will not contain the user id.

This was working untill 7.4, is there a way to force sp_id_user to
be reevaluated ? That function is declared immutable because is ofthen
used in expresssion like this:

select * from user_data where id_user = sp_id_user('login');


I believe is a good idea write in the release notes, with a bigger font, this
optimization about stable and immutable functions.


Regards
Gaetano Mendola

Gaetano Mendola <mendola@bigfoot.com> writes:
> I'm having a bounce of errors because IMMUTABLE and STABLE
> attributes for some of my functions. Let me explain with an example,

Hmm.  This particular example is a bug in exec_eval_simple_expr() ...
if we're going to bypass SPI then we'd better do the things SPI does
that are needed to maintain the correct execution environment, and
as of 8.0 one of those things is to advance ActiveSnapshot.
I've applied a patch for this.  (Memo to self: I'm beginning to wonder
if exec_eval_simple_expr is worth the trouble at all, compared to just
using SPI.  The amount of overhead it saves seems to get less with each
new release.)

> now here I can continue my function using the my_id_user, unfortunatelly
> that sp_id_user is declared as IMMUTABLE this mean that at the second call
> of sp_id_user my_id_user will not contain the user id.

That actually doesn't have anything to do with it --- the same failure
would have occurred if you'd (correctly) declared sp_id_user as STABLE.
So it's a good bug report.  But I trust you do realize you are playing
with fire.  While I have been heard to suggest mislabeling functions
as immutable if they're only going to be used in interactive queries,
I don't think I have ever failed to mention that you *will* get burnt
if you call such functions from other functions.  When this coding
someday does break for you, I won't have any sympathy at all...
        regards, tom lane

Tom Lane wrote:
> Gaetano Mendola <mendola@bigfoot.com> writes:
> 
>>I'm having a bounce of errors because IMMUTABLE and STABLE
>>attributes for some of my functions. Let me explain with an example,
> 
> 
> Hmm.  This particular example is a bug in exec_eval_simple_expr() ...
> if we're going to bypass SPI then we'd better do the things SPI does
> that are needed to maintain the correct execution environment, and
> as of 8.0 one of those things is to advance ActiveSnapshot.
> I've applied a patch for this. 

Thank you. I'll try with the CVS version and I'll let you know.
> (Memo to self: I'm beginning to wonder
> if exec_eval_simple_expr is worth the trouble at all, compared to just
> using SPI.  The amount of overhead it saves seems to get less with each
> new release.)
> 
> 
>>now here I can continue my function using the my_id_user, unfortunatelly
>>that sp_id_user is declared as IMMUTABLE this mean that at the second call
>>of sp_id_user my_id_user will not contain the user id.
> 
> 
> That actually doesn't have anything to do with it --- the same failure
> would have occurred if you'd (correctly) declared sp_id_user as STABLE.
> So it's a good bug report. 

Indeed I had the same "problem" declaring it as STABLE.
> But I trust you do realize you are playing with fire.  While I have been> heard to suggest mislabeling functions as
immutableif they're only 
 
going> to be used in interactive queries,
> I don't think I have ever failed to mention that you *will* get burnt
> if you call such functions from other functions.  When this coding
> someday does break for you, I won't have any sympathy at all...

Yes, I'll take your suggestion as gold.

Regards
Gaetano Mendola