Thread: DatabaseMetaData.getTables problem
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 ";
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();
}
}
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
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>
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
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
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
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) >
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) >
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
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