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

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

"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.

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.