Re: Apparent anomaly with views and unions

Tom and Stephan, thank you both for taking the time to reply. Further
comments inline.

Tom Lane wrote:
> Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com> writes:
>> On Fri, 11 Feb 2005, Guy Rouillier wrote:
>>> Now I create a view like this:
>>> create or replace view v1 as select * from t1;
>>>
>>> Next, I attempt to update this view like this:
>>> create or replace view v1 as select * from t1 union select * from
>>> t2;
>>>
>>> I receive: ERROR: cannot change data type of view column "serv_id"
>
>> I'm pretty sure PostgreSQL treats the type of serv_id in the new view
>> as varchar with no limit rather than varchar(50).
>
> This is the same issue reported here:
> http://archives.postgresql.org/pgsql-general/2004-12/msg00508.php
> http://archives.postgresql.org/pgsql-hackers/2004-12/msg00408.php
> and is pretty closely related to this:
> http://archives.postgresql.org/pgsql-bugs/2005-02/msg00008.php

We appear to have two issues here:

(1) What is the meaning of "replace" as in replace view?
(2) What are the semantics for multiple set operations?

My original issue deals with (1). I'm unfortunately not well versed in
the SQL spec, but from a layman's point of view, if I'm replacing a view
definition, I don't expect that to be rejected because it is
incompatible with the original view definition. My new definition may
use entirely different set of tables than the original view. "create or
replace view" should accomplish the same thing as "drop view; create
view".

>
> I was looking at fixing it yesterday. The obvious path to a fix is
> to do select_common_type across all the members of a set-operation
> tree at once, rather than pairwise as we do it now. That bothers me
> a bit because it could change the semantics. Consider for example
>
> SELECT float8col UNION ALL (SELECT numericcol UNION SELECT
> numericcol)

This seems to be addressing point (2). In this particular case, I would
expect the query engine to follow the submitter's explicit directions,
since he or she provided an explicit grouping. If no parentheses are
included, then the optimizer or query rewriter is free to do as it
wishes.

--
Guy Rouillier