I have remove the LEFT part of the join to pg_roles in
psql/description.c. I assume this is too risky for 8.1.X.

---------------------------------------------------------------------------

Tom Lane wrote:
> Andrew - Supernews <andrew+nonews@supernews.com> writes:
> > On 2006-02-06, Peter Eisentraut <peter_e@gmx.net> wrote:
> >> It already has indexes.
>
> > 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.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Index: src/bin/psql/describe.c
================================================== =================
RCS file: /cvsroot/pgsql/src/bin/psql/describe.c,v
retrieving revision 1.131
diff -c -c -r1.131 describe.c
*** src/bin/psql/describe.c 12 Feb 2006 03:22:19 -0000 1.131
--- src/bin/psql/describe.c 12 Feb 2006 19:26:31 -0000
***************
*** 194,200 ****
"\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");

/*
* we skip in/out funcs by excluding functions that take or return cstring
--- 194,200 ----
"\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 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,373 ****
_("Description"));
appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_database d"
! "\n LEFT JOIN pg_catalog.pg_roles r ON d.datdba = r.oid\n"
"ORDER BY 1;");

res = PSQLexec(buf.data, false);
--- 367,373 ----
_("Description"));
appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_database d"
! "\n JOIN pg_catalog.pg_roles r ON d.datdba = r.oid\n"
"ORDER BY 1;");

res = PSQLexec(buf.data, false);
***************
*** 1485,1491 ****

appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_class c"
! "\n LEFT 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,
--- 1485,1491 ----

appendPQExpBuffer(&buf,
"\nFROM pg_catalog.pg_class c"
! "\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,1733 ****
_("Access privileges"), _("Description"));

appendPQExpBuffer(&buf,
! "\nFROM pg_catalog.pg_namespace n LEFT 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 */
--- 1727,1733 ----
_("Access privileges"), _("Description"));

appendPQExpBuffer(&buf,
! "\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 */


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

Bruce Momjian wrote:
>
> I have remove the LEFT part of the join to pg_roles in
> psql/description.c. I assume this is too risky for 8.1.X.

Well, 8.1 already has per-role dependencies, so why not do it?


--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Alvaro Herrera wrote:
> Bruce Momjian wrote:
> >
> > I have remove the LEFT part of the join to pg_roles in
> > psql/description.c. I assume this is too risky for 8.1.X.
>
> Well, 8.1 already has per-role dependencies, so why not do it?

True, but if I messed up the SQL somehow, no one is testing it.

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

Would you eyeball it?

---------------------------------------------------------------------------

Alvaro Herrera wrote:
> Bruce Momjian wrote:
> >
> > I have remove the LEFT part of the join to pg_roles in
> > psql/description.c. I assume this is too risky for 8.1.X.
>
> Well, 8.1 already has per-role dependencies, so why not do it?
>
>
> --
> Alvaro Herrera http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

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

> Bruce Momjian wrote:
>> I have remove the LEFT part of the join to pg_roles in
>> psql/description.c. I assume this is too risky for 8.1.X.
>
> Well, 8.1 already has per-role dependencies, so why not do it?

I agree


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Christopher Kings-Lynne wrote:
> > Bruce Momjian wrote:
> >> I have remove the LEFT part of the join to pg_roles in
> >> psql/description.c. I assume this is too risky for 8.1.X.
> >
> > Well, 8.1 already has per-role dependencies, so why not do it?
>
> I agree

Done.

--
Bruce Momjian http://candle.pha.pa.us
SRA OSS, Inc. http://www.sraoss.com

+ If your life is a hard drive, Christ can be your backup. +

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