Thread: 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
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
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
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
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:
> 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
>>
>>
>
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: > 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) > >
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 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 > >
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
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
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
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>
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