Thread: DatabaseMetaData.getTables problem

DatabaseMetaData.getTables problem

From
snpe
Date:
Hello,
  I am playing with DatabaseMetaData.getTables
I call getTables(null,null,"TABELA",null)

ResultSet get (number of column of TABELA)+ 1 rows

Patch for this problem is :
---
/u2/postgrescvs/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java
2002-11-04 12:03:04.000000000 +0000
+++ org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java    2002-11-07
22:11:11.000000000 +0000
@@ -1988,7 +1988,7 @@
             " END "+
             " AS TABLE_TYPE, d.description AS REMARKS "+
             " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c "+
-            " LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid) "+
+            " LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid and
d.objsubid = 0) "+
             " LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND
dc.relname='pg_class') "+
             " LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND
dn.nspname='pg_catalog') "+
             " WHERE c.relnamespace = n.oid ";


possible memory leak??

From
Dave Cramer
Date:
If I run the following program for an extended period of time the
memory consumed by java keeps increasing 4M at a time. Can anyone else
confirm this, and next; can anyone explain why? Is this normal? I doubt it, but
can't confirm it?

Does anyone have access to an optimizer to run this on?


--
Dave Cramer <Dave@micro-automation.net>
import java.lang.Runnable;
/**
 * <p>Title: </p>
 * <p>Description: </p>
 * <p>Copyright: Copyright (c) 2002</p>
 * <p>Company: </p>
 * @author unascribed
 * @version 1.0
 */

public class TestRoutes implements Runnable
{
  Connection con;

  public TestRoutes()
  {

    try
    {
      Class.forName("org.postgresql.Driver");
      con = DriverManager.getConnection("jdbc:postgresql://localhost/symcor", "davec", "" );
    }
    catch ( Exception ex)
    {
      ex.printStackTrace();
    }

  }
  public void selectRoutes()
  {
    Statement stmt = null;
    ResultSet rs = null;
    try
    {
      stmt = con.createStatement();
      rs = stmt.executeQuery("select * from route");
      for( int i=0; rs.next(); i++ )
      {
    System.out.println( "Route " + i );
      }
    }
    catch ( Exception ex )
    {
      ex.printStackTrace();
    }
    finally
    {
      try
      {
    rs.close();
    stmt.close();
    rs=null;
    stmt=null;
      }
      catch (SQLException ex)
      {
      }
    }

  }
  public void run()
  {

    while(true)
    {
      try
      {
        //RouteModel.getAllRoutes();
    selectRoutes();
        Thread.sleep(2000);
      }
      catch( Exception ex )
      {
    return;
      }
    }
  }
  public static void main( String [] args )
  {
    TestRoutes t = new TestRoutes();
    new Thread(t).start();
  }
}


Re: possible memory leak??

From
"Michael Paesold"
Date:
Dave Cramer <Dave@micro-automation.net> wrote:

> If I run the following program for an extended period of time the
> memory consumed by java keeps increasing 4M at a time. Can anyone else
> confirm this, and next; can anyone explain why? Is this normal? I doubt
it, but
> can't confirm it?
>
> Does anyone have access to an optimizer to run this on?

Have you tried to run System.gc() at the end of each loop?

    selectRoutes();
    System.gc();
    Thread.sleep(2000);
    ...

Perhaps the JVM just isn't fast enough with the garbage collection.

Regards,
Michael Paesold


Re: possible memory leak??

From
Dave Cramer
Date:
Should the gc run before an OutOfMemoryException is thrown

in other words are the jvm's smart enough to run the gc if they are out
of memory?

Dave

On Fri, 2002-11-08 at 08:31, Michael Paesold wrote:
> Dave Cramer <Dave@micro-automation.net> wrote:
>
> > If I run the following program for an extended period of time the
> > memory consumed by java keeps increasing 4M at a time. Can anyone else
> > confirm this, and next; can anyone explain why? Is this normal? I doubt
> it, but
> > can't confirm it?
> >
> > Does anyone have access to an optimizer to run this on?
>
> Have you tried to run System.gc() at the end of each loop?
>
>     selectRoutes();
>     System.gc();
>     Thread.sleep(2000);
>     ...
>
> Perhaps the JVM just isn't fast enough with the garbage collection.
>
> Regards,
> Michael Paesold
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
Dave Cramer <Dave@micro-automation.net>


Re: possible memory leak??

From
"Michael Paesold"
Date:
Dave Cramer <Dave@micro-automation.net> wrote:

