[HACKERS] IN/OUT parameters

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

[HACKERS] IN/OUT parameters

Dave Cramer-8
I've been working on adding the functionality into the jdbc driver and
I'm having some issues.

Currently the bind message does not know anything about directionality
of the parameter. This means that considerable gyrations need to be done
by the driver to transform

create function foo( out p1 int, in p2 int,  out p3  int) ....
then a subsequent
 
"{call = select foo(?,?,?)}

This would need to be currently transformed into select foo(?), with the
other two being discarded.

It seems to me that the bind message needs to support the notion of
direction in order for this to work cleanly.

Alternatively we could punt and use SQL Server's mechanism where they
only support IN, and INOUT, which would require all parameters to be
sent to the procedure.

On a related note, drop function needs to support the in/out direction.

Dave

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] IN/OUT parameters

Tom Lane-2
Dave Cramer <[hidden email]> writes:
> On a related note, drop function needs to support the in/out direction.

It does ... do you see a problem?

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] IN/OUT parameters

Josh berkus
In reply to this post by Dave Cramer-8
Hmmmm ....

> It seems to me that the bind message needs to support the notion of
> direction in order for this to work cleanly.
>
> Alternatively we could punt and use SQL Server's mechanism where they
> only support IN, and INOUT, which would require all parameters to be
> sent to the procedure.

Does SQL2003 cover IN/OUT parameters?  I don't see anything in SQL99.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] IN/OUT parameters

Tom Lane-2
In reply to this post by Dave Cramer-8
Dave Cramer <[hidden email]> writes:
> create function foo( out p1 int, in p2 int,  out p3  int) ....
> then a subsequent
 
> "{call = select foo(?,?,?)}

> This would need to be currently transformed into select foo(?), with the
> other two being discarded.

> It seems to me that the bind message needs to support the notion of
> direction in order for this to work cleanly.

How would it help for BIND to incorporate direction?  What would it even
*mean* for BIND to incorporate direction --- it's a client-to-server
message, and can hardly be expected to transmit data in the reverse
direction.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] IN/OUT parameters

Dave Cramer-8
In reply to this post by Tom Lane-2
Ok, then I guess the documentation needs to indicate that. Or the version I am reading is not up to date.

Dave

Tom Lane wrote:
Dave Cramer [hidden email] writes:
  
On a related note, drop function needs to support the in/out direction.
    

It does ... do you see a problem?

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])


  

-- 
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] IN/OUT parameters

Dave Cramer-8
In reply to this post by Tom Lane-2
At this point I'd just like the backend to ignore the OUT parameter if
it were set that way, but allow
it to be sent.

If you consider that a function could have for arguments sake 10
parameters, and they
can be ordered in any fashion; clients have to essentially parse out the
OUT parameters and not send them.  So you have an incoming client call
with 10 parameters but some number less than or equal to get sent to the
backend.

At this point I'm leaning towards just using IN, or INOUT params, as
jdbc has a register outparam, and this could be bound to null if were
not explicitly set.

Dave



Tom Lane wrote:

>Dave Cramer <[hidden email]> writes:
>  
>
>>create function foo( out p1 int, in p2 int,  out p3  int) ....
>>then a subsequent
>>    
>>
>
>  
>
>>"{call = select foo(?,?,?)}
>>    
>>
>
>  
>
>>This would need to be currently transformed into select foo(?), with the
>>other two being discarded.
>>    
>>
>
>  
>
>>It seems to me that the bind message needs to support the notion of
>>direction in order for this to work cleanly.
>>    
>>
>
>How would it help for BIND to incorporate direction?  What would it even
>*mean* for BIND to incorporate direction --- it's a client-to-server
>message, and can hardly be expected to transmit data in the reverse
>direction.
>
> regards, tom lane
>
>
>  
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] IN/OUT parameters

Josh berkus
In reply to this post by Tom Lane-2
Tom,

> How would it help for BIND to incorporate direction?  What would it even
> *mean* for BIND to incorporate direction --- it's a client-to-server
> message, and can hardly be expected to transmit data in the reverse
> direction.

Where directionality comes in is with OUT-only parameters.   Which, IMHO,
aren't terribly important unless SQL2003 demands them; MSSQL didn't even
bother to implement them.  Anyone know what SQL3 says?

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] IN/OUT parameters

Tom Lane-2
In reply to this post by Dave Cramer-8
Dave Cramer <[hidden email]> writes:
> At this point I'd just like the backend to ignore the OUT parameter if
> it were set that way, but allow it to be sent.

I think you're thinking at the wrong level.  AIUI the issue occurs at
the Parse stage, long before any parameter value is sent (or not sent).

