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

Fri, 10 Aug 2001 16:08:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
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

Sun, 12 Aug 2001 10:49:36 +0200
Rene Pijlman <rpijlman@wanadoo.nl>
Re: [JDBC] JDBC pg_description update needed for CVS tip
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

Sun, 12 Aug 2001 12:31:41 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [JDBC] JDBC pg_description update needed for CVS tip 
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

Mon, 13 Aug 2001 00:38:17 +0200
Rene Pijlman <rpijlman@wanadoo.nl>
Re: [JDBC] JDBC pg_description update needed for CVS tip
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

Sun, 12 Aug 2001 22:06:43 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [JDBC] JDBC pg_description update needed for CVS tip 
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
Mon, 13 Aug 2001 20:01:24 +0200
Rene Pijlman <rpijlman@wanadoo.nl>
Re: JDBC pg_description update needed for CVS tip
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

Thu, 16 Aug 2001 19:53:39 +0200
Rene Pijlman <rpijlman@wanadoo.nl>
Re: Re: [JDBC] JDBC pg_description update needed for CVS tip

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

Thu, 16 Aug 2001 17:45:23 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [PATCHES] Re: JDBC pg_description update needed for CVS tip
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
> 
> 



Thu, 16 Aug 2001 19:21:01 -0700
Barry Lind <barry@xythos.com>
Re: Re: [JDBC] JDBC pg_description update needed for CVS tip

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

Fri, 17 Aug 2001 09:59:31 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [PATCHES] Re: JDBC pg_description update needed for CVS tip

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

Fri, 17 Aug 2001 13:34:32 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: [JDBC] JDBC pg_description update needed for CVS tip

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

Fri, 7 Sep 2001 00:30:24 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] JDBC pg_description update needed for CVS tip
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:
> 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
>>
>>
> 



Thu, 06 Sep 2001 22:22:17 -0700
Barry Lind <barry@xythos.com>
Re: [HACKERS] JDBC pg_description update needed for CVS tip
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

Fri, 07 Sep 2001 01:34:46 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] JDBC pg_description update needed for CVS tip 
Interestingly it was fixed in the getColumns() method, until a patch 
that was applied yesterday broke it again.

--Barry


Tom Lane 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
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 
> 



Thu, 06 Sep 2001 23:39:53 -0700
Barry Lind <barry@xythos.com>
Re: [HACKERS] JDBC pg_description update needed for CVS tip
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>

Fri, 07 Sep 2001 10:15:01 +0200
Rene Pijlman <rene@lab.applinet.nl>
Re: [HACKERS] JDBC pg_description update needed for CVS tip 
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>

Fri, 07 Sep 2001 10:53:02 +0200
Rene Pijlman <rene@lab.applinet.nl>
Re: [HACKERS] JDBC pg_description update needed for CVS tip
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


Fri, 07 Sep 2001 16:45:57 +0200
Jeroen van Vianen <jeroen.van.vianen@satama.com>
Re: [HACKERS] JDBC pg_description update needed for CVS
If you have the time this weekend to work on addressing this, that would 
be great.

thanks,
--Barry

Rene Pijlman wrote:
> 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>
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 
> 



Fri, 07 Sep 2001 09:26:17 -0700
Barry Lind <barry@xythos.com>
Re: [HACKERS] JDBC pg_description update needed for CVS tip
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>
Sun, 09 Sep 2001 00:18:05 +0200
Rene Pijlman <rene@lab.applinet.nl>
Re: [HACKERS] JDBC pg_description update needed for CVS tip 

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.

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

[ Attachment, skipping... ]

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

Sat, 8 Sep 2001 21:15:54 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] JDBC pg_description update needed for CVS tip
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


Sun, 09 Sep 2001 14:48:41 +0200
Jeroen van Vianen <jeroen.van.vianen@satama.com>
Re: [HACKERS] JDBC pg_description update needed for CVS
On Sun, 09 Sep 2001 14:48:41 +0200, you wrote:
>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 think that can only be decided by measurement. 

What you're saying is:

    1 * c1 < (N + 1) * c2

but that can only be decided if we know c1 and c2 (meaning: the
execution times of two different queries, including round trip
overhead).

That doesn't mean I'm opposed to the change, on the contrary. As
a rule, I find a complex SQL statement more elegant than the
same 'algorithm' in procedural code. But in this case I wasn't
sure how to construct it.

Regards,
Renï¿½ Pijlman <rene@lab.applinet.nl>

Sun, 09 Sep 2001 14:58:40 +0200
Rene Pijlman <rene@lab.applinet.nl>
Re: [HACKERS] JDBC pg_description update needed for CVS tip 

Patch applied.  Thanks.

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

[ Attachment, skipping... ]

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

Mon, 10 Sep 2001 10:55:16 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: [HACKERS] JDBC pg_description update needed for CVS tip