> Should the gc run before an OutOfMemoryException is thrown
>
> in other words are the jvm's smart enough to run the gc if they are out
> of memory?

Yes, the jvm should be smart enough to regain unused memory before throwing
an OutOfMemoryException. The global gc runs in the backround in defined
intervals, but more often in low memory situations, as far as I know.

It is possible to have "memory leaks" in java, but I don't see it in your
code. Such a "leak" could be created by adding all result sets to a vector
without ever removing it.

Does the overall memory consumption increase, even with System.gc() called
explicitly? Have you tried running it until the jvm needs more than maximum
allowed memory? (the limit can be set with command line arguments)

Regards,
Michael Paesold


Re: possible memory leak??

From
Mike Beachy
Date:
On Fri, Nov 08, 2002 at 08:11:45AM -0500, Dave Cramer wrote:
> If I run the following program for an extended period of time the
> memory consumed by java keeps increasing 4M at a time. Can anyone else
> confirm this, and next; can anyone explain why? Is this normal? I doubt
> it, but can't confirm it?

What is "an extended period of time"? I've been running for about 45
minutes, and see nothing like this (judging simply by ps output). My JDK is
Sun's 1.3.1 on x86 Linux. My 'route' table has 4 rows.

mike

Re: possible memory leak??

From
Jens Carlberg
Date:
Michael Paesold wrote:
> Dave Cramer <Dave@micro-automation.net> wrote:
>
>
>>Should the gc run before an OutOfMemoryException is thrown
>>
>>in other words are the jvm's smart enough to run the gc if they are out
>>of memory?
>
>
> Yes, the jvm should be smart enough to regain unused memory before throwing
> an OutOfMemoryException. The global gc runs in the backround in defined
> intervals, but more often in low memory situations, as far as I know.

 From the documentation of the OutOfMemoryError:

"Thrown when the Java Virtual Machine cannot allocate an object because
it is out of memory, and no more memory could be made available by the
garbage collector. "

Thus, the VM tries to get the memory needed through a garbage collection
before it gives up and throws an error.

<snip>
> Does the overall memory consumption increase, even with System.gc() called
> explicitly? Have you tried running it until the jvm needs more than maximum
> allowed memory? (the limit can be set with command line arguments)

Also, add the flag -verbose:gc to java to see the actual garbage
collection taking place. If you combine that with adding a System.gc()
you'll probably end up with enough debug info.

--
       _/ _/_/_/ _/   _/ _/_/_/ | Jens Carlberg
      _/ _/     _/_/ _/  _/     | jenca@lysator.liu.se
     _/ _/_/   _/ _/_/    _/    | +46 13 260872
_/_/  _/_/_/ _/   _/ _/_/_/    | ICQ: 44194995


Re: DatabaseMetaData.getTables problem

From
Barry Lind
Date:
Can you also submit a test case?  I would like to add a regression test
that tests for the problem being fixed here. (which I don't understand
entirely)

thanks,
--Barry


snpe wrote:
> Hello,
>   I am playing with DatabaseMetaData.getTables
> I call getTables(null,null,"TABELA",null)
>
> ResultSet get (number of column of TABELA)+ 1 rows
>
> Patch for this problem is :
> ---
> /u2/postgrescvs/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java
> 2002-11-04 12:03:04.000000000 +0000
> +++ org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java    2002-11-07
> 22:11:11.000000000 +0000
> @@ -1988,7 +1988,7 @@
>              " END "+
>              " AS TABLE_TYPE, d.description AS REMARKS "+
>              " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c "+
> -            " LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid) "+
> +            " LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid and
> d.objsubid = 0) "+
>              " LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND
> dc.relname='pg_class') "+
>              " LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND
> dn.nspname='pg_catalog') "+
>              " WHERE c.relnamespace = n.oid ";
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>




Re: DatabaseMetaData.getTables problem

From
Kris Jurka
Date:
The problem is that the join to retrieve the table's comment from
pg_description is not properly constrained.  A table's comment is
stored in pg_description with the objoid of the table's pg_class
oid and objsubid = 0.  A table's column's comments are stored with the
table's pg_class oid and objsubid of pg_attribute.attnum.  If any column
in the table has a comment then the query will not work properly
(retrieving extra rows).

Kris Jurka

On Fri, 8 Nov 2002, Barry Lind wrote:

