Weird issues when reading UDT from stored function

On 11/01/11 12:06, Lukas Eder wrote:

CREATE TYPE u_street_type AS (
street VARCHAR(100),
no VARCHAR(30)
)

CREATE TYPE u_address_type AS (
street u_street_type,
zip VARCHAR(50),
city VARCHAR(50),
country u_country,
since DATE,
code INTEGER
)

====================================
Output:
# of columns: 6
("(""Parliament Hill"",77)",NW31A9)

Why are there 6 columns? And why is the UDT incorrectly fetched (many
fields are missing)

Looks to me like you're getting each field of the UDT as a separate
column. You printed only the first column i.e. the 'street' part.

It might be informative to run with loglevel=2 and see how the server is
returning results. If the driver is reporting 6 columns, that means that
the server is reporting 6 fields in its RowDescription message.

Oliver

On 11/01/11 13:24, Oliver Jowett wrote:

On 11/01/11 12:06, Lukas Eder wrote:

====================================
Output:
# of columns: 6
("(""Parliament Hill"",77)",NW31A9)

Why are there 6 columns? And why is the UDT incorrectly fetched (many
fields are missing)

Looks to me like you're getting each field of the UDT as a separate
column. You printed only the first column i.e. the 'street' part.

Oops, looking closer I see what you mean, that's actually 2 columns of
the surrounding type - street + zip? What are the values of the other 5
columns reported by the driver?

A loglevel=2 trace would still be useful here.

Oliver

Looks to me like you're getting each field of the UDT as a separate
column. You printed only the first column i.e. the 'street' part.

Exactly, that's what I'm getting

It might be informative to run with loglevel=2 and see how the server is

returning results. If the driver is reporting 6 columns, that means that
the server is reporting 6 fields in its RowDescription message.

Here's what I get (there really is a RowDescription(6)):

===================================
08:15:44.914 (1) PostgreSQL 9.0 JDBC4 (build 801)
08:15:44.923 (1) Trying to establish a protocol version 3 connection to
localhost:5432
08:15:44.941 (1) FE=> StartupPacket(user=postgres, database=postgres,
client_encoding=UNICODE, DateStyle=ISO, extra_float_digits=2)
08:15:44.962 (1) <=BE AuthenticationReqMD5(salt=335c1a87)
08:15:44.968 (1) FE=>
Password(md5digest=md5ea57d63c7d2afaed5abb3f0bb88ae7b8)
08:15:44.970 (1) <=BE AuthenticationOk
08:15:44.980 (1) <=BE ParameterStatus(application_name = )
08:15:44.980 (1) <=BE ParameterStatus(client_encoding = UNICODE)
08:15:44.980 (1) <=BE ParameterStatus(DateStyle = ISO, DMY)
08:15:44.980 (1) <=BE ParameterStatus(integer_datetimes = on)
08:15:44.981 (1) <=BE ParameterStatus(IntervalStyle = postgres)
08:15:44.981 (1) <=BE ParameterStatus(is_superuser = on)
08:15:44.981 (1) <=BE ParameterStatus(server_encoding = UTF8)
08:15:44.981 (1) <=BE ParameterStatus(server_version = 9.0.1)
08:15:44.981 (1) <=BE ParameterStatus(session_authorization = postgres)
08:15:44.981 (1) <=BE ParameterStatus(standard_conforming_strings = off)
08:15:44.981 (1) <=BE ParameterStatus(TimeZone = CET)
08:15:44.981 (1) <=BE BackendKeyData(pid=2980,ckey=465709852)
08:15:44.981 (1) <=BE ReadyForQuery(I)
08:15:44.981 (1) compatible = 9.0
08:15:44.981 (1) loglevel = 2
08:15:44.981 (1) prepare threshold = 5
getConnection returning
driver[className=org.postgresql.Driver,org.postgresql.Driver@77ce3fc5]
08:15:45,021 DEBUG [org.jooq.impl.StoredProcedureImpl
] - Executing query : { call public.p_enhance_address2(?) }
08:15:45.035 (1) simple execute,
handler=org.postgresql.jdbc2.AbstractJdbc2Statement$StatementResultHandler@2eda2cef,
maxRows=0, fetchSize=0, flags=17
08:15:45.036 (1) FE=> Parse(stmt=null,query="select * from
public.p_enhance_address2($1) as result",oids={2278})
08:15:45.037 (1) FE=> Bind(stmt=null,portal=null,$1=<'null'>)
08:15:45.038 (1) FE=> Describe(portal=null)
08:15:45.038 (1) FE=> Execute(portal=null,limit=0)
08:15:45.038 (1) FE=> Sync
08:15:45.043 (1) <=BE ParseComplete [null]
08:15:45.044 (1) <=BE BindComplete [null]
08:15:45.045 (1) <=BE RowDescription(6)
08:15:45.046 (1) <=BE DataRow
08:15:45.046 (1) <=BE CommandStatus(SELECT 1)
08:15:45.062 (1) <=BE ReadyForQuery(I)
org.postgresql.util.PSQLException: Ein CallableStatement wurde mit einer
falschen Anzahl Parameter ausgeführt.
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:408)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:381)
at
org.jooq.impl.StoredProcedureImpl.execute(StoredProcedureImpl.java:125)
at
org.jooq.test.postgres.generatedclasses.Procedures.pEnhanceAddress2(Procedures.java:91)
[...]
SQLException: SQLState(42601)
08:15:45.074 (1) FE=> Terminate
===================================