What you really want, if I'm understanding correctly, is to be able to
send SQL that looks like this:

        SELECT * FROM myfunc($1,$2,$3);

and then have some of the Param symbols be simply ignored while looking
up myfunc().

This is pretty ugly in itself, and I'd not want to add a protocol change
into the mix to make it happen.  But possibly we could do it without any
protocol-level change.  What would you say to specifying that Params
that are declared as type VOID are ignored in a function lookup?  What
this would mean is that you'd need to do the following:

1. The SQL string can look as above.

2. When sending the Parse message, you'd specify the parameter types
   as, say, INT4/VOID/VOID (or possibly UNKNOWN/VOID/VOID).

3. When sending Bind, you'd have to specify dummy values (probably
   nulls) for the VOID parameter positions.

While I haven't looked at the backend code yet, I think we could drop
VOID-type parameters out of the argument list of a function during
parse analysis without too much trouble.  This would affect *all*
function calls, not only those appearing in SELECT * FROM, but
I don't see any legitimate use of VOID-type values that this would
interfere with.

Klugy, isn't it?  Anyone have a better idea?  Or should we just tell
Dave that the JDBC driver ought to handle it?

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] IN/OUT parameters

Tom Lane-2
In reply to this post by Dave Cramer-8
Dave Cramer <[hidden email]> writes:
> Ok, then I guess the documentation needs to indicate that. Or the
> version I am reading is not up to date.

Oh, you're right, I overlooked adding that to the documentation of
the other statement types that work with function names.

Come to think of it, the regprocedure type may need some work too.
It'd probably be nice if it understood IN/OUT decorations, at
least for input.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])
Reply | Threaded
Open this post in threaded view
|

Re: [HACKERS] IN/OUT parameters

Josh berkus
People:

OK, found it in SQL99:

<SQL parameter declaration> ::=
[ <parameter mode> ] [ <SQL parameter name> ] <parameter type> [ RESULT ]
<parameter mode> ::=
  IN
  | OUT
  | INOUT

... so this is something we need to support, apparently both for Functions and
Procedures (when we get the latter), in the backend, not just JDBC.  As you
can imagine, though, SQL03 does nothing to clarify calling rules for IN/OUT
params.

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: [JDBC] [HACKERS] IN/OUT parameters

Dave Cramer-8
In reply to this post by Tom Lane-2
I think this is the driver's problem. It appears that we are following
the spec, so lets leave this alone.

Regarding what an OUT parameter might mean. We already have some
facility in the FE/BE to indicate the types of the returning columns.
Directionality is implied by where in the bind message these parameters
are sent. I can see the extra overhead of putting directionality in for
each column.
I'd agree that changing the FE/BE at this point is not a good idea.
However, if we ever do get real stored procs, this may be an issue.

I've looked at the driver some more and it appears we parse the sql more
than once due to the fact that we are supporting v2, and v3 backend
protocols. At this point I've got to consider the TODO on the jdbc list
of putting a proper parser in. Are there any javacc wizards around ?

Dave
Tom Lane wrote:

>Dave Cramer <[hidden email]> writes:
>  
>
>>At this point I'd just like the backend to ignore the OUT parameter if
>>it were set that way, but allow it to be sent.
>>    
>>
>
>I think you're thinking at the wrong level.  AIUI the issue occurs at
>the Parse stage, long before any parameter value is sent (or not sent).
>
>What you really want, if I'm understanding correctly, is to be able to
>send SQL that looks like this:
>
> SELECT * FROM myfunc($1,$2,$3);
>
>and then have some of the Param symbols be simply ignored while looking
>up myfunc().
>
>This is pretty ugly in itself, and I'd not want to add a protocol change
>into the mix to make it happen.  But possibly we could do it without any
>protocol-level change.  What would you say to specifying that Params
>that are declared as type VOID are ignored in a function lookup?  What
>this would mean is that you'd need to do the following:
>
>1. The SQL string can look as above.
>
>2. When sending the Parse message, you'd specify the parameter types
>   as, say, INT4/VOID/VOID (or possibly UNKNOWN/VOID/VOID).
>
>3. When sending Bind, you'd have to specify dummy values (probably
>   nulls) for the VOID parameter positions.
>
>While I haven't looked at the backend code yet, I think we could drop
>VOID-type parameters out of the argument list of a function during
>parse analysis without too much trouble.  This would affect *all*
>function calls, not only those appearing in SELECT * FROM, but
>I don't see any legitimate use of VOID-type values that this would
>interfere with.
>
>Klugy, isn't it?  Anyone have a better idea?  Or should we just tell
>Dave that the JDBC driver ought to handle it?
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>
>  
>

--
Dave Cramer
http://www.postgresintl.com
519 939 0336
ICQ#14675561


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])