Thread: minor view creation weirdness

minor view creation weirdness

From
Neil Conway
Date:
Is this a bug?

(using CVS code from yesterday)

nconway=# create view baz (a,b) as select 'hello', 'world';
WARNING:  column "a" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
WARNING:  column "b" has type "unknown"
DETAIL:  Proceeding with relation creation anyway.
CREATE VIEW
nconway=# 

-Neil




Re: minor view creation weirdness

From
Greg Stark
Date:
Neil Conway <neilc@samurai.com> writes:

> Is this a bug?
> 
> (using CVS code from yesterday)
> 
> nconway=# create view baz (a,b) as select 'hello', 'world';
> WARNING:  column "a" has type "unknown"
> DETAIL:  Proceeding with relation creation anyway.
> WARNING:  column "b" has type "unknown"
> DETAIL:  Proceeding with relation creation anyway.
> CREATE VIEW
> nconway=# 

7.3 does the same thing actually. I don't know what that means though.


-- 
greg



Re: minor view creation weirdness

From
Oliver Elphick
Date:
On Thu, 2003-10-02 at 08:40, Greg Stark wrote:
> Neil Conway <neilc@samurai.com> writes:
> 
> > Is this a bug?
> > 
> > (using CVS code from yesterday)
> > 
> > nconway=# create view baz (a,b) as select 'hello', 'world';
> > WARNING:  column "a" has type "unknown"
> > DETAIL:  Proceeding with relation creation anyway.
> > WARNING:  column "b" has type "unknown"
> > DETAIL:  Proceeding with relation creation anyway.
> > CREATE VIEW
> > nconway=# 
> 
> 7.3 does the same thing actually. I don't know what that means though.

junk=# \d baz      View "public.baz"Column |   Type    | Modifiers
--------+-----------+-----------a      | "unknown" |b      | "unknown" |
View definition:SELECT 'hello' AS a, 'world' AS b;

There is no table behind the view, so there is no way for PostgreSQL to
derive the column types of a and b.  A quoted string (as supplied in the
view definition) could be one of text, varchar, char, date, time,
timestamp, cidr and so on.


-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
========================================   "Set your affection on things above, not on things on      the earth."
  Colossians 3:2 
 



Re: minor view creation weirdness

From
Tom Lane
Date:
Neil Conway <neilc@samurai.com> writes:
> Is this a bug?

> nconway=# create view baz (a,b) as select 'hello', 'world';
> WARNING:  column "a" has type "unknown"
> DETAIL:  Proceeding with relation creation anyway.

It's always done that, although the spelling of the notice has
varied over the years.

These days we tend to force "unknown" to become "text" if a specific
data type is really required, and I suppose a case could be made that
CREATE VIEW should do that too.  But the consequences of guessing wrong
are probably worse here than elsewhere, since you can't really change
the view column type short of dropping and recreating the view.

I'd almost argue that we should change this message to an error:
ERROR: column "a" has type "unknown"HINT: Explicitly cast the string literal to some specific type.
        regards, tom lane


Re: minor view creation weirdness

From
Neil Conway
Date:
On Thu, 2003-10-02 at 10:16, Tom Lane wrote:
> Neil Conway <neilc@samurai.com> writes:
> > nconway=# create view baz (a,b) as select 'hello', 'world';
> > WARNING:  column "a" has type "unknown"
> > DETAIL:  Proceeding with relation creation anyway.
> 
> It's always done that, although the spelling of the notice has
> varied over the years.

> I'd almost argue that we should change this message to an error

I agree.

-Neil




Re: minor view creation weirdness

From
Tom Lane
Date:
Neil Conway <neilc@samurai.com> writes:
> On Thu, 2003-10-02 at 10:16, Tom Lane wrote:
>> Neil Conway <neilc@samurai.com> writes:
>>> nconway=# create view baz (a,b) as select 'hello', 'world';
>>> WARNING:  column "a" has type "unknown"
>>> DETAIL:  Proceeding with relation creation anyway.

>> I'd almost argue that we should change this message to an error

> I agree.

Motion proposed and seconded; any objections out there?
        regards, tom lane


Re: minor view creation weirdness

From
Christopher Kings-Lynne
Date:
>>>nconway=# create view baz (a,b) as select 'hello', 'world';
>>>WARNING:  column "a" has type "unknown"
>>>DETAIL:  Proceeding with relation creation anyway.
>>
>>It's always done that, although the spelling of the notice has
>>varied over the years.
> 
> 
>>I'd almost argue that we should change this message to an error
> 
> 
> I agree.

