JDBC pg_description update needed for CVS tip

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

Attached is the patch requested by Tom Lane (see below). It
includes two changes in the JDBC driver:

1) When connected to a backend >= 7.2: use obj_description() and
col_description() instead of direct access to pg_description.

2) In DatabaseMetaData.getTables()/getColumns()/getProcedures():
when there is no comment on the object, return null in the
REMARKS column of the ResultSet, instead of the default string
"no remarks".

Change 2 first appeared as a side-effect of change 1, but it is
actually more compliant with the JDBC spec: "String object
containing an explanatory comment on the table/column/procedure,
which may be null". The default string "no remarks" was strictly
speaking incorrect, as it could not be distinguished from a real
user comment "no remarks". So I removed the default string
completely.

Change 2 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()/getProcedures.

Patch tested with jdbc2 against both a 7.1 and a CVS tip
backend. I did not have a jdbc1 environment to build and test
with, but since the touched code is identical in jdbc1 and jdbc2
I don't foresee any problems.

Regards,
René Pijlman

On Fri, 10 Aug 2001 16:08:50 -0400, Tom Lane wrote:

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

---------------------------(end of broadcast)---------------------------
TIP 3: 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

Regards,
René Pijlman

I I'm not mistaken I haven't seen the usual confirmation ("Your
patch has been added to the PostgreSQL unapplied patches list")
for this patch yet.

Is there something wrong with the patch, or is it just waiting
for something or someone?

On Mon, 13 Aug 2001 20:01:24 +0200, I wrote:

Attached is the patch requested by Tom Lane (see below). It
includes two changes in the JDBC driver:

1) When connected to a backend >= 7.2: use obj_description() and
col_description() instead of direct access to pg_description.

2) In DatabaseMetaData.getTables()/getColumns()/getProcedures():
when there is no comment on the object, return null in the
REMARKS column of the ResultSet, instead of the default string
"no remarks".

Change 2 first appeared as a side-effect of change 1, but it is
actually more compliant with the JDBC spec: "String object
containing an explanatory comment on the table/column/procedure,
which may be null". The default string "no remarks" was strictly
speaking incorrect, as it could not be distinguished from a real
user comment "no remarks". So I removed the default string
completely.

Change 2 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()/getProcedures.

Patch tested with jdbc2 against both a 7.1 and a CVS tip
backend. I did not have a jdbc1 environment to build and test
with, but since the touched code is identical in jdbc1 and jdbc2
I don't foresee any problems.

Regards,
Ren� Pijlman

On Fri, 10 Aug 2001 16:08:50 -0400, Tom Lane wrote:

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

---------------------------(end of broadcast)---------------------------
TIP 3: 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

Regards,
Ren� Pijlman

Regards,
Ren� Pijlman

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

Attached is the patch requested by Tom Lane (see below). It
includes two changes in the JDBC driver:

1) When connected to a backend >= 7.2: use obj_description() and
col_description() instead of direct access to pg_description.

2) In DatabaseMetaData.getTables()/getColumns()/getProcedures():
when there is no comment on the object, return null in the
REMARKS column of the ResultSet, instead of the default string
"no remarks".

Change 2 first appeared as a side-effect of change 1, but it is
actually more compliant with the JDBC spec: "String object
containing an explanatory comment on the table/column/procedure,
which may be null". The default string "no remarks" was strictly
speaking incorrect, as it could not be distinguished from a real
user comment "no remarks". So I removed the default string
completely.

Change 2 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()/getProcedures.

Patch tested with jdbc2 against both a 7.1 and a CVS tip
backend. I did not have a jdbc1 environment to build and test
with, but since the touched code is identical in jdbc1 and jdbc2
I don't foresee any problems.

Regards,
Ren? Pijlman

On Fri, 10 Aug 2001 16:08:50 -0400, Tom Lane wrote:

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

---------------------------(end of broadcast)---------------------------
TIP 3: 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

Regards,
Ren? Pijlman

[ Attachment, skipping... ]

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

If it helps I reviewed that patch and it looks fine to me.

--Barry

Rene Pijlman wrote:

Applied. Thanks.

Attached is the patch requested by Tom Lane (see below). It
includes two changes in the JDBC driver:

1) When connected to a backend >= 7.2: use obj_description() and
col_description() instead of direct access to pg_description.

2) In DatabaseMetaData.getTables()/getColumns()/getProcedures():
when there is no comment on the object, return null in the
REMARKS column of the ResultSet, instead of the default string
"no remarks".

Change 2 first appeared as a side-effect of change 1, but it is
actually more compliant with the JDBC spec: "String object
containing an explanatory comment on the table/column/procedure,
which may be null". The default string "no remarks" was strictly
speaking incorrect, as it could not be distinguished from a real
user comment "no remarks". So I removed the default string
completely.

