Thread: BUG #13539: ERROR: record type has not been registered with CTE and subselect in target list

The following bug has been logged on the website:

Bug reference:      13539
Logged by:          Maciek Sakrejda
Email address:      maciek@heroku.com
PostgreSQL version: 9.4.4
Operating system:   Ubuntu 15.04
Description:

I'm not sure if this is a legitimate bug, but the error message is unclear.
I've searched for this message and have seen reports of similar errors, but
the SQL is somewhat different, so I thought I'd report it:

maciek=# with foo(a) as (select 1) select (select (foo) from foo).* from
foo;
ERROR:  record type has not been registered

I know how to work around it; just reporting it in the hope of maybe getting
a clearer error.

Thanks!

On Wed, Aug 5, 2015 at 5:57 PM, <maciek@heroku.com> wrote:

> The following bug has been logged on the website:
>
> Bug reference:      13539
> Logged by:          Maciek Sakrejda
> Email address:      maciek@heroku.com
> PostgreSQL version: 9.4.4
> Operating system:   Ubuntu 15.04
> Description:
>
> I'm not sure if this is a legitimate bug, but the error message is unclea=
r.
> I've searched for this message and have seen reports of similar errors, b=
ut
> the SQL is somewhat different, so I thought I'd report it:
>
> maciek=3D# with foo(a) as (select 1) select (select (foo) from foo).* fro=
m
> foo;
> ERROR:  record type has not been registered
>
> I know how to work around it; just reporting it in the hope of maybe
> getting
> a clearer error.
>

=E2=80=8BSuggestions are welcomed though this is an existing limitation of
PostgreSQL.  The planner does not capture enough information to deal with
constructs of the form (ROW(...)).* =E2=80=8B
regardless of how they were formed.  The above is the equivalent to:
SELECT (foo.foo_val).* FROM ( VALUES (ROW(1)) ) foo (foo_val) which is
likewise unresolvable.

=E2=80=8BSomething like: "wildcard expansion of uncataloged =E2=80=8Brecord=
 type not
possible" ?

To my recollection the concept of "type registration" is not used in the
user documentation.  Uncataloged at least implies that using CREATE TYPE
(or table/view implicitly) to add a named type to the system type catalog
would resolve the problem.  Though an implied "create function returns
table" type does get "registered" in the planner so maybe that is too
specific...

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Wed, Aug 5, 2015 at 5:57 PM, <maciek@heroku.com> wrote:
>> maciek=# with foo(a) as (select 1) select (select (foo) from foo).* from
>> foo;
>> ERROR:  record type has not been registered

> ​Suggestions are welcomed though this is an existing limitation of
> PostgreSQL.  The planner does not capture enough information to deal with
> constructs of the form (ROW(...)).* ​
> regardless of how they were formed.  The above is the equivalent to:
> SELECT (foo.foo_val).* FROM ( VALUES (ROW(1)) ) foo (foo_val) which is
> likewise unresolvable.

> ​Something like: "wildcard expansion of uncataloged ​record type not
> possible" ?

It's a bug IMO.  The reason the error message is not very transparent
is that it's not meant to ever be seen by users.  Hence, improving the
wording would just be putting lipstick on the wrong end of a pig.

            regards, tom lane

(But having said that, I've not looked into how hard it'd be to fix this
particular case...)