Re: [HACKERS] 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 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

[redirected to pgsql-general]

On 04/23/2012 09:42 AM, Krzysztof Nienartowicz wrote:
> Hello,
> Sorry for re-posting - I initially posted this in pgsql.sql - probably
> this group is more appropriate.

pgsql-general probably would be best. -hackers is for discussion of
internals and development, not for usage questions.

[types have namespaces]

> Is there any way of avoid this error different than having a single
> type defined for all schemas?
> Any hints appreciated..

Probably your best bet is to put the types explicitly in the public
namespace when they are created, instead of relying on the search path
that happens to be in force at the time:

create type public.foo as ( ...);

Then, assuming that public is in your search path they will be picked up
properly when used. Alternatively, you can namespace qualify them when used:

create type public.bar as (f1 public.foo[], ...);

cheers

andrew

> pgsql-general probably would be best. -hackers is for discussion of internals and development, not for usage questions.

ok, thank you.

>
> [types have namespaces]
>
>
>> Is there any way of avoid this error different than having a single
>> type defined for all schemas?
>> Any hints appreciated..
>
>
>
> Probably your best bet is to put the types explicitly in the public
> namespace when they are created, instead of relying on the search path that
> happens to be in force at the time:
>
>   create type public.foo as ( ...);

Yes, I did it in the end, but such a solution makes things risky if
the types evolve and the namespaces might hold different schema
versions.
This error occurs when there is no search_path overlap, i.e. roles
have only their own namespace search_path, even without public path
set.

>
>
> Then, assuming that public is in your search path they will be picked up
> properly when used. Alternatively, you can namespace qualify them when used:
>
>   create type public.bar as (f1 public.foo[], ...);

These types are qualified when created - the error does not happen on
creation - there are two types in two different namespaces - it
happens only on insert where it is not possible to qualify the type's
namespace.
It looks like a bug in the planner to me.

Thanks,
Krzysztof

>
>
>
> cheers
>
> andrew

On 04/24/2012 05:12 AM, Krzysztof Nienartowicz wrote:
>
> These types are qualified when created - the error does not happen on
> creation - there are two types in two different namespaces - it
> happens only on insert where it is not possible to qualify the type's
> namespace.
> It looks like a bug in the planner to me.
>

If it is please present a self-contained case demonstrating the bug,
preferably using psql rather than JDBC. Was all this done with the same
user / search path throughout the session (no connection pooling, for
example)? A complete log of the session (with log_statements,
log_connections and log_disconnections all turned on) might also help.

cheers

andrew

On Mon, Apr 23, 2012 at 9:42 AM, Krzysztof Nienartowicz
<krzysztof(dot)nienartowicz(at)gmail(dot)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