Re: Speedup patch for getTables() and getIndexInfo()

From: Panu Outinen <panu(at)vertex(dot)fi>
To: Dave Cramer <Dave(at)micro-automation(dot)net>
Cc: "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>, Barry Lind <barry(at)xythos(dot)com>
Subject: Re: Speedup patch for getTables() and getIndexInfo()
Date: 2002-09-12 14:53:14
Message-ID: 5.1.0.14.0.20020912172941.039aa220@vertex.fi
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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(at)postgresql(dot)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(at)postgresql(dot)org

In response to

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Barry Lind 2002-09-12 16:51:53 What releases should the jdbc driver support? Was: Speedup patch for getTables() and getIndexInfo()
Previous Message Tim McAuley 2002-09-12 14:43:54 Is getProcedureColumns working for pgjdbc2.jar?