Обсуждение: Views an non-present column

Hi everyone,

I am playing with Postgresql 8.0 (Mandriva 2006) and there is something
I don't understand. Let's assume that I have a table with some columns.
I create the following view:

CREATE VIEW vista as select name from mytable;

Then I check that everything is fine:

EXPLAIN select name from vista;

OK, I get a "Seq Scan on...".

but if I do:

EXPLAIN select mytable.name from vista;

I get an horrible:

 Nested Loop  (cost=17.70..9834.70 rows=490000 width=82)
   ->  Seq Scan on mytable  (cost=0.00..17.00 rows=700 width=0)
   ->  Materialize  (cost=17.70..24.70 rows=700 width=82)
         ->  Seq Scan on mytable  (cost=0.00..17.00 rows=700 width=82)


I also get the following notice:
NOTICE:  Ajout d'une entrée manquante dans FROM (table «mytable»)

Bad translation:
 adding an entry missing in the clause FROM (table «mytable»)

My question is: is there a way to prevent such a behaviour ? I would
like to get an error instead.

Cheers,
Fabien SK

On Sat, Dec 10, 2005 at 19:04:18 +0100,
  Fabien SK <fabsk@free.fr> wrote:
> Hi everyone,
>
> I am playing with Postgresql 8.0 (Mandriva 2006) and there is something
> I don't understand. Let's assume that I have a table with some columns.
> I create the following view:
>
> CREATE VIEW vista as select name from mytable;
>
> Then I check that everything is fine:
>
> EXPLAIN select name from vista;
>
> OK, I get a "Seq Scan on...".
>
> but if I do:
>
> EXPLAIN select mytable.name from vista;
>
> I get an horrible:
>
>  Nested Loop  (cost=17.70..9834.70 rows=490000 width=82)
>    ->  Seq Scan on mytable  (cost=0.00..17.00 rows=700 width=0)
>    ->  Materialize  (cost=17.70..24.70 rows=700 width=82)
>          ->  Seq Scan on mytable  (cost=0.00..17.00 rows=700 width=82)
>
>
> I also get the following notice:
> NOTICE:  Ajout d'une entrée manquante dans FROM (table «mytable»)
>
> Bad translation:
>  adding an entry missing in the clause FROM (table «mytable»)
>
> My question is: is there a way to prevent such a behaviour ? I would
> like to get an error instead.

It depends on which version of postgres you are using. In 8.1 its off by
default. My memory is that the option to turn it off was first put in 8.0.
You can use SET ADD_MISSING_FROM = OFF to disable this.

Le samedi 10 décembre 2005 à 12:52 -0600, Bruno Wolff III a écrit :

> It depends on which version of postgres you are using. In 8.1 its off by
> default. My memory is that the option to turn it off was first put in 8.0.
> You can use SET ADD_MISSING_FROM = OFF to disable this.

Thank you a lot, it works perfectly, goodbye errors !

Fabien