Re: [HACKERS] JDBC pg_description update needed for CVS

At 00:18 9/9/2001 +0200, Rene Pijlman wrote:
>On Fri, 07 Sep 2001 01:34:46 -0400, Tom Lane wrote:
> >there is still an unpatched reference to pg_description in
> >getColumns(), in both jdbc1 and jdbc2.
>
>This was introduced by Jeroen's patch (see
>http://fts.postgresql.org/db/mw/msg.html?mid=1032468). Attached
>is a patch that returns getColumns() to using "select
>obj_description()" instead of direct access to pg_description,
>as per the request by Tom.
>
>I've incorporated Jeroen's fix to left outer join with
>pg_attrdef instead of inner join, so getColumns() also returns
>columns without a default value.
>
>I have, however, not included Jeroen's attempt to combine
>multiple queries into one huge multi-join query for better
>performance, because:
>1) I don't know how to do that using obj_description() instead
>of direct access to pg_description

Exactly. That's why I put a comment in my orginal mail
(http://fts.postgresql.org/db/mw/msg.html?mid=1032468) about not being able
to use the col_description in a (left) outer join and used the actual code
of col_description instead. Is it possible to do:

select t1.*, f from t1 left outer join
function_returning_a_single_row_or_null(parameters) f ?

I think this should be possible, but I have no clue how/whether the grammar
and/or executor should be changed to allow this. Or someone with more
experience with outer join SQL syntax might be able to help here.

>2) I don't think a performance improvement (if any) in this
>method is very important

It is of course a performance improvement if it uses only 1 SQL statement
rather than N+1 with N being the number of columns reported. E.g. if you
list all columns of all tables in a big database, this would be a huge win.
I noted that some of the JDBC MetaData functions in the Oracle JDBC driver
were really slow compared to PostgreSQL's (e.g. seconds slower).

>Because of the outer join, getColumns() will only work with a
>backend >= 7.1. Since the conditional coding for 7.1/7.2 and
>jdbc1/jdbc2 is already giving me headaches I didn't pursue a
>pre-7.1 solution.

Cheers,

Jeroen