Thread: 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
Tony Caduto <tony_caduto@amsoftwaredesign.com> 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 asFROM foo(...) AS foo(foo)
while a function that does return a record type gives youFROM foo(...) AS foo(col1, col2)
regards, tom lane
Tom Lane wrote:
>Tony Caduto <tony_caduto@amsoftwaredesign.com> 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
Tony Caduto <tony_caduto@amsoftwaredesign.com> 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
Tom Lane wrote: > Tony Caduto <tony_caduto@amsoftwaredesign.com> 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, Pennsylvania19073
Tom Lane wrote: >Tony Caduto <tony_caduto@amsoftwaredesign.com> 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
On 9/29/05, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Tony Caduto <tony_caduto@amsoftwaredesign.com> 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 mrylander@gmail.com GPLS -- PINES Development Database Developer http://open-ils.org
Mike Rylander <mrylander@gmail.com> 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
Tom Lane wrote: >Mike Rylander <mrylander@gmail.com> 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
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.
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
> > 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/
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
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 jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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,
Pennsylvania19073
On 9/30/05, Jim C. Nasby <jnasby@pervasive.com> 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 mrylander@gmail.com GPLS -- PINES Development Database Developer http://open-ils.org
-----Original Message----- From: pgsql-hackers-owner@postgresql.org 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