Thread: BUG #1169: Select table.oid from view seems to loop

The following bug has been logged online:

Bug reference:      1169
Logged by:          Robert Osowiecki

Email address:      robson@cavern.pl

PostgreSQL version: 7.4

Operating system:   Linux

Description:        Select table.oid from view seems to loop

Details:

Hello!

Here's what i found:

select count(*) from view1;
 count
-------
 12874
(1 row)

select table1.oid from view1;
NOTICE:  adding missing FROM-clause entry for table "table1"
(psql crashes)

select table1.ord from view1 limit 13000;
(13000 rows in result)

Hope it helps: feel free to ask for more details by email.

Yours,

Robson.

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> select table1.oid from view1;
> NOTICE:  adding missing FROM-clause entry for table "table1"
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

This is an unqualified join, and will yield count(table1)*count(view1)
rows.  table1.oid is not exposed by the view and so you can't select
it from the view.  What you wrote is interpreted as
    select table1.oid from view1, table1;

> (psql crashes)

I'm not surprised psql ran out of memory, but I'd have expected a more
graceful response than "crashing".  What happened *exactly*?  On my
machine, recent psql versions just discard excess data and then complain
later.

            regards, tom lane

On Thu, 17 Jun 2004, PostgreSQL Bugs List wrote:

> Description:        Select table.oid from view seems to loop
>
> Details:
>
> Hello!
>
> Here's what i found:
>
> select count(*) from view1;
>  count
> -------
>  12874
> (1 row)
>
> select table1.oid from view1;
> NOTICE:  adding missing FROM-clause entry for table "table1"
> (psql crashes)

Note the notice.  IIRC the above is technically invalid SQL, but
PostgreSQL tries to do what it thinks you want which is to transform it
into:
 select table1.oid from view1, table1;

There's a GUC variable (add_missing_from) which allows you to control
whether it does the transform.

PostgreSQL Bugs List wrote:
> The following bug has been logged online:
>
> Bug reference:      1169
> Logged by:          Robert Osowiecki
>
> Email address:      robson@cavern.pl
> PostgreSQL version: 7.4
> Operating system:   Linux
> Description:        Select table.oid from view seems to loop

> Here's what i found:
>
> select count(*) from view1;
>  count
> -------
>  12874
> (1 row)
>
> select table1.oid from view1;
> NOTICE:  adding missing FROM-clause entry for table "table1"
> (psql crashes)

This statement is probably not what you meant to say. There is no
"table1.oid" in view1, so PG is adding a reference to table1, turning
your query into:

   SELECT table1.oid FROM view1, table1;

This gives you the product of both relations, e.g. if you have 2000 rows
in view1 and 3000 in table1 that's 6,000,000 rows in total. You probably
run out of memory somewhere which looks like a crash.

In 7.4 there is a postgresql.conf setting to control this behaviour
(add_missing_from). I'd turn it off, as I've always found it to cause
problems rather than solve them.

HTH
--
   Richard Huxton
   Archonet Ltd

Richard Huxton wrote:

> In 7.4 there is a postgresql.conf setting to control this behaviour
> (add_missing_from). I'd turn it off, as I've always found it to cause
> problems rather than solve them.

I agree :)
PG now reports "ERROR: missing from clause", I've added "table1.oid" do
view definition
and everything works fine.

Thanks for your support and have a nice day!

Robson.