aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
authorBruce Momjian <bruce@momjian.us>2006-02-12 19:31:14 +0000
committerBruce Momjian <bruce@momjian.us>2006-02-12 19:31:14 +0000
commit05e27a9c20d4cd9f605e8601ca6b04be9056113e (patch)
tree74614ead3fed9c5a2d2efe06f675eb063efbc687
parent7d57a1825f62a67b0eddc65d8618a9043d14f9e8 (diff)
downloadpostgresql-05e27a9c20d4cd9f605e8601ca6b04be9056113e.tar.gz
postgresql-05e27a9c20d4cd9f605e8601ca6b04be9056113e.zip
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.
-rw-r--r--src/bin/psql/describe.c10
1 files changed, 5 insertions, 5 deletions
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 17ca722f307..1653ae2bdaf 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -3,7 +3,7 @@
*
* Copyright (c) 2000-2005, PostgreSQL Global Development Group
*
- * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.131 2006/02/12 03:22:19 momjian Exp $
+ * $PostgreSQL: pgsql/src/bin/psql/describe.c,v 1.132 2006/02/12 19:31:14 momjian Exp $
*/
#include "postgres_fe.h"
#include "describe.h"
@@ -194,7 +194,7 @@ describeFunctions(const char *pattern, bool verbose)
"\nFROM pg_catalog.pg_proc p"
"\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace"
"\n LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang"
- "\n LEFT JOIN pg_catalog.pg_roles r ON r.oid = p.proowner\n");
+ "\n JOIN pg_catalog.pg_roles r ON r.oid = p.proowner\n");
/*
* we skip in/out funcs by excluding functions that take or return cstring
@@ -367,7 +367,7 @@ listAllDbs(bool verbose)
_("Description"));
appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_database d"
- "\n LEFT JOIN pg_catalog.pg_roles r ON d.datdba = r.oid\n"
+ "\n JOIN pg_catalog.pg_roles r ON d.datdba = r.oid\n"
"ORDER BY 1;");
res = PSQLexec(buf.data, false);
@@ -1485,7 +1485,7 @@ listTables(const char *tabtypes, const char *pattern, bool verbose)
appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_class c"
- "\n LEFT JOIN pg_catalog.pg_roles r ON r.oid = c.relowner"
+ "\n JOIN pg_catalog.pg_roles r ON r.oid = c.relowner"
"\n LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace");
if (showIndexes)
appendPQExpBuffer(&buf,
@@ -1727,7 +1727,7 @@ listSchemas(const char *pattern, bool verbose)
_("Access privileges"), _("Description"));
appendPQExpBuffer(&buf,
- "\nFROM pg_catalog.pg_namespace n LEFT JOIN pg_catalog.pg_roles r\n"
+ "\nFROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_roles r\n"
" ON n.nspowner=r.oid\n"
"WHERE (n.nspname !~ '^pg_temp_' OR\n"
" n.nspname = (pg_catalog.current_schemas(true))[1])\n"); /* temp schema is first */