Thread: Speedup patch for getTables() and getIndexInfo()
Hi !!
The following patch speeds up the queries in getTables() and
getIndexInfo(). It replaces the looping of queries with a simple join
operation. Patch is against the current CVS (2002-09-08).
- Panu
PS. Tested against PostgreSQL 7.1.2 and PostgreSQL 7.2.1 databases
--------------------------------------------------------------------------
--- AbstractJdbc1DatabaseMetaData.java.org Sun Sep 8 20:58:57 2002
+++ AbstractJdbc1DatabaseMetaData.java Sun Sep 8 21:02:58 2002
@@ -1836,7 +1836,9 @@
f[4] = new Field(connection, "REMARKS", iVarcharOid, NAME_SIZE);
// Now form the query
- StringBuffer sql = new StringBuffer("select relname,oid,relkind from
pg_class where (");
+ StringBuffer sql = new
StringBuffer(connection.haveMinimumServerVersion("7.2") ?
+ "select relname,oid,relkind,obj_description(oid,'pg_class') from
pg_class where (" :
+ "select c.relname,c.oid,c.relkind,d.description from pg_class c
left outer join pg_description d on (d.objoid = c.oid) where (" );
boolean notFirst = false;
for (int i = 0;i < types.length;i++)
@@ -1863,24 +1865,6 @@
while (r.next())
{
byte[][] tuple = new byte[5][0];
-
- // Fetch the description for the table (if any)
- String getDescriptionStatement =
- connection.haveMinimumServerVersion("7.2") ?
- "select obj_description(" + r.getInt(2) + ",'pg_class')" :
- "select description from pg_description where objoid=" + r.getInt(2);
-
- java.sql.ResultSet dr = connection.ExecSQL(getDescriptionStatement);
-
- byte remarks[] = null;
-
- if (((AbstractJdbc1ResultSet)dr).getTupleCount() == 1)
- {
- dr.next();
- remarks = dr.getBytes(1);
- }
- dr.close();
-
String relKind;
switch (r.getBytes(3)[0])
{
@@ -1911,7 +1895,7 @@
tuple[1] = null; // Schema name
tuple[2] = r.getBytes(1); // Table name
tuple[3] = (relKind == null) ? null : relKind.getBytes(); // Table type
- tuple[4] = remarks; // Remarks
+ tuple[4] = r.getBytes(4); //Remarks
v.addElement(tuple);
}
r.close();
@@ -3096,8 +3080,8 @@
"x.indkey, " +
"c.reltuples, " +
"c.relpages, " +
- "x.indexrelid " +
- "FROM pg_index x, pg_class c, pg_class i, pg_am a " +
+ "b.attname " +
+ "FROM pg_index x left outer join pg_attribute b on (x.indexrelid
= b.attrelid), pg_class c, pg_class i, pg_am a " +
"WHERE ((c.relname = '" + tableName.toLowerCase() + "') " +
" AND (c.oid = x.indrelid) " +
" AND (i.oid = x.indexrelid) " +
@@ -3118,7 +3102,6 @@
{
columnOrdinals[o++] = Integer.parseInt(stok.nextToken());
}
- java.sql.ResultSet columnNameRS = connection.ExecSQL("select a.attname
FROM pg_attribute a WHERE a.attrelid = " + r.getInt(9));
for (int i = 0; i < columnOrdinals.length; i++)
{
byte [] [] tuple = new byte [13] [];
@@ -3134,14 +3117,7 @@
Integer.toString(java.sql.DatabaseMetaData.tableIndexHashed).getBytes()
:
Integer.toString(java.sql.DatabaseMetaData.tableIndexOther).getBytes();
tuple[7] = Integer.toString(i + 1).getBytes();
- if (columnNameRS.next())
- {
- tuple[8] = columnNameRS.getBytes(1);
- }
- else
- {
- tuple[8] = "".getBytes();
- }
+ tuple[8] = r.getBytes(9);
tuple[9] = null; // sort sequence ???
tuple[10] = r.getBytes(7); // inexact
tuple[11] = r.getBytes(8);
Panu,
The getIndexInfo uses an outer join which won't be backward compatible?
I did apply the getTables patch and will commit soon, can you modify the
getIndexInfo part?
Dave
On Sun, 2002-09-08 at 14:18, Panu Outinen wrote:
> Hi !!
>
> The following patch speeds up the queries in getTables() and
> getIndexInfo(). It replaces the looping of queries with a simple join
> operation. Patch is against the current CVS (2002-09-08).
>
> - Panu
>
> PS. Tested against PostgreSQL 7.1.2 and PostgreSQL 7.2.1 databases
>
> --------------------------------------------------------------------------
>
> --- AbstractJdbc1DatabaseMetaData.java.org Sun Sep 8 20:58:57 2002
> +++ AbstractJdbc1DatabaseMetaData.java Sun Sep 8 21:02:58 2002
> @@ -1836,7 +1836,9 @@
> f[4] = new Field(connection, "REMARKS", iVarcharOid, NAME_SIZE);
>
> // Now form the query
> - StringBuffer sql = new StringBuffer("select relname,oid,relkind from
> pg_class where (");
> + StringBuffer sql = new
> StringBuffer(connection.haveMinimumServerVersion("7.2") ?
> + "select relname,oid,relkind,obj_description(oid,'pg_class') from
> pg_class where (" :
> + "select c.relname,c.oid,c.relkind,d.description from pg_class c
> left outer join pg_description d on (d.objoid = c.oid) where (" );
>
> boolean notFirst = false;
> for (int i = 0;i < types.length;i++)
> @@ -1863,24 +1865,6 @@
> while (r.next())
> {
> byte[][] tuple = new byte[5][0];
> -
> - // Fetch the description for the table (if any)
> - String getDescriptionStatement =
> - connection.haveMinimumServerVersion("7.2") ?
> - "select obj_description(" + r.getInt(2) + ",'pg_class')" :
> - "select description from pg_description where objoid=" + r.getInt(2);
> -
> - java.sql.ResultSet dr = connection.ExecSQL(getDescriptionStatement);
> -
> - byte remarks[] = null;
> -
> - if (((AbstractJdbc1ResultSet)dr).getTupleCount() == 1)
> - {
> - dr.next();
> - remarks = dr.getBytes(1);
> - }
> - dr.close();
> -
> String relKind;
> switch (r.getBytes(3)[0])
> {
> @@ -1911,7 +1895,7 @@
> tuple[1] = null; // Schema name
> tuple[2] = r.getBytes(1); // Table name
> tuple[3] = (relKind == null) ? null : relKind.getBytes(); // Table type
> - tuple[4] = remarks; // Remarks
> + tuple[4] = r.getBytes(4); //Remarks
> v.addElement(tuple);
> }
> r.close();
> @@ -3096,8 +3080,8 @@
> "x.indkey, " +
> "c.reltuples, " +
> "c.relpages, " +
> - "x.indexrelid " +
> - "FROM pg_index x, pg_class c, pg_class i, pg_am a " +
> + "b.attname " +
> + "FROM pg_index x left outer join pg_attribute b on (x.indexrelid
> = b.attrelid), pg_class c, pg_class i, pg_am a " +
> "WHERE ((c.relname = '" + tableName.toLowerCase() + "') " +
> " AND (c.oid = x.indrelid) " +
> " AND (i.oid = x.indexrelid) " +
> @@ -3118,7 +3102,6 @@
> {
> columnOrdinals[o++] = Integer.parseInt(stok.nextToken());
> }
> - java.sql.ResultSet columnNameRS = connection.ExecSQL("select a.attname
> FROM pg_attribute a WHERE a.attrelid = " + r.getInt(9));
> for (int i = 0; i < columnOrdinals.length; i++)
> {
> byte [] [] tuple = new byte [13] [];
> @@ -3134,14 +3117,7 @@
> Integer.toString(java.sql.DatabaseMetaData.tableIndexHashed).getBytes()
> :
> Integer.toString(java.sql.DatabaseMetaData.tableIndexOther).getBytes();
> tuple[7] = Integer.toString(i + 1).getBytes();
> - if (columnNameRS.next())
> - {
> - tuple[8] = columnNameRS.getBytes(1);
> - }
> - else
> - {
> - tuple[8] = "".getBytes();
> - }
> + tuple[8] = r.getBytes(9);
> tuple[9] = null; // sort sequence ???
> tuple[10] = r.getBytes(7); // inexact
> tuple[11] = r.getBytes(8);
>
>
>
>
>
>
> ---------------------------(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
>
>
At 10:00 12.9.2002 -0400, Dave Cramer wrote:
>Panu,
>
>The getIndexInfo uses an outer join which won't be backward compatible?
>I did apply the getTables patch and will commit soon, can you modify the
>getIndexInfo part?
Well, both getTables() and getIndexInfo() part of my patch need the 'outer
join' functionality. And e.g. getColumns() uses 'outer join' always in
current code!
So how far backward compatible does it need to be nowadays ? I mean 7.3
version of Postgresql is already coming, isn't 7.1.x enough ?
- Panu
>Dave
>On Sun, 2002-09-08 at 14:18, Panu Outinen wrote:
> > Hi !!
> >
> > The following patch speeds up the queries in getTables() and
> > getIndexInfo(). It replaces the looping of queries with a simple join
> > operation. Patch is against the current CVS (2002-09-08).
> >
> > - Panu
> >
> > PS. Tested against PostgreSQL 7.1.2 and PostgreSQL 7.2.1 databases
> >
> > --------------------------------------------------------------------------
> >
> > --- AbstractJdbc1DatabaseMetaData.java.org Sun Sep 8 20:58:57 2002
> > +++ AbstractJdbc1DatabaseMetaData.java Sun Sep 8 21:02:58 2002
> > @@ -1836,7 +1836,9 @@
> > f[4] = new Field(connection, "REMARKS", iVarcharOid,
> NAME_SIZE);
> >
> > // Now form the query
> > - StringBuffer sql = new StringBuffer("select
> relname,oid,relkind from
> > pg_class where (");
> > + StringBuffer sql = new
> > StringBuffer(connection.haveMinimumServerVersion("7.2") ?
> > + "select
> relname,oid,relkind,obj_description(oid,'pg_class') from
> > pg_class where (" :
> > + "select c.relname,c.oid,c.relkind,d.description from
> pg_class c
> > left outer join pg_description d on (d.objoid = c.oid) where (" );
> >
> > boolean notFirst = false;
> > for (int i = 0;i < types.length;i++)
> > @@ -1863,24 +1865,6 @@
> > while (r.next())
> > {
> > byte[][] tuple = new byte[5][0];
> > -
> > - // Fetch the description for the table (if any)
> > - String getDescriptionStatement =
> > - connection.haveMinimumServerVersion("7.2") ?
> > - "select obj_description(" + r.getInt(2) +
> ",'pg_class')" :
> > - "select description from pg_description
> where objoid=" + r.getInt(2);
> > -
> > - java.sql.ResultSet dr =
> connection.ExecSQL(getDescriptionStatement);
> > -
> > - byte remarks[] = null;
> > -
> > - if (((AbstractJdbc1ResultSet)dr).getTupleCount()
> == 1)
> > - {
> > - dr.next();
> > - remarks = dr.getBytes(1);
> > - }
> > - dr.close();
> > -
> > String relKind;
> > switch (r.getBytes(3)[0])
> > {
> > @@ -1911,7 +1895,7 @@
> > tuple[1] = null; // Schema name
> > tuple[2] = r.getBytes(1); // Table name
> > tuple[3] = (relKind == null) ? null :
> relKind.getBytes(); // Table type
> > - tuple[4] = remarks; // Remarks
> > + tuple[4] = r.getBytes(4); //Remarks
> > v.addElement(tuple);
> > }
> > r.close();
> > @@ -3096,8 +3080,8 @@
> > "x.indkey, " +
> > "c.reltuples, " +
> > "c.relpages, " +
> > - "x.indexrelid " +
> > - "FROM pg_index
> x, pg_class c, pg_class i, pg_am a " +
> > + "b.attname " +
> > + "FROM pg_index
> x left outer join pg_attribute b on (x.indexrelid
> > = b.attrelid), pg_class c, pg_class i, pg_am a " +
> > "WHERE
> ((c.relname = '" + tableName.toLowerCase() + "') " +
> > " AND (c.oid =
> x.indrelid) " +
> > " AND (i.oid =
> x.indexrelid) " +
> > @@ -3118,7 +3102,6 @@
> > {
> > columnOrdinals[o++] =
> Integer.parseInt(stok.nextToken());
> > }
> > - java.sql.ResultSet columnNameRS =
> connection.ExecSQL("select a.attname
> > FROM pg_attribute a WHERE a.attrelid = " + r.getInt(9));
> > for (int i = 0; i < columnOrdinals.length; i++)
> > {
> > byte [] [] tuple = new byte [13] [];
> > @@ -3134,14 +3117,7 @@
> >
> Integer.toString(java.sql.DatabaseMetaData.tableIndexHashed).getBytes()
> > :
> >
> Integer.toString(java.sql.DatabaseMetaData.tableIndexOther).getBytes();
> > tuple[7] = Integer.toString(i +
> 1).getBytes();
> > - if (columnNameRS.next())
> > - {
> > - tuple[8] = columnNameRS.getBytes(1);
> > - }
> > - else
> > - {
> > - tuple[8] = "".getBytes();
> > - }
> > + tuple[8] = r.getBytes(9);
> > tuple[9] = null; // sort sequence ???
> > tuple[10] = r.getBytes(7); // inexact
> > tuple[11] = r.getBytes(8);
> >
> >
> >
> >
> >
> >
> > ---------------------------(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
> >
> >
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
What releases should the jdbc driver support? Was: Speedup patch for getTables() and getIndexInfo()
From
Barry Lind
Date:
We have been trying to remain backwardly compatible back to 7.0. But as
you point out some non-backwardly compatible things have been added. It
is possible to use outer-joins for 7.1 and greater and not use them for
7.0 (i.e. if (connection.haveMinimumServerVersion("7.1")) { do join
stuff } else { do existing logic } ). But before doing that does anyone
have an opinion on how far back the driver should support server versions?
I would suggest we have a policy of supporting the current and two
previous versions. Since the current production version is 7.2 that
means supporting 7.0 and 7.1 in the 7.2 driver, and supporting 7.1 and
7.2 in the 7.3 driver. The problem with this is that we generally don't
backport fixes to previous releases, so if someone found a bug in the
7.2 driver and was running on a 7.0 database they wouldn't be able to
easily get a fix since we would fix it in the 7.3 version which wouldn't
support 7.0. If we decide on a policy I think it is then a good idea to
have the driver error when connecting to a database version that isn't
supported.
thanks,
--Barry
Panu Outinen wrote:
> At 10:00 12.9.2002 -0400, Dave Cramer wrote:
>
>> Panu,
>>
>> The getIndexInfo uses an outer join which won't be backward compatible?
>> I did apply the getTables patch and will commit soon, can you modify the
>> getIndexInfo part?
>
>
> Well, both getTables() and getIndexInfo() part of my patch need the
> 'outer join' functionality. And e.g. getColumns() uses 'outer join'
> always in current code!
>
> So how far backward compatible does it need to be nowadays ? I mean 7.3
> version of Postgresql is already coming, isn't 7.1.x enough ?
>
> - Panu
>
>
>
>> Dave
>> On Sun, 2002-09-08 at 14:18, Panu Outinen wrote:
>> > Hi !!
>> >
>> > The following patch speeds up the queries in getTables() and
>> > getIndexInfo(). It replaces the looping of queries with a simple join
>> > operation. Patch is against the current CVS (2002-09-08).
>> >
>> > - Panu
>> >
>> > PS. Tested against PostgreSQL 7.1.2 and PostgreSQL 7.2.1 databases
>> >
>> >
>>
--------------------------------------------------------------------------
>>
>> >
>> > --- AbstractJdbc1DatabaseMetaData.java.org Sun Sep 8 20:58:57 2002
>> > +++ AbstractJdbc1DatabaseMetaData.java Sun Sep 8 21:02:58 2002
>> > @@ -1836,7 +1836,9 @@
>> > f[4] = new Field(connection, "REMARKS", iVarcharOid,
>> NAME_SIZE);
>> >
>> > // Now form the query
>> > - StringBuffer sql = new StringBuffer("select
>> relname,oid,relkind from
>> > pg_class where (");
>> > + StringBuffer sql = new
>> > StringBuffer(connection.haveMinimumServerVersion("7.2") ?
>> > + "select
>> relname,oid,relkind,obj_description(oid,'pg_class') from
>> > pg_class where (" :
>> > + "select c.relname,c.oid,c.relkind,d.description from
>> pg_class c
>> > left outer join pg_description d on (d.objoid = c.oid) where (" );
>> >
>> > boolean notFirst = false;
>> > for (int i = 0;i < types.length;i++)
>> > @@ -1863,24 +1865,6 @@
>> > while (r.next())
>> > {
>> > byte[][] tuple = new byte[5][0];
>> > -
>> > - // Fetch the description for the table (if any)
>> > - String getDescriptionStatement =
>> > -
>> connection.haveMinimumServerVersion("7.2") ?
>> > - "select obj_description(" +
>> r.getInt(2) + ",'pg_class')" :
>> > - "select description from
>> pg_description where objoid=" + r.getInt(2);
>> > -
>> > - java.sql.ResultSet dr =
>> connection.ExecSQL(getDescriptionStatement);
>> > -
>> > - byte remarks[] = null;
>> > -
>> > - if
>> (((AbstractJdbc1ResultSet)dr).getTupleCount() == 1)
>> > - {
>> > - dr.next();
>> > - remarks = dr.getBytes(1);
>> > - }
>> > - dr.close();
>> > -
>> > String relKind;
>> > switch (r.getBytes(3)[0])
>> > {
>> > @@ -1911,7 +1895,7 @@
>> > tuple[1] = null; // Schema name
>> > tuple[2] = r.getBytes(1); // Table name
>> > tuple[3] = (relKind == null) ? null :
>> relKind.getBytes(); // Table type
>> > - tuple[4] = remarks; // Remarks
>> > + tuple[4] = r.getBytes(4); //Remarks
>> > v.addElement(tuple);
>> > }
>> > r.close();
>> > @@ -3096,8 +3080,8 @@
>> >
"x.indkey, " +
>> >
>> "c.reltuples, " +
>> >
>> "c.relpages, " +
>> > -
>> "x.indexrelid " +
>> > - "FROM
>> pg_index x, pg_class c, pg_class i, pg_am a " +
>> > +
"b.attname " +
>> > + "FROM
>> pg_index x left outer join pg_attribute b on (x.indexrelid
>> > = b.attrelid), pg_class c, pg_class i, pg_am a " +
>> > "WHERE
>> ((c.relname = '" + tableName.toLowerCase() + "') " +
>> > " AND
>> (c.oid = x.indrelid) " +
>> > " AND
>> (i.oid = x.indexrelid) " +
>> > @@ -3118,7 +3102,6 @@
>> > {
>> > columnOrdinals[o++] =
>> Integer.parseInt(stok.nextToken());
>> > }
>> > - java.sql.ResultSet columnNameRS =
>> connection.ExecSQL("select a.attname
>> > FROM pg_attribute a WHERE a.attrelid = " + r.getInt(9));
>> > for (int i = 0; i < columnOrdinals.length; i++)
>> > {
>> > byte [] [] tuple = new byte [13] [];
>> > @@ -3134,14 +3117,7 @@
>> >
Integer.toString(java.sql.DatabaseMetaData.tableIndexHashed).getBytes()
>> > :
>> >
Integer.toString(java.sql.DatabaseMetaData.tableIndexOther).getBytes();
>> > tuple[7] = Integer.toString(i +
>> 1).getBytes();
>> > - if (columnNameRS.next())
>> > - {
>> > - tuple[8] =
>> columnNameRS.getBytes(1);
>> > - }
>> > - else
>> > - {
>> > - tuple[8] = "".getBytes();
>> > - }
>> > + tuple[8] = r.getBytes(9);
>> > tuple[9] = null; // sort sequence ???
>> > tuple[10] = r.getBytes(7); //
>> inexact
>> > tuple[11] = r.getBytes(8);
>> >
>> >
>> >
>> >
>> >
>> >
>> > ---------------------------(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
>> >
>> >
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>
At 09:51 12.9.2002 -0700, Barry Lind wrote:
>We have been trying to remain backwardly compatible back to 7.0. But as
>you point out some non-backwardly compatible things have been added. It
>is possible to use outer-joins for 7.1 and greater and not use them for
>7.0 (i.e. if (connection.haveMinimumServerVersion("7.1")) { do join
>stuff } else { do existing logic } ). But before doing that does anyone
>have an opinion on how far back the driver should support server versions?
>
>I would suggest we have a policy of supporting the current and two
>previous versions. Since the current production version is 7.2 that
>means supporting 7.0 and 7.1 in the 7.2 driver, and supporting 7.1 and
>7.2 in the 7.3 driver.
My vote goes for this policy. Otherwise the driver gets bloated with code.
And what about the testing phase, people would need to have several
different database versions available for themselves in order to do full
testing. Usually the best tester is the coder himself :-)
>The problem with this is that we generally don't
>backport fixes to previous releases, so if someone found a bug in the
>7.2 driver and was running on a 7.0 database they wouldn't be able to
>easily get a fix since we would fix it in the 7.3 version which wouldn't
>support 7.0. If we decide on a policy I think it is then a good idea to
>have the driver error when connecting to a database version that isn't
>supported.
How about just giving a warning like "Too old database version, not all
features supported". If basic functionality is still supported (SELECT,
INSERT, DELETE, UPDATE) it's still useful.
This reminds me of the current production version of Microsoft's JDBC
driver for SQL Server 2000. They added the version check into the driver
just before the released version (it was nearly fully functional and still
would be with SQL Server 7.0). Of course they have other reasons to do this.
- Panu
>thanks,
>--Barry
>
>
>Panu Outinen wrote:
> > At 10:00 12.9.2002 -0400, Dave Cramer wrote:
> >
> >> Panu,
> >>
> >> The getIndexInfo uses an outer join which won't be backward compatible?
> >> I did apply the getTables patch and will commit soon, can you modify the
> >> getIndexInfo part?
> >
> >
> > Well, both getTables() and getIndexInfo() part of my patch need the
> > 'outer join' functionality. And e.g. getColumns() uses 'outer join'
> > always in current code!
> >
> > So how far backward compatible does it need to be nowadays ? I mean 7.3
> > version of Postgresql is already coming, isn't 7.1.x enough ?
> >
> > - Panu
> >
...
I don't have any problem with only supporting the last 2 versions. The
rational is that: many bugs get fixed along with features so it behooves
people to upgrade their servers anyway. There is no barrier to upgrading
other than time and effort.
Dave
On Thu, 2002-09-12 at 12:51, Barry Lind wrote:
> We have been trying to remain backwardly compatible back to 7.0. But as
> you point out some non-backwardly compatible things have been added. It
> is possible to use outer-joins for 7.1 and greater and not use them for
> 7.0 (i.e. if (connection.haveMinimumServerVersion("7.1")) { do join
> stuff } else { do existing logic } ). But before doing that does anyone
> have an opinion on how far back the driver should support server versions?
>
> I would suggest we have a policy of supporting the current and two
> previous versions. Since the current production version is 7.2 that
> means supporting 7.0 and 7.1 in the 7.2 driver, and supporting 7.1 and
> 7.2 in the 7.3 driver. The problem with this is that we generally don't
> backport fixes to previous releases, so if someone found a bug in the
> 7.2 driver and was running on a 7.0 database they wouldn't be able to
> easily get a fix since we would fix it in the 7.3 version which wouldn't
> support 7.0. If we decide on a policy I think it is then a good idea to
> have the driver error when connecting to a database version that isn't
> supported.
>
> thanks,
> --Barry
>
>
> Panu Outinen wrote:
> > At 10:00 12.9.2002 -0400, Dave Cramer wrote:
> >
> >> Panu,
> >>
> >> The getIndexInfo uses an outer join which won't be backward compatible?
> >> I did apply the getTables patch and will commit soon, can you modify the
> >> getIndexInfo part?
> >
> >
> > Well, both getTables() and getIndexInfo() part of my patch need the
> > 'outer join' functionality. And e.g. getColumns() uses 'outer join'
> > always in current code!
> >
> > So how far backward compatible does it need to be nowadays ? I mean 7.3
> > version of Postgresql is already coming, isn't 7.1.x enough ?
> >
> > - Panu
> >
> >
> >
> >> Dave
> >> On Sun, 2002-09-08 at 14:18, Panu Outinen wrote:
> >> > Hi !!
> >> >
> >> > The following patch speeds up the queries in getTables() and
> >> > getIndexInfo(). It replaces the looping of queries with a simple join
> >> > operation. Patch is against the current CVS (2002-09-08).
> >> >
> >> > - Panu
> >> >
> >> > PS. Tested against PostgreSQL 7.1.2 and PostgreSQL 7.2.1 databases
> >> >
> >> >
> >>
> --------------------------------------------------------------------------
> >>
> >> >
> >> > --- AbstractJdbc1DatabaseMetaData.java.org Sun Sep 8 20:58:57 2002
> >> > +++ AbstractJdbc1DatabaseMetaData.java Sun Sep 8 21:02:58 2002
> >> > @@ -1836,7 +1836,9 @@
> >> > f[4] = new Field(connection, "REMARKS", iVarcharOid,
> >> NAME_SIZE);
> >> >
> >> > // Now form the query
> >> > - StringBuffer sql = new StringBuffer("select
> >> relname,oid,relkind from
> >> > pg_class where (");
> >> > + StringBuffer sql = new
> >> > StringBuffer(connection.haveMinimumServerVersion("7.2") ?
> >> > + "select
> >> relname,oid,relkind,obj_description(oid,'pg_class') from
> >> > pg_class where (" :
> >> > + "select c.relname,c.oid,c.relkind,d.description from
> >> pg_class c
> >> > left outer join pg_description d on (d.objoid = c.oid) where (" );
> >> >
> >> > boolean notFirst = false;
> >> > for (int i = 0;i < types.length;i++)
> >> > @@ -1863,24 +1865,6 @@
> >> > while (r.next())
> >> > {
> >> > byte[][] tuple = new byte[5][0];
> >> > -
> >> > - // Fetch the description for the table (if any)
> >> > - String getDescriptionStatement =
> >> > -
> >> connection.haveMinimumServerVersion("7.2") ?
> >> > - "select obj_description(" +
> >> r.getInt(2) + ",'pg_class')" :
> >> > - "select description from
> >> pg_description where objoid=" + r.getInt(2);
> >> > -
> >> > - java.sql.ResultSet dr =
> >> connection.ExecSQL(getDescriptionStatement);
> >> > -
> >> > - byte remarks[] = null;
> >> > -
> >> > - if
> >> (((AbstractJdbc1ResultSet)dr).getTupleCount() == 1)
> >> > - {
> >> > - dr.next();
> >> > - remarks = dr.getBytes(1);
> >> > - }
> >> > - dr.close();
> >> > -
> >> > String relKind;
> >> > switch (r.getBytes(3)[0])
> >> > {
> >> > @@ -1911,7 +1895,7 @@
> >> > tuple[1] = null; // Schema name
> >> > tuple[2] = r.getBytes(1); // Table name
> >> > tuple[3] = (relKind == null) ? null :
> >> relKind.getBytes(); // Table type
> >> > - tuple[4] = remarks; // Remarks
> >> > + tuple[4] = r.getBytes(4); //Remarks
> >> > v.addElement(tuple);
> >> > }
> >> > r.close();
> >> > @@ -3096,8 +3080,8 @@
> >> >
> "x.indkey, " +
> >> >
> >> "c.reltuples, " +
> >> >
> >> "c.relpages, " +
> >> > -
> >> "x.indexrelid " +
> >> > - "FROM
> >> pg_index x, pg_class c, pg_class i, pg_am a " +
> >> > +
> "b.attname " +
> >> > + "FROM
> >> pg_index x left outer join pg_attribute b on (x.indexrelid
> >> > = b.attrelid), pg_class c, pg_class i, pg_am a " +
> >> > "WHERE
> >> ((c.relname = '" + tableName.toLowerCase() + "') " +
> >> > " AND
> >> (c.oid = x.indrelid) " +
> >> > " AND
> >> (i.oid = x.indexrelid) " +
> >> > @@ -3118,7 +3102,6 @@
> >> > {
> >> > columnOrdinals[o++] =
> >> Integer.parseInt(stok.nextToken());
> >> > }
> >> > - java.sql.ResultSet columnNameRS =
> >> connection.ExecSQL("select a.attname
> >> > FROM pg_attribute a WHERE a.attrelid = " + r.getInt(9));
> >> > for (int i = 0; i < columnOrdinals.length; i++)
> >> > {
> >> > byte [] [] tuple = new byte [13] [];
> >> > @@ -3134,14 +3117,7 @@
> >> >
> Integer.toString(java.sql.DatabaseMetaData.tableIndexHashed).getBytes()
> >> > :
> >> >
> Integer.toString(java.sql.DatabaseMetaData.tableIndexOther).getBytes();
> >> > tuple[7] = Integer.toString(i +
> >> 1).getBytes();
> >> > - if (columnNameRS.next())
> >> > - {
> >> > - tuple[8] =
> >> columnNameRS.getBytes(1);
> >> > - }
> >> > - else
> >> > - {
> >> > - tuple[8] = "".getBytes();
> >> > - }
> >> > + tuple[8] = r.getBytes(9);
> >> > tuple[9] = null; // sort sequence ???
> >> > tuple[10] = r.getBytes(7); //
> >> inexact
> >> > tuple[11] = r.getBytes(8);
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > ---------------------------(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
> >> >
> >> >
> >>
> >>
> >>
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> >
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
I agree
On Fri, 2002-09-13 at 05:54, Panu Outinen wrote:
> At 09:51 12.9.2002 -0700, Barry Lind wrote:
> >We have been trying to remain backwardly compatible back to 7.0. But as
> >you point out some non-backwardly compatible things have been added. It
> >is possible to use outer-joins for 7.1 and greater and not use them for
> >7.0 (i.e. if (connection.haveMinimumServerVersion("7.1")) { do join
> >stuff } else { do existing logic } ). But before doing that does anyone
> >have an opinion on how far back the driver should support server versions?
> >
> >I would suggest we have a policy of supporting the current and two
> >previous versions. Since the current production version is 7.2 that
> >means supporting 7.0 and 7.1 in the 7.2 driver, and supporting 7.1 and
> >7.2 in the 7.3 driver.
>
> My vote goes for this policy. Otherwise the driver gets bloated with code.
> And what about the testing phase, people would need to have several
> different database versions available for themselves in order to do full
> testing. Usually the best tester is the coder himself :-)
>
> >The problem with this is that we generally don't
> >backport fixes to previous releases, so if someone found a bug in the
> >7.2 driver and was running on a 7.0 database they wouldn't be able to
> >easily get a fix since we would fix it in the 7.3 version which wouldn't
> >support 7.0. If we decide on a policy I think it is then a good idea to
> >have the driver error when connecting to a database version that isn't
> >supported.
>
> How about just giving a warning like "Too old database version, not all
> features supported". If basic functionality is still supported (SELECT,
> INSERT, DELETE, UPDATE) it's still useful.
>
> This reminds me of the current production version of Microsoft's JDBC
> driver for SQL Server 2000. They added the version check into the driver
> just before the released version (it was nearly fully functional and still
> would be with SQL Server 7.0). Of course they have other reasons to do this.
>
> - Panu
>
>
> >thanks,
> >--Barry
> >
> >
> >Panu Outinen wrote:
> > > At 10:00 12.9.2002 -0400, Dave Cramer wrote:
> > >
> > >> Panu,
> > >>
> > >> The getIndexInfo uses an outer join which won't be backward compatible?
> > >> I did apply the getTables patch and will commit soon, can you modify the
> > >> getIndexInfo part?
> > >
> > >
> > > Well, both getTables() and getIndexInfo() part of my patch need the
> > > 'outer join' functionality. And e.g. getColumns() uses 'outer join'
> > > always in current code!
> > >
> > > So how far backward compatible does it need to be nowadays ? I mean 7.3
> > > version of Postgresql is already coming, isn't 7.1.x enough ?
> > >
> > > - Panu
> > >
> ...
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
--
Felipe Schnack
Analista de Sistemas
felipes@ritterdosreis.br
Cel.: (51)91287530
Linux Counter #281893
Faculdade Ritter dos Reis
www.ritterdosreis.br
felipes@ritterdosreis.br
Fone/Fax.: (51)32303328
I have made similar changes in my work to make DatabaseMetaData schema
aware. Could you not commit this patch unitl reviewing the schema
changes? I will submit them this weekend.
On 12 Sep 2002, Dave Cramer wrote:
> Panu,
>
> The getIndexInfo uses an outer join which won't be backward compatible?
> I did apply the getTables patch and will commit soon, can you modify the
> getIndexInfo part?
>
> Dave
> On Sun, 2002-09-08 at 14:18, Panu Outinen wrote:
> > Hi !!
> >
> > The following patch speeds up the queries in getTables() and
> > getIndexInfo(). It replaces the looping of queries with a simple join
> > operation. Patch is against the current CVS (2002-09-08).
> >
> > - Panu
> >
> > PS. Tested against PostgreSQL 7.1.2 and PostgreSQL 7.2.1 databases
> >
> > --------------------------------------------------------------------------
> >
> > --- AbstractJdbc1DatabaseMetaData.java.org Sun Sep 8 20:58:57 2002
> > +++ AbstractJdbc1DatabaseMetaData.java Sun Sep 8 21:02:58 2002
> > @@ -1836,7 +1836,9 @@
> > f[4] = new Field(connection, "REMARKS", iVarcharOid, NAME_SIZE);
> >
> > // Now form the query
> > - StringBuffer sql = new StringBuffer("select relname,oid,relkind from
> > pg_class where (");
> > + StringBuffer sql = new
> > StringBuffer(connection.haveMinimumServerVersion("7.2") ?
> > + "select relname,oid,relkind,obj_description(oid,'pg_class') from
> > pg_class where (" :
> > + "select c.relname,c.oid,c.relkind,d.description from pg_class c
> > left outer join pg_description d on (d.objoid = c.oid) where (" );
> >
> > boolean notFirst = false;
> > for (int i = 0;i < types.length;i++)
> > @@ -1863,24 +1865,6 @@
> > while (r.next())
> > {
> > byte[][] tuple = new byte[5][0];
> > -
> > - // Fetch the description for the table (if any)
> > - String getDescriptionStatement =
> > - connection.haveMinimumServerVersion("7.2") ?
> > - "select obj_description(" + r.getInt(2) + ",'pg_class')" :
> > - "select description from pg_description where objoid=" + r.getInt(2);
> > -
> > - java.sql.ResultSet dr = connection.ExecSQL(getDescriptionStatement);
> > -
> > - byte remarks[] = null;
> > -
> > - if (((AbstractJdbc1ResultSet)dr).getTupleCount() == 1)
> > - {
> > - dr.next();
> > - remarks = dr.getBytes(1);
> > - }
> > - dr.close();
> > -
> > String relKind;
> > switch (r.getBytes(3)[0])
> > {
> > @@ -1911,7 +1895,7 @@
> > tuple[1] = null; // Schema name
> > tuple[2] = r.getBytes(1); // Table name
> > tuple[3] = (relKind == null) ? null : relKind.getBytes(); // Table type
> > - tuple[4] = remarks; // Remarks
> > + tuple[4] = r.getBytes(4); //Remarks
> > v.addElement(tuple);
> > }
> > r.close();
> > @@ -3096,8 +3080,8 @@
> > "x.indkey, " +
> > "c.reltuples, " +
> > "c.relpages, " +
> > - "x.indexrelid " +
> > - "FROM pg_index x, pg_class c, pg_class i, pg_am a " +
> > + "b.attname " +
> > + "FROM pg_index x left outer join pg_attribute b on (x.indexrelid
> > = b.attrelid), pg_class c, pg_class i, pg_am a " +
> > "WHERE ((c.relname = '" + tableName.toLowerCase() + "') " +
> > " AND (c.oid = x.indrelid) " +
> > " AND (i.oid = x.indexrelid) " +
> > @@ -3118,7 +3102,6 @@
> > {
> > columnOrdinals[o++] = Integer.parseInt(stok.nextToken());
> > }
> > - java.sql.ResultSet columnNameRS = connection.ExecSQL("select a.attname
> > FROM pg_attribute a WHERE a.attrelid = " + r.getInt(9));
> > for (int i = 0; i < columnOrdinals.length; i++)
> > {
> > byte [] [] tuple = new byte [13] [];
> > @@ -3134,14 +3117,7 @@
> > Integer.toString(java.sql.DatabaseMetaData.tableIndexHashed).getBytes()
> > :
> > Integer.toString(java.sql.DatabaseMetaData.tableIndexOther).getBytes();
> > tuple[7] = Integer.toString(i + 1).getBytes();
> > - if (columnNameRS.next())
> > - {
> > - tuple[8] = columnNameRS.getBytes(1);
> > - }
> > - else
> > - {
> > - tuple[8] = "".getBytes();
> > - }
> > + tuple[8] = r.getBytes(9);
> > tuple[9] = null; // sort sequence ???
> > tuple[10] = r.getBytes(7); // inexact
> > tuple[11] = r.getBytes(8);
> >
> >
> >
> >
> >
> >
> > ---------------------------(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
> >
> >
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>