Oops, looking closer I see what you mean, that's actually 2 columns of the

surrounding type - street + zip?

Yes, exactly. Somehow the driver stops at the second type element of the
surrounding type. This may be correlated to the fact that the inner type has
exactly 2 elements?

What are the values of the other 5 columns reported by the driver?

The other 5 columns are reported as null (always).
In pgAdmin III, I correctly get a single column in the result set. Also, the
postgres information_schema only holds one parameter:

===================================
select parameter_mode, parameter_name, udt_name
from information_schema.parameters
where specific_name like 'p_enhance_address2%'

yields:

"OUT";"address";"u_address_type"
===================================

I've done:
test=# CREATE FUNCTION p_enhance_address3 (address OUT u_address_type, i1 OUT
int)
AS $$
BEGIN
SELECT t_author.address
INTO address
FROM t_author
WHERE first_name = 'George';
i1 = 12;
END;
$$ LANGUAGE plpgsql;
test=# select *
from p_enhance_address3();
address | i1
----------------------------------------------------+----
("(""(""""Parliament Hill"""",77)"",NW31A9)",,,,,) | 12
(1 row)

Result is ok. Because UDT is described in same way as row, it's looks like
that backand do this nasty thing and instead of 1 column, it sends 6 in your
case.

Forward to hackers. Maybe they will say something, because I don;t see this in
docs.

Radek
Lukas Eder <lukas.eder@gmail.com> Tuesday 11 January 2011 16:55:52

Hmm, you're right, the result seems slightly different. But still the UDT
record is not completely fetched as if it were selected directly from
T_AUTHOR in a PreparedStatement...

2011/1/11 Radosław Smogura <rsmogura@softperience.eu>

Dear hackers :) Could you look at this thread from General.
---
I say the backend if you have one "row type" output result treats it as
the full output result, it's really bad if you use STRUCT types (in your
example you see few columns, but this should be one column!). I think
backend should return ROWDESC(1), then per row data describe this row
type data. In other words result should be as in my example but without
last column. Because this funny behaviour is visible in psql in JDBC I
think it's backend problem or some far inconsistency. I don't see this
described in select statement.

Kind regards,
Radek

On Wed, Jan 12, 2011 at 5:12 AM, rsmogura <rsmogura@softperience.eu> wrote:

Dear hackers :) Could you look at this thread from General.
---
I say the backend if you have one "row type" output result treats it as the
full output result, it's really bad if you use STRUCT types (in your example
you see few columns, but this should be one column!). I think backend should
return ROWDESC(1), then per row data describe this row type data. In other
words result should be as in my example but without last column. Because
this funny behaviour is visible in psql in JDBC I think it's backend problem
or some far inconsistency. I don't see this described in select statement.

I've read this report over a few times now, and I'm still not
understanding exactly what is happening that you're unhappy about.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 17/01/11 17:27, Robert Haas wrote:

On Wed, Jan 12, 2011 at 5:12 AM, rsmogura<rsmogura@softperience.eu> wrote:

