Re: [HACKERS] Fwd: [JDBC] Weird issues when reading UDT from stored function
2011/2/17 Florian Pflug f...@phlo.org
On Feb17, 2011, at 01:14 , Oliver Jowett wrote:
Any suggestions about how the JDBC driver can express the query to get
the behavior that it wants? Specifically, the driver wants to call a
particular function with N OUT or INOUT parameters (and maybe some other
IN parameters too) and get a resultset with N columns back.
There's no sane way to do that, I fear. You could of course look up the
function definition in the catalog before actually calling it, but with
overloading and polymorphic types finding the right pg_proc entry seems
awfully complex.
Your best option is probably to just document this caveat...
But there still is a bug in the JDBC driver as I originally documented it.
Even if you say it's not simple to know whether the signature is actually a
single UDT with 6 attributes or just 6 OUT parameters, the result is wrong
(as stated in my original mail):
The nested UDT structure completely screws up fetching results. This
is what I get with JDBC:
PreparedStatement stmt = connection.prepareStatement(select *
from p_enhance_address2());
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
System.out.println(# of columns: +
rs.getMetaData().getColumnCount());
System.out.println(rs.getObject(1));
}
Output:
# of columns: 6
((Parliament Hill,77),NW31A9)
The result set meta data correctly state that there are 6 OUT columns. But
only the first 2 are actually fetched (because of a nested UDT)...
Re: [HACKERS] Fwd: [JDBC] Weird issues when reading UDT from stored function
Hi Oliver There are two problems. The first problem is a plpgsql problem in that particular function. It's broken regardless of how you call it. Here's how to fix it [...] Thanks for insisting! I missed that fact. In the end, it looked like the same error, but you're right about the plpgsql syntax error. The second problem is that the JDBC driver always generates calls in the SELECT * FROM ... form, but this does not work correctly for one-OUT-parameter-that-is-a-UDT, as seen in the example immediately above. Here's how to do the call for that particular case [...] Knowing these things, I think I can live with the status quo in my case. As I'm writing a database abstraction library (http://jooq.sourceforge.net), with generated source code, I can hide these Postgres-specific details from end-user code easily and assemble the UDT myself when reading the 6 return values. Any questions? (I'm sure there will be questions. Sigh.) Thanks again for the patience! :-)
Re: [HACKERS] Fwd: [JDBC] Weird issues when reading UDT from stored function
I'm not trying to fix the signature. I want exactly that signature. I want to return 1 UDT as an OUT parameter from a function. Somewhere between JDBC and the database, this signature is lost, and JDBC's internal code tells me that I have to bind 6 OUT parameters, instead of 1. It happens to be so, because the UDT contains 6 attributes, so somehow the JDBC/database protocol flattens the UDT, and I think that's a bug, either in JDBC or in the protocol or in the database. My findings were that I can correctly read the UDT OUT parameter using the pgAdmin III tool, so I excluded the database as a bug holder candidate. Cheers Lukas 2011/2/15 Robert Haas robertmh...@gmail.com On Sat, Feb 12, 2011 at 6:16 AM, Lukas Eder lukas.e...@gmail.com wrote: I had tried that before. That doesn't seem to change anything. JDBC still expects 6 OUT parameters, instead of just 1... Oh, hrm. I thought you were trying to fix the return value, rather than the signature. I am not sure how to fix the signature. Can you just make it return RECORD? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] Fwd: [JDBC] Weird issues when reading UDT from stored function
That was my opinion, but you're saying that JDBC is not the cause either? 2011/2/16 Robert Haas robertmh...@gmail.com On Wed, Feb 16, 2011 at 7:07 AM, Lukas Eder lukas.e...@gmail.com wrote: So what should I do? File a bug to the main Postgres mailing list? Or just not support that feature? Well, I thought you just said you'd ruled out a PG bug? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] Fwd: [JDBC] Weird issues when reading UDT from stored function
Hmm, good point. I should try that. I have only tried these syntaxes:
connection.prepareStatement(select * from p_enhance_address2());
connection.prepareCall({ call p_enhance_address2(?) }); // with an
output parameter registered
Since I'm doing this for my database abstraction tool
http://jooq.sourceforge.net, I could add a specialised Postgres stored
procedures abstraction and hide these details from the outside world...
Thanks for the hint!
2011/2/16 Robert Haas robertmh...@gmail.com
On Wed, Feb 16, 2011 at 7:03 AM, Oliver Jowett oli...@opencloud.com
wrote:
On 17/02/11 00:58, Robert Haas wrote:
On Wed, Feb 16, 2011 at 3:30 AM, Lukas Eder lukas.e...@gmail.com
wrote:
I'm not trying to fix the signature. I want exactly that signature. I
want
to return 1 UDT as an OUT parameter from a function.
Somewhere between JDBC and the database, this signature is lost, and
JDBC's
internal code tells me that I have to bind 6 OUT parameters, instead of
1.
It happens to be so, because the UDT contains 6 attributes, so somehow
the
JDBC/database protocol flattens the UDT, and I think that's a bug,
either in
JDBC or in the protocol or in the database. My findings were that I can
correctly read the UDT OUT parameter using the pgAdmin III tool, so I
excluded the database as a bug holder candidate.
Oh, OK. Sorry, I can't help you any with the JDBC side...
Well, the underlying problem is that SELECT * from
function_with_one_out_parameter() is returning *6* columns, not 1
column. I don't know if that's expected or not on the plpgsql side, but
the JDBC driver has no way of distinguishing that sort of result from a
function that has 6 OUT parameters.
If you do SELECT function_with_one_out_parameter() rather than SELECT
* FROM function_with_one_out_parameter(), you'll get just one
argument. Does that help at all?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Re: [HACKERS] Fwd: [JDBC] Weird issues when reading UDT from stored function
So what should I do? File a bug to the main Postgres mailing list? Or just not support that feature? 2011/2/16 Oliver Jowett oli...@opencloud.com On 17/02/11 00:58, Robert Haas wrote: On Wed, Feb 16, 2011 at 3:30 AM, Lukas Eder lukas.e...@gmail.com wrote: I'm not trying to fix the signature. I want exactly that signature. I want to return 1 UDT as an OUT parameter from a function. Somewhere between JDBC and the database, this signature is lost, and JDBC's internal code tells me that I have to bind 6 OUT parameters, instead of 1. It happens to be so, because the UDT contains 6 attributes, so somehow the JDBC/database protocol flattens the UDT, and I think that's a bug, either in JDBC or in the protocol or in the database. My findings were that I can correctly read the UDT OUT parameter using the pgAdmin III tool, so I excluded the database as a bug holder candidate. Oh, OK. Sorry, I can't help you any with the JDBC side... Well, the underlying problem is that SELECT * from function_with_one_out_parameter() is returning *6* columns, not 1 column. I don't know if that's expected or not on the plpgsql side, but the JDBC driver has no way of distinguishing that sort of result from a function that has 6 OUT parameters. Oliver
Re: [HACKERS] Fwd: [JDBC] Weird issues when reading UDT from stored function
I had tried that before. That doesn't seem to change anything. JDBC still expects 6 OUT parameters, instead of just 1... 2011/2/11 Robert Haas robertmh...@gmail.com On Tue, Jan 25, 2011 at 2:39 AM, Lukas Eder lukas.e...@gmail.com wrote: So what you're suggesting is that the plpgsql code is causing the issues? Are there any indications about how I could re-write this code? The important thing for me is to have the aforementioned signature of the plpgsql function with one UDT OUT parameter. Even if this is a bit awkward in general, in this case, I don't mind rewriting the plpgsql function content to create a workaround for this problem... Possibly something like address := (SELECT ...) rather than SELECT ... INTO address? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] Fwd: [JDBC] Weird issues when reading UDT from stored function
Here, we've somehow got the first two fields of u_address_type - street and zip - squashed together into one column named 'street', and all the other columns nulled out. I think this is the old problem of PL/pgsql having two forms of SELECT INTO. You can either say: SELECT col1, col2, col3, ... INTO recordvar FROM ... Or you can say: SELECT col1, col2, col3, ... INTO nonrecordvar1, nonrecordvar2, nonrecordvar3, ... FROM ... In this case, since address is a recordvar, it's expecting the first form - thus the first select-list item gets matched to the first column of the address, rather than to address as a whole. It's not smart enough to consider the types of the items involved - only whether they are records. :-( So what you're suggesting is that the plpgsql code is causing the issues? Are there any indications about how I could re-write this code? The important thing for me is to have the aforementioned signature of the plpgsql function with one UDT OUT parameter. Even if this is a bit awkward in general, in this case, I don't mind rewriting the plpgsql function content to create a workaround for this problem...
