LEFT JOIN used in psql describe.c

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Does anyone know why so many LEFT JOINs are used in psql/describe.c to
join to the pg_namespace table, like here:

I thought a pg_class row always pointed to a valid pg_namespace row
because of our dependency restrictions.

Yes, pg_relnamespace is definitely not null. I've actually already removed
the left joins from my \df patch, since I had to rewrite some of the
queries anyway. If this is wrong, please let me know of course!

The patch has actually been done for some time now, but the tab completion
part of it got tricky with things like \df <tab> and \dfS <tab>, since the
code pretty much assumes that the only differentiation of system/non-system
objects occurs in pg_class objects. I'll try to get back to it next week,
once DBD::Pg 1.41 is finished up.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200504012315
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFCThy7vJuQZxSWSsgRAuNRAKClDG8QDxnX7LJMWqODtGqKnClpQQCfaZZ8
UwEpCmHJOyfSOuF0MAkQ7xg=
=blNw
-----END PGP SIGNATURE-----

"Greg Sabino Mullane" <greg@turnstep.com> writes:

Does anyone know why so many LEFT JOINs are used in psql/describe.c to
join to the pg_namespace table, like here:

Yes, pg_relnamespace is definitely not null. I've actually already removed
the left joins from my \df patch, since I had to rewrite some of the
queries anyway. If this is wrong, please let me know of course!

I think the idea was to be certain to show every pg_proc entry (or other
catalog for other \d commands), no matter how badly broken the catalog
interrelationships might be. If there's not an unarguable reason
for eliminating the left joins I'd be inclined to keep it like that.
What does an inner join buy here, other than brittleness?

(Yeah, I have the perspective of a developer who deals with broken
situations every day. So?)

regards, tom lane

Tom Lane wrote:

"Greg Sabino Mullane" <greg@turnstep.com> writes:

Does anyone know why so many LEFT JOINs are used in psql/describe.c to
join to the pg_namespace table, like here:

Yes, pg_relnamespace is definitely not null. I've actually already removed
the left joins from my \df patch, since I had to rewrite some of the
queries anyway. If this is wrong, please let me know of course!

I think the idea was to be certain to show every pg_proc entry (or other
catalog for other \d commands), no matter how badly broken the catalog
interrelationships might be. If there's not an unarguable reason
for eliminating the left joins I'd be inclined to keep it like that.
What does an inner join buy here, other than brittleness?

(Yeah, I have the perspective of a developer who deals with broken
situations every day. So?)

If we have problems with the system catalogs, I don't see how this join
has a high probability of catching the problem. If there was some known
problem of the join not always working, I could see the use of LEFT
JOIN, but there isn't, so it just seems confusing, and these queries are
used by others as models of how to do system joins, so could confuse our
users as well.

I think the LEFT JOIN should be removed unless there is a known problem,
and if one shows up, we can re-add them later.

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

Bruce Momjian wrote:

Tom Lane wrote:

"Greg Sabino Mullane" <greg@turnstep.com> writes:

Does anyone know why so many LEFT JOINs are used in psql/describe.c to
join to the pg_namespace table, like here:

Yes, pg_relnamespace is definitely not null. I've actually already removed
the left joins from my \df patch, since I had to rewrite some of the
queries anyway. If this is wrong, please let me know of course!

I think the idea was to be certain to show every pg_proc entry (or other
catalog for other \d commands), no matter how badly broken the catalog
interrelationships might be. If there's not an unarguable reason
for eliminating the left joins I'd be inclined to keep it like that.
What does an inner join buy here, other than brittleness?

(Yeah, I have the perspective of a developer who deals with broken
situations every day. So?)

If we have problems with the system catalogs, I don't see how this join
has a high probability of catching the problem. If there was some known
problem of the join not always working, I could see the use of LEFT
JOIN, but there isn't, so it just seems confusing, and these queries are
used by others as models of how to do system joins, so could confuse our
users as well.

