Обсуждение: JDBC pg_description update needed for CVS tip

Would some JDBC hacker develop a patch for the following issue?  The
change is just barely large enough that I don't want to commit untested
code for it --- but not having a Java development environment at hand,
I can't test the updated code.

The problem is in DatabaseMetaData.java (same code in both jdbc1 and
jdbc2, looks like).  It does direct access to pg_description that isn't
right anymore.  In getTables, instead of

    java.sql.ResultSet dr = connection.ExecSQL("select description from pg_description where objoid="+r.getInt(2));

it should be

    java.sql.ResultSet dr = connection.ExecSQL("select obj_description("+r.getInt(2)+",'pg_class')");

In getColumns, the change is a little more involved, because
pg_attribute doesn't have an OID column anymore.  The initial query
can't fetch a.oid, but should fetch a.attrelid instead, and then the
pg_description query should become

    java.sql.ResultSet dr = connection.ExecSQL("select col_description("+r.getInt(1)+","+r.getInt(5)+")");

(col_description takes the table OID and the column's attnum).

The reason this is more than a 3-line change is that it should be done
either the old way or the new way depending on whether server version >=
7.2 or not, for backwards-compatibility of the driver.

It's possible there are other similar changes needed that I missed in a
quick lookover.

So, would some enterprising person fix the JDBC code to work with CVS
tip, and submit a patch?

        thanks, tom lane

On Fri, 10 Aug 2001 16:08:50 -0400, Tom Lane wrote:
[direct access to pg_description that isn't right anymore]
>So, would some enterprising person fix the JDBC code to work
>with CVS tip, and submit a patch?

I'm working on it Tom, but I may need a couple of days or so to
get this done. Is that OK? This is because I still need to setup
a test environment with a running server build from current CVS.
That's fine, I wanted to do that anyway. I'm also in the middle
a chess tournament and still need to work on my Queen's Gambit
Declined :-)

By the way, what does "tip" mean?

Regards,
René Pijlman

Rene Pijlman <rpijlman@wanadoo.nl> writes:
> By the way, what does "tip" mean?

"CVS tip" = "latest file versions in CVS".  Think tip of a branch...

            regards, tom lane

On Fri, 10 Aug 2001 16:08:50 -0400, you wrote:
>The problem is in DatabaseMetaData.java (same code in both jdbc1 and
>jdbc2, looks like).  It does direct access to pg_description that isn't
>right anymore.  In getTables, instead of
>
>    java.sql.ResultSet dr = connection.ExecSQL("select description from pg_description where objoid="+r.getInt(2));
>
>it should be
>
>    java.sql.ResultSet dr = connection.ExecSQL("select obj_description("+r.getInt(2)+",'pg_class')");

Done that (columns to). When testing I noticed a difference
between 7.1 and 7.2: when there is no comment on a table or
column, 7.1 returns the string "no remarks" in the REMARKS
column of the ResultSet from getTables()/getColumns(), whereas
7.2 returns null.

So it appears that your new statement that uses
obj_description() and col_description() returns one row with a
null when there is no comment, instead of 0 rows. Is this
intentional?

The JDBC spec says: "String object containing an explanatory
comment on the table/column, which may be null". So actually,
this new behaviour is closer to the standard than the old
behaviour and I'm inclined to leave it this way. In fact, I
might as well remove the defaultRemarks code from
DatabaseMetaData.java.

This might break existing code that doesn't follow the JDBC spec
and isn't prepared to handle a null in the REMARKS column of
getTables()/getColumns().

Regards,
René Pijlman

Rene Pijlman <rpijlman@wanadoo.nl> writes:
> So it appears that your new statement that uses
> obj_description() and col_description() returns one row with a
> null when there is no comment, instead of 0 rows. Is this
> intentional?

That is how selecting a function result would work.  If you don't
like the behavior then we can reconsider it --- but if it's per
spec then I think we should be happy.

            regards, tom lane