Re: [HACKERS] Stable functions problem
Tom Lane wrote: Gaetano Mendola [EMAIL PROTECTED] 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 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... Yes, I'll take your suggestion as gold. Regards Gaetano Mendola ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] 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
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [HACKERS] Stable functions problem
Gaetano Mendola [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