I think the LEFT JOIN should be removed unless there is a known problem,
and if one shows up, we can re-add them later.

I still think that the LEFT JOINs used in psql system queries is
confusing and perhaps adds performance overhead while adding little
reliability, but no one else seems to think so so I will drop the idea.

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

On Tue, 10 May 2005, Bruce Momjian wrote:

Bruce Momjian wrote:

Tom Lane wrote:

"Greg Sabino Mullane" <greg@turnstep.com> writes:

Does anyone know why so many LEFT JOINs are used in psql/describe.c to
join to the pg_namespace table, like here:

Yes, pg_relnamespace is definitely not null. I've actually already removed
the left joins from my \df patch, since I had to rewrite some of the
queries anyway. If this is wrong, please let me know of course!

I think the idea was to be certain to show every pg_proc entry (or other
catalog for other \d commands), no matter how badly broken the catalog
interrelationships might be. If there's not an unarguable reason
for eliminating the left joins I'd be inclined to keep it like that.
What does an inner join buy here, other than brittleness?

(Yeah, I have the perspective of a developer who deals with broken
situations every day. So?)

If we have problems with the system catalogs, I don't see how this join
has a high probability of catching the problem. If there was some known
problem of the join not always working, I could see the use of LEFT
JOIN, but there isn't, so it just seems confusing, and these queries are
used by others as models of how to do system joins, so could confuse our
users as well.

I think the LEFT JOIN should be removed unless there is a known problem,
and if one shows up, we can re-add them later.

I still think that the LEFT JOINs used in psql system queries is
confusing and perhaps adds performance overhead while adding little
reliability, but no one else seems to think so so I will drop the idea.

I'm a bit confused here, but I believe Tom (at least how I read it) was
agreeing with you about pulling the LEFT JOIN out ... "I think the LEFT
JOIN should be removed unless there is a known problem, and if one shows
up, we can re-add them later." ... or am I mis-quoting?

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664

Marc G. Fournier wrote:

On Tue, 10 May 2005, Bruce Momjian wrote:

Bruce Momjian wrote:

Tom Lane wrote:

"Greg Sabino Mullane" <greg@turnstep.com> writes:

Does anyone know why so many LEFT JOINs are used in psql/describe.c to
join to the pg_namespace table, like here:

Yes, pg_relnamespace is definitely not null. I've actually already removed
the left joins from my \df patch, since I had to rewrite some of the
queries anyway. If this is wrong, please let me know of course!

I think the idea was to be certain to show every pg_proc entry (or other
catalog for other \d commands), no matter how badly broken the catalog
interrelationships might be. If there's not an unarguable reason
for eliminating the left joins I'd be inclined to keep it like that.
What does an inner join buy here, other than brittleness?

(Yeah, I have the perspective of a developer who deals with broken
situations every day. So?)

If we have problems with the system catalogs, I don't see how this join
has a high probability of catching the problem. If there was some known
problem of the join not always working, I could see the use of LEFT
JOIN, but there isn't, so it just seems confusing, and these queries are
used by others as models of how to do system joins, so could confuse our
users as well.

I think the LEFT JOIN should be removed unless there is a known problem,
and if one shows up, we can re-add them later.

I still think that the LEFT JOINs used in psql system queries is
confusing and perhaps adds performance overhead while adding little
reliability, but no one else seems to think so so I will drop the idea.

I'm a bit confused here, but I believe Tom (at least how I read it) was
agreeing with you about pulling the LEFT JOIN out ... "I think the LEFT
JOIN should be removed unless there is a known problem, and if one shows
up, we can re-add them later." ... or am I mis-quoting?

I am actually quoting myself in the posting, so the words are mine, not
Tom's.

The basic issue is code simplicity vs. robustness, and I am leaning to
the former because there is no known robustness problem. It is actually
opposite of our opinions on checking for unreferenced files, where I
want robustness (because it is a known problem) and Tom wants simplicity
(though he is flexible on this), so it seems the two of us switch sides
occasionally. :-)

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