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


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org