Re: [HACKERS] Found small issue with OUT params
-Original Message- From: [EMAIL PROTECTED] on behalf of Bruce Momjian Sent: Sat 10/1/2005 1:16 AM To: Jim C. Nasby Cc: Joshua D. Drake; Tony Caduto; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Found small issue with OUT params fix pgxs for spaces in file names I posted a patch for that. http://archives.postgresql.org/pgsql-patches/2005-09/msg00137.php Regards, Dave ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Found small issue with OUT params
Tom Lane wrote: Mike Rylander [EMAIL PROTECTED] writes: Using that logic, a functions with one OUT param would be the same as a function returning a rowtype with only one column, But it's not (and no, I don't want to make it so, because the overhead for the useless record result would be significant). regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend Tom, I hardly think the overhead would be significant on modern processors, I don't think the majority of users are running on Pentium 90s.( I am assuming you mean a performance overhead) The whole point is the current behavior is inconsistent and not expected and should be changed to be inline with the way other DB systems work. What is the point of even allowing a single OUT param then? You might as well just raise a error and tell the user that a single OUT param is not allowed. 8.1 is going to bring even more users over from systems like Firebird, MS SQL and even Oracle, and all of these allow a single OUT param and it returns the name of the OUT param, not the name of the function. Like I said before this behavior is going to make it more difficult to port applications from other systems. How difficult can it be to check if the function has a single OUT param as compared to the old way of using RETURN? Sorry if I am being a pain in the you know what, but I really think I am correct on this matter. Thanks, Tony ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Found small issue with OUT params
On Fri, Sep 30, 2005 at 10:20:34AM -0500, Tony Caduto wrote: Tom, I hardly think the overhead would be significant on modern processors, I don't think the majority of users are running on Pentium 90s.( I am assuming you mean a performance overhead) Um, please read the documention. Returning a tuple is *significantly* more expensive than returning a single value. You have to get the tuple descriptor, allocate memory for the tuple, fill in all the fields with your data... For a single value you just return it. See here for all the details, you really don't want to do it if you don't need to. http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html#AEN30497 Now, you could fudge the parser to automatically alter the name of the value in the function but I'm have no idea how hard that would be... -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgp0BOnOk9s4S.pgp Description: PGP signature
Re: [HACKERS] Found small issue with OUT params
Martijn van Oosterhout wrote: On Fri, Sep 30, 2005 at 10:20:34AM -0500, Tony Caduto wrote: Tom, I hardly think the overhead would be significant on modern processors, I don't think the majority of users are running on Pentium 90s.( I am assuming you mean a performance overhead) Um, please read the documention. Returning a tuple is *significantly* more expensive than returning a single value. You have to get the tuple descriptor, allocate memory for the tuple, fill in all the fields with your data... For a single value you just return it. See here for all the details, you really don't want to do it if you don't need to. http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html#AEN30497 Now, you could fudge the parser to automatically alter the name of the value in the function but I'm have no idea how hard that would be... So you might notice little performance hit bringing back a million rows, and most of these type of single OUT params functions only return one row/value anyway. There would be zero perceivable difference in performance regardless of the extra overhead for a single value/row. As a application developer, I don't care about tuples etc, I just want it to work as expected without having to resort to hacks like creating a second OUT param that is not used, otherwise I would have to change a lot of client code where ever the OUT param is refernced by name instead of position and that is done a lot because the position is more likely to change than the name. The bottom line(regardless of any overhead or if I read the docs about returning a tuple) is that if you have a OUT param it should return that name, not the name of the function, period. Thanks, Tony ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Found small issue with OUT params
So you might notice little performance hit bringing back a million rows, and most of these type of single OUT params functions only return one row/value anyway. There would be zero perceivable difference in performance regardless of the extra overhead for a single value/row. Sounds like we need a test case... up for it? Sincerely, Joshua D. Drake -- Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240 PostgreSQL Replication, Consulting, Custom Programming, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Found small issue with OUT params
On Friday 30 September 2005 11:49, Martijn van Oosterhout wrote:
On Fri, Sep 30, 2005 at 10:20:34AM -0500, Tony Caduto wrote:
Tom,
I hardly think the overhead would be significant on modern processors, I
don't think the majority of users are running on Pentium 90s.( I am
assuming you mean a performance overhead)
Um, please read the documention. Returning a tuple is *significantly*
more expensive than returning a single value. You have to get the tuple
descriptor, allocate memory for the tuple, fill in all the fields with
your data... For a single value you just return it.
ISTM it is better for us to be consistent with the visible behavior than to
have two different behaviors for out param functions just so one can be
faster. That way if people are concerned about the speed difference, they
can rewrite the function without an out param... afaict, as it stands now
you've given them no choice and are forcing them to handle two different
scenarios.
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Found small issue with OUT params
On Fri, Sep 30, 2005 at 10:53:22AM -0700, Joshua D. Drake wrote: So you might notice little performance hit bringing back a million rows, and most of these type of single OUT params functions only return one row/value anyway. There would be zero perceivable difference in performance regardless of the extra overhead for a single value/row. Sounds like we need a test case... up for it? If there is a performance difference my vote is that we bite the bullet for 8.1 and accept the performance hit rather than settle for sub-optimal behavior. Much easier to fix the performance penalty down the road than to fix the behavior. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Found small issue with OUT params
Jim C. Nasby wrote: On Fri, Sep 30, 2005 at 10:53:22AM -0700, Joshua D. Drake wrote: So you might notice little performance hit bringing back a million rows, and most of these type of single OUT params functions only return one row/value anyway. There would be zero perceivable difference in performance regardless of the extra overhead for a single value/row. Sounds like we need a test case... up for it? If there is a performance difference my vote is that we bite the bullet for 8.1 and accept the performance hit rather than settle for sub-optimal behavior. Much easier to fix the performance penalty down the road than to fix the behavior. Yes, it seems this is the concensus. I have added it to the open items list: have a single OUT parameter return the parameter name, not function name --- PostgreSQL 8.1 Open Items = Current version at http://candle.pha.pa.us/cgi-bin/pgopenitems or from http://www.postgresql.org/developer/beta. Bugs fix pg_dump --clean for roles fix foreign trigger timing issue fix ALTER SCHEMA RENAME for sequence name binding, or remove improve spinlock performance fix semantic issues of granted permissions in roles fix pgxs for spaces in file names have a single OUT parameter return the parameter name, not function name Questions - cosider O_SYNC as default when O_DIRECT exists /contrib move to pgfoundry pgindent? make sure bitmap scan optimizer settings are reasonable Documentation - -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Found small issue with OUT params
On 9/30/05, Jim C. Nasby [EMAIL PROTECTED] wrote: On Fri, Sep 30, 2005 at 10:53:22AM -0700, Joshua D. Drake wrote: So you might notice little performance hit bringing back a million rows, and most of these type of single OUT params functions only return one row/value anyway. There would be zero perceivable difference in performance regardless of the extra overhead for a single value/row. Sounds like we need a test case... up for it? If there is a performance difference my vote is that we bite the bullet for 8.1 and accept the performance hit rather than settle for sub-optimal behavior. Much easier to fix the performance penalty down the road than to fix the behavior. What about just returning the single OUT value named by the parameter, instead of special casing single-OUT functions? If I understand correctly, Tom has just added a test to make single-OUT functions look like RETURNS functions. If that were removed then we'd have what, at least by counting the responses on this thread, seems to be the desired (and expected) behaviour. Or I could just be misunderstanding the implementation again. -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Found small issue with OUT params
Tom Lane wrote: Tony Caduto [EMAIL PROTECTED] writes: Please don't take this the wrong way, but don't you think even if a single param is declared as OUT it should return the name of the OUT param? Not really, because create function foo (in x int, out y float) is supposed to have the same external behavior as create function foo (in x int) returns float. I agree it's a bit of a judgment call, but I do not see a case for changing it. regards, tom lane Hi Tom, I understand where you are coming from, but I really think it should be changed because that is how every other DB I know of works with a single OUT param. I was recently porting a fairly large application from Firebird/Interbase and I had a bunch of functions that had one output param, and in the win32 application that I was also moving over, it was expecting the name of the OUT param, not the name of the function, So either I change every single instance of the client code to now use the function name or I add another dummy OUT param so my app does not have to be modified. The biggest reason to change this behavior is for porting from other Databases so client code does not need to be needlessly modifed. The new IN/OUT/INOUT params are sweet, and aside from this one issue, it made porting the Firebird procs super easy. I know I don't have much pull with development, but I think it should be changed for the 8.1 release. Thanks, Tony ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Found small issue with OUT params
On 9/29/05, Tom Lane [EMAIL PROTECTED] wrote: Tony Caduto [EMAIL PROTECTED] writes: Please don't take this the wrong way, but don't you think even if a single param is declared as OUT it should return the name of the OUT param? Not really, because create function foo (in x int, out y float) is supposed to have the same external behavior as create function foo (in x int) returns float. I agree it's a bit of a judgment call, but I do not see a case for changing it. Just my $0.02, but that seems inconsistent. In my mind, the difference between functions with OUT params and functions that return a RECORD (or a specific rowtype) is syntactic sugar. I'm pretty sure that this was used to explain the implementation when it was being discussed, in fact. Using that logic, a functions with one OUT param would be the same as a function returning a rowtype with only one column, and the one column in such a rowtype certainly has a name of it's own. -- Mike Rylander [EMAIL PROTECTED] GPLS -- PINES Development Database Developer http://open-ils.org ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Found small issue with OUT params
Mike Rylander [EMAIL PROTECTED] writes: Using that logic, a functions with one OUT param would be the same as a function returning a rowtype with only one column, But it's not (and no, I don't want to make it so, because the overhead for the useless record result would be significant). regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Found small issue with OUT params
Hi,
consider this function:
CREATE OR REPLACE FUNCTION FIND_USER_SOCKET_BYNAME (
IN IN_USERNAME VARCHAR,
OUT OUT_SOCKET_ADDRESS INTEGER)
AS
$BODY$
BEGIN
select socket_address from userdata where fullname = in_username into
out_socket_address;
if out_socket_address is null then
out_socket_address = 0 ;
end if;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
If I call it like this:
select * from FIND_USER_SOCKET_BYNAME('juser');
I would expect to get back 1 value with the name of the OUT param
(OUT_SOCKET_ADDRESS).
However it comes back with the name of the function which I would expect
if I called it like this:
select FIND_USER_SOCKET_BYNAME('juser');
If I add another OUT value then the value comes back with the name of
the out param(plus the temp one I added) as expected.
It's easy enough to work around, but was not as expected.
Thanks,
Tony Caduto
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Found small issue with OUT params
Tony Caduto [EMAIL PROTECTED] writes:
If I call it like this:
select * from FIND_USER_SOCKET_BYNAME('juser');
I would expect to get back 1 value with the name of the OUT param
(OUT_SOCKET_ADDRESS).
However it comes back with the name of the function
This is intentional, for compatibility with the pre-existing behavior
with functions in FROM. A function that isn't returning a record is
effectively declared as
FROM foo(...) AS foo(foo)
while a function that does return a record type gives you
FROM foo(...) AS foo(col1, col2)
regards, tom lane
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Found small issue with OUT params
Tom Lane wrote:
Tony Caduto [EMAIL PROTECTED] writes:
If I call it like this:
select * from FIND_USER_SOCKET_BYNAME('juser');
I would expect to get back 1 value with the name of the OUT param
(OUT_SOCKET_ADDRESS).
However it comes back with the name of the function
This is intentional, for compatibility with the pre-existing behavior
with functions in FROM. A function that isn't returning a record is
effectively declared as
FROM foo(...) AS foo(foo)
while a function that does return a record type gives you
FROM foo(...) AS foo(col1, col2)
regards, tom lane
Tom,
Please don't take this the wrong way, but don't you think even if a
single param is declared as OUT it should return the name of the OUT param?
If the function has no OUT params and uses the return keyword it should
return the name of the function, if it has one or many out params it
should return even a single column as the name of the OUT param.
It seems inconsistant that just because I have one OUT param declared it
does not return the name I specified for that param.
Isn't it possible to detect that the function has a single OUT param
declared and if a OUT param is declared return that name?
I am bringing this up because people coming over from Oracle or MS SQL
server will notice something like this.
Thanks,
Tony Caduto
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Found small issue with OUT params
Tony Caduto [EMAIL PROTECTED] writes: Please don't take this the wrong way, but don't you think even if a single param is declared as OUT it should return the name of the OUT param? Not really, because create function foo (in x int, out y float) is supposed to have the same external behavior as create function foo (in x int) returns float. I agree it's a bit of a judgment call, but I do not see a case for changing it. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Found small issue with OUT params
Tom Lane wrote: Tony Caduto [EMAIL PROTECTED] writes: Please don't take this the wrong way, but don't you think even if a single param is declared as OUT it should return the name of the OUT param? Not really, because create function foo (in x int, out y float) is supposed to have the same external behavior as create function foo (in x int) returns float. I agree it's a bit of a judgment call, but I do not see a case for changing it. I am agreeing with the poster that use of OUT should always print the out parameter name. Is there a downside to doing that? Seems it gives people an option. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: explain analyze is your friend