Except that it would totally break backwards-compatibility?  Or will 
'unknown' fields in views be dumped with explicit casts?

Chris




Re: minor view creation weirdness

From
Greg Stark
Date:
Tom Lane <tgl@sss.pgh.pa.us> writes:

> >> I'd almost argue that we should change this message to an error
> 
> > I agree.
> 
> Motion proposed and seconded; any objections out there?

Uhm, doesn't the spec have anything to say about this? 
I mean, the view sure looks like standard SQL on its face.

In any case, I would sure think there was something strange about a query
working fine as a select but not working in a view:

slo=>  select 'foo' as a,'bar' as b; a  |  b  
-----+-----foo | bar
(1 row)

slo=> create view x as select 'foo' as a,'bar' as b;
WARNING:  Attribute "a" has an unknown typeProceeding with relation creation anyway
WARNING:  Attribute "b" has an unknown typeProceeding with relation creation anyway
CREATE VIEW

slo=> select * from x; a  |  b  
-----+-----foo | bar
(1 row)

-- 
greg



Re: minor view creation weirdness

From
Tom Lane
Date:
Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
>>> I'd almost argue that we should change this message to an error
>> 
>> I agree.

> Except that it would totally break backwards-compatibility?

Well, that's a possible problem.  How many such views do you think are
out there, given the existence of the warning?
        regards, tom lane


Re: minor view creation weirdness

From
Robert Treat
Date:
On Fri, 2003-10-03 at 00:50, Greg Stark wrote:
> 
> Tom Lane <tgl@sss.pgh.pa.us> writes:
> 
> > >> I'd almost argue that we should change this message to an error
> > 
> > > I agree.
> > 
> > Motion proposed and seconded; any objections out there?
> 
> Uhm, doesn't the spec have anything to say about this? 
> I mean, the view sure looks like standard SQL on its face.
> 
> In any case, I would sure think there was something strange about a query
> working fine as a select but not working in a view:
> 
> slo=>  select 'foo' as a,'bar' as b;
>   a  |  b  
> -----+-----
>  foo | bar
> (1 row)
> 
> slo=> create view x as select 'foo' as a,'bar' as b;
> WARNING:  Attribute "a" has an unknown type
>     Proceeding with relation creation anyway
> WARNING:  Attribute "b" has an unknown type
>     Proceeding with relation creation anyway
> CREATE VIEW
> 
> slo=> select * from x;
>   a  |  b  
> -----+-----
>  foo | bar
> (1 row)
> 

or the create table case:

rms=# create table x as select 'foo' as a, 'bar' as b;
WARNING:  42P16: attribute "a" has type UNKNOWN
DETAIL:  Proceeding with relation creation anyway.
LOCATION:  CheckAttributeType, heap.c:427
WARNING:  42P16: attribute "b" has type UNKNOWN
DETAIL:  Proceeding with relation creation anyway.
LOCATION:  CheckAttributeType, heap.c:427
WARNING:  attribute "a" has type UNKNOWN
DETAIL:  Proceeding with relation creation anyway.
WARNING:  attribute "b" has type UNKNOWN
DETAIL:  Proceeding with relation creation anyway.
SELECT

rms=# select * from x; a  |  b  
-----+-----foo | bar
(1 row)

rms=# \d x       Table "public.x"Column |   Type    | Modifiers 
--------+-----------+-----------a      | "unknown" | b      | "unknown" | 


Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL



Re: minor view creation weirdness

From
Tom Lane
Date:
Greg Stark <gsstark@mit.edu> writes:
> Uhm, doesn't the spec have anything to say about this? 
> I mean, the view sure looks like standard SQL on its face.

Well, you might read the spec as requiring the view column to have
datatype CHAR(n) where n is the length of the unknown literal.
I see in SQL92:
        9) The data type of a <character string literal> is fixed-length           character string. The length of a
<characterstring literal>           is the number of <character representation>s that it contains.           ...
  Note: <character string literal>s are allowed to be zero-length           strings (i.e., to contain no characters)
eventhough it is           not permitted to declare a <data type> that is CHARACTER with           <length> zero.
 

The NOTE would appear to imply that
CREATE VIEW v AS SELECT '';

is illegal, which is not a conclusion I care to follow blindly.  In any
case, in Postgres I would think we would want to take the type as "text"
not "CHAR(n)", spec or no spec.
        regards, tom lane