Adding missing FROM clause - thanks or no thanks?

[John Siracusa]

I recently had a problem where a bunch of postgres backends were taking up
huge amounts of CPU time. I found a bunch of log messages like this:

NOTICE: adding missing FROM-clause entry for table "b"

Which I eventually tracked to an improperly constructed query that reduced
to something like this:

select a1, a2 FROM a WHERE a1 = '...' AND b.b1 = '...';

Table b is pretty big and the conditions were such that it was (apparently)
doing a join of every row in a with every possible row of b. That's
certainly now what I wanted.

Anyway, I fixed the query construction bug, but I have a question: is this
"adding missing FROM-clause entry" behavior part of the SQL standard(s) or a
"feature" of Postgres? Prior to seeing this, I'd have expected a query like
the above to cause a fatal error in the parsing stage. Put another way, can
someone give me an example of when this behavior is useful or desirable?

-John
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Bruno Wolff III]

On Tue, Jan 27, 2004 at 13:00:15 -0500,
John Siracusa <si******@mindspring.com> wrote:

Anyway, I fixed the query construction bug, but I have a question: is this
"adding missing FROM-clause entry" behavior part of the SQL standard(s) or a
"feature" of Postgres? Prior to seeing this, I'd have expected a query like
the above to cause a fatal error in the parsing stage. Put another way, can
someone give me an example of when this behavior is useful or desirable?

It is an extension. I think starting with 7.4 you can turn it off.
It is sort of useful for delete where there isn't a syntax to specify
addition tables, though I think you can get the same functionality
even there using subselects in the where clause.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Bruce Momjian]

John Siracusa wrote:

I recently had a problem where a bunch of postgres backends were taking up
huge amounts of CPU time. I found a bunch of log messages like this:

NOTICE: adding missing FROM-clause entry for table "b"

Which I eventually tracked to an improperly constructed query that reduced
to something like this:

select a1, a2 FROM a WHERE a1 = '...' AND b.b1 = '...';

Table b is pretty big and the conditions were such that it was (apparently)
doing a join of every row in a with every possible row of b. That's
certainly now what I wanted.

Anyway, I fixed the query construction bug, but I have a question: is this
"adding missing FROM-clause entry" behavior part of the SQL standard(s) or a
"feature" of Postgres? Prior to seeing this, I'd have expected a query like
the above to cause a fatal error in the parsing stage. Put another way, can
someone give me an example of when this behavior is useful or desirable?

It is a PostgreSQL extension and can be turned off with a
postgresql.conf variable or via SET:

add_missing_from = false

--
Bruce Momjian | http://candle.pha.pa.us
pg***@candle.pha.pa.us | [phone]
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

[John Siracusa]

On 1/27/04 1:28 PM, Bruno Wolff III wrote:

It is an extension. I think starting with 7.4 you can turn it off.
It is sort of useful for delete where there isn't a syntax to specify
addition tables, though I think you can get the same functionality
even there using subselects in the where clause.
On 1/27/04 1:34 PM, Bruce Momjian wrote: It is a PostgreSQL extension and can be turned off with a
postgresql.conf variable or via SET:

add_missing_from = false

Great, thanks to both of you :)

-John
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[V i s h a l Kashyap]

Dear John Siracusa ,
Are you using version

7.4.x if no whats the version of PostgreSQL ?

AFAIK this limitation was introduced in 7.4.x refer the docs to verify.
Some of my application's query developed on 7.3.x also reported same
errors/ suggestions when same was taken to 7.4.x
I added the from tabel and all went well
but this behaviour is relent for subqueries.

I recently had a problem where a bunch of postgres backends were taking up
huge amounts of CPU time. I found a bunch of log messages like this:

--
Regards,
Vishal Kashyap

~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
I Know you believe my words so logon to Jabber.org
and add vi***********@jabber.org to your roster.
OR
Seek Me at 264360076
~*~*~*~*~*~*~*~*
I am usually called as Vishal Kashyap
but my Girlfriend calls me as Vishal CASH UP.
This is because others identify me because of my generosity
but my Girlfriend identify me because of my CASH.
~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings