pgsql: Remove LEFT part of JOIN to pg_roles because of optimizer

From: momjian(at)postgresql(dot)org (Bruce Momjian)
To: pgsql-committers(at)postgresql(dot)org
Subject: pgsql: Remove LEFT part of JOIN to pg_roles because of optimizer
Date: 2006-02-12 19:31:14
Message-ID: 20060212193114.E42919DC81D@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-committers

Log Message:
-----------
Remove LEFT part of JOIN to pg_roles because of optimizer limitation:

> True, but they're not being used where you'd expect. This seems to be
> something to do with the fact that it's not pg_authid which is being
> accessed, but rather the view pg_roles.

I looked into this and it seems the problem is that the view doesn't
get flattened into the main query because of the has_nullable_targetlist
limitation in prepjointree.c. That's triggered because pg_roles has
'********'::text AS rolpassword
which isn't nullable, meaning it would produce wrong behavior if
referenced above the outer join.

Ultimately, the reason this is a problem is that the planner deals only
in simple Vars while processing joins; it doesn't want to think about
expressions. I'm starting to think that it may be time to fix this,
because I've run into several related restrictions lately, but it seems
like a nontrivial project.

In the meantime, reducing the LEFT JOIN to pg_roles to a JOIN as per
Peter's suggestion seems like the best short-term workaround.

Modified Files:
--------------
pgsql/src/bin/psql:
describe.c (r1.131 -> r1.132)
(http://developer.postgresql.org/cvsweb.cgi/pgsql/src/bin/psql/describe.c.diff?r1=1.131&r2=1.132)

Browse pgsql-committers by date

  From Date Subject
Next Message Bruce Momjian 2006-02-12 19:52:06 pgsql: Support "" for thousands separator and plus sign in to_char(),
Previous Message Bruce Momjian 2006-02-12 19:24:35 pgsql: Update README file.