Обсуждение: Bug with DatabaseMetaData and temporary tables/schemas
Hi,
DatabaseMetaData does not correctly report the implicitely created temporary schema when creating a temporary table.
I ran the following code using a 9.0.1 database on Windows XP, with the postgresql-9.0-801.jdbc4.jar driver:
rs = con.getMetaData().getSchemas();
System.out.println("before create table: ");
while (rs.next())
{
System.out.println("schema: " + rs.getString(1));
}
rs.close();
stmt = con.createStatement();
stmt.execute("create temporary table foo (id integer)");
System.out.println("after create table: ");
rs = con.getMetaData().getSchemas();
while (rs.next())
{
System.out.println("schema: " + rs.getString(1));
}
rs.close();
rs = stmt.executeQuery("select nspname from pg_namespace");
while (rs.next())
{
System.out.println("schema from pg_namespace: " + rs.getString(1));
}
rs.close();
On an otherwise empty database this produces the following output:
before create table:
schema: information_schema
schema: pg_catalog
schema: pg_toast_temp_1
schema: pg_toast_temp_2
schema: pg_toast_temp_3
schema: public
after create table:
schema: information_schema
schema: pg_catalog
schema: pg_toast_temp_1
schema: pg_toast_temp_2
schema: pg_toast_temp_3
schema: public
schema from pg_namespace: pg_toast
schema from pg_namespace: pg_temp_1
schema from pg_namespace: pg_toast_temp_1
schema from pg_namespace: pg_catalog
schema from pg_namespace: information_schema
schema from pg_namespace: public
schema from pg_namespace: pg_temp_2
schema from pg_namespace: pg_toast_temp_2
schema from pg_namespace: pg_temp_3
schema from pg_namespace: pg_toast_temp_3
So the pg_temp_X schema(s) are there, but getSchemas() does not return them.
Am I missing something, or is this a bug in the driver?
Regards
Thomas
On Fri, 5 Nov 2010, Thomas Kellerer wrote: > DatabaseMetaData does not correctly report the implicitely created temporary > schema when creating a temporary table. getSchemas is trying to help by not showing you tables that you cannot access in other backends' temp schemas. It's not quite smart enough though. It's not filtering the toast schemas and it isn't allowing you to see your own temp schema. It looks like psql will show you your own temp schema and all of the temp toast schemas. So that could potentially be improved as well. I'll take a look at making getSchemas a little more consistent. Kris Jurka
Kris Jurka <books@ejurka.com> writes: > It looks like psql will show you your own temp schema and all of the > temp toast schemas. So that could potentially be improved as well. Yeah, there was some discussion of that just recently: http://archives.postgresql.org/message-id/16710.1284837096@sss.pgh.pa.us I haven't got round to making that change yet, but maybe now is a good time. The consensus seemed to be that \dn (without S) should not show any pg_XXX schemas, nor information_schema. Not sure that that's appropriate for getSchemas though --- an API intended for programs should maybe just hide the temp and toast schemas. regards, tom lane
Tom Lane wrote on 07.11.2010 02:02: > Kris Jurka<books@ejurka.com> writes: >> It looks like psql will show you your own temp schema and all of the >> temp toast schemas. So that could potentially be improved as well. > > Yeah, there was some discussion of that just recently: > http://archives.postgresql.org/message-id/16710.1284837096@sss.pgh.pa.us > > I haven't got round to making that change yet, but maybe now is a > good time. The consensus seemed to be that \dn (without S) should > not show any pg_XXX schemas, nor information_schema. Not sure that > that's appropriate for getSchemas though --- an API intended for > programs should maybe just hide the temp and toast schemas. > Well it should at least show "my" temp schemas. Or getColumns() and getTables() should be smart enough to return information about temp tables without having to specifythe temp schema. Regards Thomas
On Sun, 7 Nov 2010, Thomas Kellerer wrote: > Tom Lane wrote on 07.11.2010 02:02: >> Kris Jurka<books@ejurka.com> writes: >>> It looks like psql will show you your own temp schema and all of the >>> temp toast schemas. So that could potentially be improved as well. >> >> Yeah, there was some discussion of that just recently: >> http://archives.postgresql.org/message-id/16710.1284837096@sss.pgh.pa.us >> >> I haven't got round to making that change yet, but maybe now is a >> good time. The consensus seemed to be that \dn (without S) should >> not show any pg_XXX schemas, nor information_schema. Not sure that >> that's appropriate for getSchemas though --- an API intended for >> programs should maybe just hide the temp and toast schemas. >> > > Well it should at least show "my" temp schemas. I've applied a patch to fix this. The driver will now show your own temp schema and toast temp schema, but no other temp schemas (toast or otherwise). > Or getColumns() and getTables() should be smart enough to return information > about temp tables without having to specify the temp schema. I'm not sure what you are referring to here. You can certainly leave the schema blank and it should work. Kris Jurka
Kris Jurka wrote on 22.12.2010 18:01: > I've applied a patch to fix this. The driver will now show your own > temp schema and toast temp schema, but no other temp schemas (toast > or otherwise). Great! Thanks a lot >> Or getColumns() and getTables() should be smart enough to return >> information about temp tables without having to specify the temp >> schema. > > I'm not sure what you are referring to here. You can certainly leave > the schema blank and it should work. You are right it does when leaving the schema blank Regards Thomas