Обсуждение: Postgres planner bug in 7.3.x and 7.4.1 ?

Hello

We're experiencing weird behaviours with both Postgres 7.3.x and 7.4.1
versions, relating to views based on views including fields based upon
plpgsql functions. Attached is a .sql file showing our problems, from a
co-worker who doesn't have immediate access to this mailing list.

Here's the psql output on a 7.4.1 version (t1, t2 already created), showing
the function being called :

postgres@msi:/$ psql -p5433 -d t2g_3 -f /tmp/t1.sql
You are now connected as new user postgres.
psql:/tmp/t1.sql:32: ERROR:  function "plpgsql_call_handler" already exists
with same argument types
psql:/tmp/t1.sql:35: ERROR:  language "plpgsql" already exists
CREATE FUNCTION
CREATE VIEW
CREATE VIEW
CREATE VIEW
CREATE VIEW
psql:/tmp/t1.sql:284: NOTICE:  f1 >>>>>>>> 8980
psql:/tmp/t1.sql:284: NOTICE:  f1 >>>>>>>> 8981
psql:/tmp/t1.sql:284: NOTICE:  f1 >>>>>>>> 8982
  num_adr | num_person | firstname | name
---------+------------+-----------+------
     6231 |       8982 | Chico     | Marx
(1 row)

  num_adr | num_person | firstname | name
---------+------------+-----------+------
     6231 |       8982 | Chico     | Marx
(1 row)

Thanks for any support

laurent

---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.545 / Virus Database: 339 - Release Date: 27/11/2003

Laurent Perez <downloader@free.fr> writes:
> We're experiencing weird behaviours with both Postgres 7.3.x and 7.4.1
> versions, relating to views based on views including fields based upon
> plpgsql functions.

There are a couple of things going on here.  The principal one is a
limitation that subqueries can't be flattened into the parent query
if they are below an outer join and have non-nullable output columns
(which is presently defined as "any column that's not a simple Var").
An output column that isn't nullable wouldn't be guaranteed to go to
null in null-extended rows, thus breaking the outer join semantics.

Since the subquery doesn't get flattened, all its output columns will
still get evaluated for each row demanded from the subquery.  The
optimization that discards unused output columns (including your
expensive function call) is part of the flattening process.

That applies to your problem because f1(t1.num_person) isn't nullable
--- that is, it might produce a non-null output even when num_person
is null.  Had you declared f1 as strict, then in principle the system
could recognize the column as nullable.  At the moment it won't, but
I'll see what I can do about fixing that for 7.5.

The other thing that's going on is a plain old bug.  Given the above
restriction, the system should not have flattened the example involving
v4, but it did so because of an oversight in handling nested views.
I have fixed that here:
http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/optimizer/prep/prepjointree.c
That's probably not really what you wanted to hear, since that will
guarantee the inefficient behavior in both cases :-(

The easiest workaround I can think of is to avoid putting the expensive
function call below an outer join, though that may not be real practical
for you.  Alternatively, since you are evidently concerned with cases
where the function-call column won't be referenced at all, maybe you
could make a variant version of the view that doesn't contain that
column in the first place.

            regards, tom lane