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

From: Krzysztof Nienartowicz <krzysztof(dot)nienartowicz(at)gmail(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Namespace of array of user defined types is confused by the parser in insert?
Date: 2012-04-23 13:42:40
Message-ID: eadee2d7-66b2-41ce-8035-cd88ece194d7@2g2000yqk.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-hackers

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 ts
periodError 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 | 16645
17123 | 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Andrew Dunstan 2012-04-23 16:09:43 Re: [HACKERS] Namespace of array of user defined types is confused by the parser in insert?
Previous Message Vincenzo Romano 2012-04-23 12:50:06 Re: How to convert BYTEA (from decrypt) to TEXT?

Browse pgsql-hackers by date

  From Date Subject
Next Message Boszormenyi Zoltan 2012-04-23 14:03:39 Re: ECPG FETCH readahead
Previous Message Sandro Santilli 2012-04-23 13:37:42 Re: Gsoc2012 idea, tablesample