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