Thread: Namespace of array of user defined types is confused by the parser in insert?

Hello,
Sorry for re-posting - I initially posted this in pgsql.sql - probably
this group is more appropriate.

I have a bizzare problem that started to manifest itself after
addition of field being the array of compound UDTs to the table
declared in multiple schemas.
It is clearly related to how the type namespace is resolved and shows
up for the JDBC client (probably related to the paramterized query, as
the static query works without problems).

given the types:

drop type if exists periodSearchResults cascade;
create type periodSearchResults as
(period float8[], -- [1] - 1st value, kth... value for the same tsperiodError float8[],probability float8[],amplitude
float8[]);

drop type if exists periodTSResult cascade;
create type periodTSResult as
(      method text,      periods periodSearchResults[] -- 1st raw ts, 2...kth - residual
values
)
;

and a table defined as:

CREATE TABLE timeseriesresult (  id bigint NOT NULL,
--- ...  fperiodsearchresults periodTSResult[]--- ....
);

when the type periodTSResult is defined in more than one schema, I got
error for insert using JDBC client (or prepared statement - it works
with a non-parametrized query) in any other then a first schema it was
defined in - in this case the first schema it was defined in is
cu7user_test, the schema of the execution is cu7user_test_2:
<log>
ERROR:  column "fperiodsearchresults" is of type periodtsresult[] but
expression is of type cu7user_test.periodtsresult[] at character 1416
HINT:  You will need to rewrite or cast the expression.
STATEMENT:  INSERT INTO cu7user_test_2.timeseriesresult (runid,
catalogid, sourceid, ftimeseriestype, fstate,
fminimizedfunctionminval, freducedchi2, fstopcriteria,
fweightedmodeling, fhomoscedasticitytest, fkurtosis,
fnumpointsobstime, fljungboxrandomnesstest, fmedianabsolutedeviation,
fmax, fmeanobstime, fmean, fmeanerror, fmedian, fmedianerror, fmin,
frange, frobustweightedstddev, fskewness, fstddev, fsymmetrytest,
ftrimmedweightedmean, ftrimmedweightedrange, fvariabilityflag,
fstatvariabilityflag, fweightedkurtosis, fweightedmean,
fweightedmeanconfidenceinterval, fweightedmeanobstime,
fweightednormalizedp2pscatter, fweightedskewness, fweightedstddevdf,
fweightedstddevwdf, fabbe, fchi2, fiqr, foutliermedian, fpstetson,
fpabbe, fpchi2, fpiqr, fpkurtosis, fpoutliermedian, fpskew,
fpweightedkurtosis, fpweightedskew, fstetson, referencetime,
cadencevalue, cadenceerror, cadencename, fperiodsearchmethod,
fweightedpercentileranks, fweightedpercentiles, fotherparameters,
ffundamentalfrequencies, mapfperiodsearchresults,
fperiodsearchresults, fpolynomialcoefficients, ffouriercoefficients,
derivedtschanges) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10,
$11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24,
$25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38,
$39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52,
$53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66)
DEBUG:  parse S_10: ROLLBACK
</log>

I debugged a little to find out that indeed there are two types
defined in separate schemas:

SELECT t.oid, t.typname,(select nspname from pg_namespace n where
n.oid=typnamespace) nspname ,typarray  FROM pg_catalog.pg_type t where
typname like 'periodts%';

gives:
oid  |    typname     |    nspname     | typarray
-------+----------------+----------------+----------16646 | periodtsresult | cu7user_test   |    1664517123 |
periodtsresult| cu7user_test_2 |    17122 

but for user/schema cu7user_test_2
parse_coerce.c:86:

if (!can_coerce_type(1, &exprtype, &targettype, ccontext))              return NULL;
the function returns false and subsequently coerce_to_target_type
returns null resulting in the above error.
This is caused by the
parse_coerce: 421 expr pointer returning type_id for the type defined
in the '1st' schema:

type_id = exprType((Node *) expr); //<-- type_id returned is 16645,
should be 17122

expr pointer value is already wrong in the parsed list in the loop
starting at     analyze:799

foreach(lc, exprlist) <- wrong type_id already for the array UDT
column


Is there any way of avoid this error different than having a single
type defined for all schemas?
Any hints appreciated..
Postgres 9.1.3, latest JDBC driver.

Best regards,
Krzysztof

On Mon, Apr 23, 2012 at 9:42 AM, Krzysztof Nienartowicz
<krzysztof.nienartowicz@gmail.com> wrote:
> Hello,
> Sorry for re-posting - I initially posted this in pgsql.sql - probably
> this group is more appropriate.
>
> I have a bizzare problem that started to manifest itself after
> addition of field being the array of compound UDTs to the table
> declared in multiple schemas.
> It is clearly related to how the type namespace is resolved and shows
> up for the JDBC client (probably related to the paramterized query, as
> the static query works without problems).

I'm replying to this awfully late, but I'm guessing this is some kind
of JDBC magic, not anything that PostgreSQL is causing directly.  You
might want to post to pgsql-jdbc, if you haven't already.

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