Skip site navigation (1) Skip section navigation (2)

Re: Fwd: Weird issues when reading UDT from stored function

From: Lukas Eder <lukas(dot)eder(at)gmail(dot)com>
To: Radosław Smogura <rsmogura(at)softperience(dot)eu>
Cc: pgsql-jdbc(at)postgresql(dot)org, oliver(at)opencloud(dot)com
Subject: Re: Fwd: Weird issues when reading UDT from stored function
Date: 2011-01-11 22:54:19
Message-ID: AANLkTi=UH_9qC8CmQBN7aEwsdUMYAzjRUCJmDiWFTQQH@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-jdbc
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(at)softperience(dot)eu>

> 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(dot)eder(at)gmail(dot)com> Tuesday 11 January 2011 16:55:52
> > > 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(dot)postgresql(dot)Driver(at)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(AbstractJdbc2S
> > tatement.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(Procedu
> > res.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"
> > ===================================
>

In response to

Responses

pgsql-hackers by date

Next:From: Joel JacobsonDate: 2011-01-11 22:55:07
Subject: Re: pg_depend explained
Previous:From: Alexey KlyukinDate: 2011-01-11 22:25:35
Subject: arrays as pl/perl input arguments [PATCH]

pgsql-jdbc by date

Next:From: rsmoguraDate: 2011-01-12 10:12:13
Subject: Re: Fwd: Weird issues when reading UDT from stored function
Previous:From: Radosław SmoguraDate: 2011-01-11 18:46:33
Subject: Re: Fwd: Weird issues when reading UDT from stored function

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group