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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