Change 2 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()/getProcedures.

Patch tested with jdbc2 against both a 7.1 and a CVS tip
backend. I did not have a jdbc1 environment to build and test
with, but since the touched code is identical in jdbc1 and jdbc2
I don't foresee any problems.

Regards,
Ren? Pijlman

On Fri, 10 Aug 2001 16:08:50 -0400, Tom Lane wrote:

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

---------------------------(end of broadcast)---------------------------
TIP 3: 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

Regards,
Ren? Pijlman

[ Attachment, skipping... ]

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

This patch was applied a few hours ago.

If it helps I reviewed that patch and it looks fine to me.

--Barry

Rene Pijlman wrote:

I I'm not mistaken I haven't seen the usual confirmation ("Your
patch has been added to the PostgreSQL unapplied patches list")
for this patch yet.

Is there something wrong with the patch, or is it just waiting
for something or someone?

On Mon, 13 Aug 2001 20:01:24 +0200, I wrote:

Attached is the patch requested by Tom Lane (see below). It
includes two changes in the JDBC driver:

1) When connected to a backend >= 7.2: use obj_description() and
col_description() instead of direct access to pg_description.

2) In DatabaseMetaData.getTables()/getColumns()/getProcedures():
when there is no comment on the object, return null in the
REMARKS column of the ResultSet, instead of the default string
"no remarks".

Change 2 first appeared as a side-effect of change 1, but it is
actually more compliant with the JDBC spec: "String object
containing an explanatory comment on the table/column/procedure,
which may be null". The default string "no remarks" was strictly
speaking incorrect, as it could not be distinguished from a real
user comment "no remarks". So I removed the default string
completely.

Change 2 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()/getProcedures.

Patch tested with jdbc2 against both a 7.1 and a CVS tip
backend. I did not have a jdbc1 environment to build and test
with, but since the touched code is identical in jdbc1 and jdbc2
I don't foresee any problems.

Regards,
Ren? Pijlman

On Fri, 10 Aug 2001 16:08:50 -0400, Tom Lane wrote:

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

---------------------------(end of broadcast)---------------------------
TIP 3: 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

Regards,
Ren? Pijlman

Regards,
Ren? Pijlman

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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

http://www.postgresql.org/search.mpl

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Can someone tackles this and supply a patch?

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

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

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

I believe this was done a while ago. (It looks like it was patched on
Aug 17 by a patch from Rene).

thanks,
--Barry

Bruce Momjian wrote:

Barry Lind <barry@xythos.com> writes:

I believe this was done a while ago. (It looks like it was patched on
Aug 17 by a patch from Rene).

Looking again, getTables() seems to be fixed, but there is still an
unpatched reference to pg_description in getColumns(), in both
jdbc1 and jdbc2.

regards, tom lane

Interestingly it was fixed in the getColumns() method, until a patch
that was applied yesterday broke it again.

--Barry

Tom Lane wrote:

On Fri, 07 Sep 2001 01:34:46 -0400, you wrote:

Looking again, getTables() seems to be fixed, but there is still an
unpatched reference to pg_description in getColumns(), in both
jdbc1 and jdbc2.

There shouldn't be, I fixed that in the same patch. I'll have a
look at it this weekend.

Regards,
Ren� Pijlman <rene@lab.applinet.nl>

On Thu, 06 Sep 2001 23:39:53 -0700, you wrote:

Interestingly it was fixed in the getColumns() method, until a patch
that was applied yesterday broke it again.

Ah, that's probably the getColumns() fix from my fellow
countryman that was based on an older version before my patch.

Let me know if I have to re-merge my changes with a new patch.
I'll have time for that this weekend, so it can be in 7.2 beta1.

Also, this calls for a regression test :-)

Regards,
Ren� Pijlman <rene@lab.applinet.nl>

At 00:30 9/7/2001 -0400, Bruce Momjian wrote:

Can someone tackles this and supply a patch?

This has been addressed in the patch that was recently committed for JDBC's
broken getColumn() support. As I'm using outer joins and was unable to come
up with SQL syntax that would correctly use an outer join with a function
returning a single row (col_description), I used the actual function
definition for col_description for >= 7.2 servers. For details see my mail
at http://fts.postgresql.org/db/mw/msg.html?mid=1032468

OTOH, I haven't touched JDBC's getTable() code.

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

--
Bruce Momjian                        |  http://candle.pha.pa.us
pgman@candle.pha.pa.us               |  (610) 853-3000
+  If your life is a hard drive,     |  830 Blythe Avenue
+  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Cheers,

Jeroen

If you have the time this weekend to work on addressing this, that would
be great.

thanks,
--Barry

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
2) I don't think a performance improvement (if any) in this
method is very important

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.

Regards,
René Pijlman <rene@lab.applinet.nl>