> Can you also submit a test case?  I would like to add a regression test
> that tests for the problem being fixed here. (which I don't understand
> entirely)
>
> thanks,
> --Barry
>
>
> snpe wrote:
> > Hello,
> >   I am playing with DatabaseMetaData.getTables
> > I call getTables(null,null,"TABELA",null)
> >
> > ResultSet get (number of column of TABELA)+ 1 rows
> >
> > Patch for this problem is :
> > ---
> > /u2/postgrescvs/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java
> > 2002-11-04 12:03:04.000000000 +0000
> > +++ org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java    2002-11-07
> > 22:11:11.000000000 +0000
> > @@ -1988,7 +1988,7 @@
> >              " END "+
> >              " AS TABLE_TYPE, d.description AS REMARKS "+
> >              " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c "+
> > -            " LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid) "+
> > +            " LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid and
> > d.objsubid = 0) "+
> >              " LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND
> > dc.relname='pg_class') "+
> >              " LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace AND
> > dn.nspname='pg_catalog') "+
> >              " WHERE c.relnamespace = n.oid ";
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" 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)
>


Re: DatabaseMetaData.getTables problem

From
Kris Jurka
Date:
Giving it another look this change needs to be made in two places for the
schema and non-schema aware code paths.  Patch attached.

Kris Jurka


On Fri, 8 Nov 2002, Kris Jurka wrote:

>
> The problem is that the join to retrieve the table's comment from
> pg_description is not properly constrained.  A table's comment is
> stored in pg_description with the objoid of the table's pg_class
> oid and objsubid = 0.  A table's column's comments are stored with the
> table's pg_class oid and objsubid of pg_attribute.attnum.  If any column
> in the table has a comment then the query will not work properly
> (retrieving extra rows).
>
> Kris Jurka
>

Attachment

Re: DatabaseMetaData.getTables problem

From
snpe
Date:
Kris explain good.
I don't know that you need regression test, but this is command :

Connection con = DriverManager.getConnection (....);
DatabaseMetaData db = con.getMetaData();
ResultSet rs =db.getTables(null,null,"any_table",null);

If ResultSet get one row for catalog,shema that is ok.

regards
Haris Peco

On Friday 08 November 2002 06:34 pm, Kris Jurka wrote:
> The problem is that the join to retrieve the table's comment from
> pg_description is not properly constrained.  A table's comment is
> stored in pg_description with the objoid of the table's pg_class
> oid and objsubid = 0.  A table's column's comments are stored with the
> table's pg_class oid and objsubid of pg_attribute.attnum.  If any column
> in the table has a comment then the query will not work properly
> (retrieving extra rows).
>
> Kris Jurka
>
> On Fri, 8 Nov 2002, Barry Lind wrote:
> > Can you also submit a test case?  I would like to add a regression test
> > that tests for the problem being fixed here. (which I don't understand
> > entirely)
> >
> > thanks,
> > --Barry
> >
> > snpe wrote:
> > > Hello,
> > >   I am playing with DatabaseMetaData.getTables
> > > I call getTables(null,null,"TABELA",null)
> > >
> > > ResultSet get (number of column of TABELA)+ 1 rows
> > >
> > > Patch for this problem is :
> > > ---
> > > /u2/postgrescvs/pgsql/src/interfaces/jdbc/org/postgresql/jdbc1/Abstract
> > >Jdbc1DatabaseMetaData.java 2002-11-04 12:03:04.000000000 +0000
> > > +++ org/postgresql/jdbc1/AbstractJdbc1DatabaseMetaData.java    2002-11-07
> > > 22:11:11.000000000 +0000
> > > @@ -1988,7 +1988,7 @@
> > >              " END "+
> > >              " AS TABLE_TYPE, d.description AS REMARKS "+
> > >              " FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c "+
> > > -            " LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid) "+
> > > +            " LEFT JOIN pg_catalog.pg_description d ON (c.oid = d.objoid and
> > > d.objsubid = 0) "+
> > >              " LEFT JOIN pg_catalog.pg_class dc ON (d.classoid=dc.oid AND
> > > dc.relname='pg_class') "+
> > >              " LEFT JOIN pg_catalog.pg_namespace dn ON (dn.oid=dc.relnamespace
> > > AND dn.nspname='pg_catalog') "+
> > >              " WHERE c.relnamespace = n.oid ";
> > >
> > >
> > > ---------------------------(end of
> > > broadcast)--------------------------- TIP 2: you can get off all lists
> > > at once with the unregister command (send "unregister
> > > YourEmailAddressHere" 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)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org