Dear hackers :) Could you look at this thread from General.
---
I say the backend if you have one "row type" output result treats it as the
full output result, it's really bad if you use STRUCT types (in your example
you see few columns, but this should be one column!). I think backend should
return ROWDESC(1), then per row data describe this row type data. In other
words result should be as in my example but without last column. Because
this funny behaviour is visible in psql in JDBC I think it's backend problem
or some far inconsistency. I don't see this described in select statement.

I've read this report over a few times now, and I'm still not
understanding exactly what is happening that you're unhappy about.

If I understand it correctly, the problem is this:

Given the schema and data from the OP

(summary:
t_author is a TABLE
t_author.address is of type u_address_type
u_address_type is a TYPE with fields: street, zip, city, country, since,
code
u_address_type.street is of type u_street_type
u_street_type is a TYPE with fields: street, no)

A bare SELECT works as expected:

test_udt=# SELECT t_author.address FROM t_author WHERE first_name = 'George';
address
-------------------------------------------------------------------
("(""Parliament Hill"",77)",NW31A9,Hampstead,England,1980-01-01,)
(1 row)

However, doing the same via a plpgsql function with an OUT parameter
produces something completely mangled:

test_udt=# CREATE FUNCTION p_enhance_address2 (address OUT u_address_type) AS $$ BEGIN SELECT t_author.address INTO address FROM t_author WHERE first_name = 'George'; END; $$ LANGUAGE plpgsql;
CREATE FUNCTION

test_udt=# SELECT * FROM p_enhance_address2();
street | zip | city | country | since | code
-------------------------------------+-----+------+---------+-------+------
("(""Parliament Hill"",77)",NW31A9) | | | | |
(1 row)

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.

Unsurprisingly the JDBC driver produces confusing results when faced
with this, so it was originally reported as a JDBC problem, but the
underlying problem can be seen via psql too.

Oliver

On Mon, Jan 17, 2011 at 12:00 AM, Oliver Jowett <oliver@opencloud.com> wrote:

However, doing the same via a plpgsql function with an OUT parameter
produces something completely mangled:

test_udt=# CREATE FUNCTION p_enhance_address2 (address OUT u_address_type)
AS $$ BEGIN SELECT t_author.address INTO address FROM t_author WHERE
first_name = 'George'; END; $$ LANGUAGE plpgsql;
CREATE FUNCTION

test_udt=# SELECT * FROM p_enhance_address2();
              street                | zip | city | country | since | code

-------------------------------------+-----+------+---------+-------+------
 ("(""Parliament Hill"",77)",NW31A9) |     |      |         |       |
(1 row)

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. :-(

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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...

Hi,
I don't know if this is a bug, but at least I haven't found any clear
statement in documentation about; this should be wrote with big and bold
letters.

In any way I think this is bug or big inconsistency, because of, as was
stated in previous mail
test=# CREATE FUNCTION p_enhance_address3 (address OUT u_address_type,
i1 OUT
int)
AS $$
BEGIN
SELECT t_author.address
INTO address
FROM t_author
WHERE first_name = 'George';
i1 = 12;
END;
$$ LANGUAGE plpgsql;
test=# select *
from p_enhance_address3();
address | i1
----------------------------------------------------+----
("(""(""""Parliament Hill"""",77)"",NW31A9)",,,,,) | 12
(1 row),
but if you will create above function without last, i1 parameter
(SELECT * FROM p_enhance_address2();) then result will be
street | zip | city | country | since |
code
-------------------------------------+-----+------+---------+-------+------
("(""Parliament Hill"",77)",NW31A9) | | | | |
In last case, I think, result should be "packed" in one column, because
of it clearly "unpacked" record.

On Tue, Jan 25, 2011 at 2:39 AM, Lukas Eder <lukas.eder@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

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 <robertmhaas@gmail.com>

On Sat, Feb 12, 2011 at 6:16 AM, Lukas Eder <lukas.eder@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

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 <robertmhaas@gmail.com>

On Wed, Feb 16, 2011 at 3:30 AM, Lukas Eder <lukas.eder@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...

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 17/02/11 00:58, Robert Haas wrote:

On Wed, Feb 16, 2011 at 3:30 AM, Lukas Eder <lukas.eder@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

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 <oliver@opencloud.com>

On Wed, Feb 16, 2011 at 7:03 AM, Oliver Jowett <oliver@opencloud.com> wrote:

On 17/02/11 00:58, Robert Haas wrote:

On Wed, Feb 16, 2011 at 3:30 AM, Lukas Eder <lukas.eder@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