postgresql-jdbc.git / summary / log / commit / refs
commit b383f6d2c8f19e2b5b867039ca96071ba8d495e1
Author: Valentine Gogichashvili <valgog@gmail.com>
Date: Fri Apr 12 00:35:27 2013 +0000
Consider search_path when resolving type names to OIDs
In case when types with the same name existed in several schemas,
TypeInfoCache did not consider the current search_path and was choosing
an OID of a type not deterministically. These change will make
the type from the current schema to be chosen. Also this change remains
backwards compatible with the previous implementation, still being anble
to find a type, that is not included into the current search_path.
Provided test fails now, as it does not TypeInfoCache
directly. So more work is to be done to make this test work.
org/postgresql/jdbc2/TypeInfoCache.java | 25 +++++--
org/postgresql/test/TestUtil.java | 49 +++++++++++++
org/postgresql/test/jdbc2/Jdbc2TestSuite.java | 1 +
.../test/jdbc2/SearchPathLookupTest.java | 84 ++++++++++++++++++++++
4 files changed, 152 insertions(+), 7 deletions(-)
diff --git a/org/postgresql/jdbc2/TypeInfoCache.java b/org/postgresql/jdbc2/TypeInfoCache.java
index 2e797580..56591d64 100644
--- a/org/postgresql/jdbc2/TypeInfoCache.java
+++ b/org/postgresql/jdbc2/TypeInfoCache.java
@@ -185,11 +185,18 @@ public class TypeInfoCache implements TypeInfo {
// People can name their own types starting with _.
// Other types use typelem that aren't actually arrays, like box.
//
- String sql = "SELECT typinput='array_in'::regproc, typtype FROM ";
- if (_conn.haveMinimumServerVersion("7.3")) {
- sql += "pg_catalog.";
+ String sql;
+ if (_conn.haveMinimumServerVersion("8.0")) {
+ // in case of multiple records (in different schemas) choose the one from the current schema,
+ // otherwise take the last version of a type that is at least more deterministic then before
+ // (keeping old behaviour of finding types, that should not be found without correct search path)
+ sql = "SELECT typinput='array_in'::regproc, typtype FROM pg_catalog.pg_type WHERE typname = ? " +
+ "ORDER BY ( select nspname = current_schema() from pg_namespace as ns where ns.oid = typnamespace ) DESC, oid DESC LIMIT 1";
+ } else if (_conn.haveMinimumServerVersion("7.3")) {
+ sql = "SELECT typinput='array_in'::regproc, typtype FROM pg_catalog.pg_type WHERE typname = ? ORDER BY oid DESC LIMIT 1";
+ } else {
+ sql = "SELECT typinput='array_in'::regproc, typtype FROM pg_type WHERE typname = ? LIMIT 1";
}
- sql += "pg_type WHERE typname = ?";
_getTypeInfoStatement = _conn.prepareStatement(sql);
}
@@ -233,10 +240,14 @@ public class TypeInfoCache implements TypeInfo {
if (_getOidStatement == null) {
String sql;
- if (_conn.haveMinimumServerVersion("7.3")) {
- sql = "SELECT oid FROM pg_catalog.pg_type WHERE typname = ?";
+ if (_conn.haveMinimumServerVersion("8.0")) {
+ // see comments in @getSQLType()
+ sql = "SELECT oid FROM pg_catalog.pg_type WHERE typname = ? " +
+ "ORDER BY ( select nspname = current_schema() from pg_namespace as ns where ns.oid = typnamespace ) DESC, oid DESC LIMIT 1";
+ } else if (_conn.haveMinimumServerVersion("7.3")) {
+ sql = "SELECT oid FROM pg_catalog.pg_type WHERE typname = ? ORDER BY oid DESC LIMIT 1";
} else {
- sql = "SELECT oid FROM pg_type WHERE typname = ?";
+ sql = "SELECT oid FROM pg_type WHERE typname = ? ORDER BY oid DESC LIMIT 1";
}
_getOidStatement = _conn.prepareStatement(sql);
diff --git a/org/postgresql/test/TestUtil.java b/org/postgresql/test/TestUtil.java
index 7c6c6121..2ef7ba60 100644
--- a/org/postgresql/test/TestUtil.java
+++ b/org/postgresql/test/TestUtil.java
@@ -196,6 +196,55 @@ public class TestUtil
con.close();
}
+ /*
+ * Helper - creates a test schema for use by a test
+ */
+ public static void createSchema(Connection con,
+ String schema) throws SQLException
+ {
+ Statement st = con.createStatement();
+ try
+ {
+ // Drop the schema
+ dropSchema(con, schema);
+
+ // Now create the schema
+ String sql = "CREATE SCHEMA " + schema;
+
+ st.executeUpdate(sql);
+ }
+ finally
+ {
+ st.close();
+ }
+ }
+
+ /*
+ * Helper - drops a schema
+ */
+ public static void dropSchema(Connection con, String schema) throws SQLException
+ {
+ Statement stmt = con.createStatement();
+ try
+ {
+ String sql = "DROP SCHEMA " + schema;
+ if (haveMinimumServerVersion(con, "7.3"))
+ {
+ sql += " CASCADE ";
+ }
+ stmt.executeUpdate(sql);
+ }
+ catch (SQLException ex)
+ {
+ // Since every create schema issues a drop schema
+ // it's easy to get a schema doesn't exist error.
+ // we want to ignore these, but if we're in a
+ // transaction then we've got trouble
+ if (!con.getAutoCommit())
+ throw ex;
+ }
+ }
+
/*
* Helper - creates a test table for use by a test
*/
diff --git a/org/postgresql/test/jdbc2/Jdbc2TestSuite.java b/org/postgresql/test/jdbc2/Jdbc2TestSuite.java
index d87591b0..699aaab7 100644
--- a/org/postgresql/test/jdbc2/Jdbc2TestSuite.java
+++ b/org/postgresql/test/jdbc2/Jdbc2TestSuite.java
@@ -40,6 +40,7 @@ public class Jdbc2TestSuite extends TestSuite
suite.addTestSuite(ConnectionTest.class);
suite.addTestSuite(DatabaseMetaDataTest.class);
suite.addTestSuite(DatabaseMetaDataPropertiesTest.class);
+ suite.addTestSuite(SearchPathLookupTest.class);
suite.addTestSuite(EncodingTest.class);
// Connectivity/Protocols
diff --git a/org/postgresql/test/jdbc2/SearchPathLookupTest.java b/org/postgresql/test/jdbc2/SearchPathLookupTest.java
new file mode 100644
index 00000000..e440c19e
--- /dev/null
+++ b/org/postgresql/test/jdbc2/SearchPathLookupTest.java
@@ -0,0 +1,84 @@
+/*-------------------------------------------------------------------------
+*
+* Copyright (c) 2004-2013, PostgreSQL Global Development Group
+*
+*
+*-------------------------------------------------------------------------
+*/
+package org.postgresql.test.jdbc2;
+
+import org.postgresql.test.TestUtil;
+import junit.framework.TestCase;
+import java.sql.*;
+
+/*
+ * TestCase to test the internal functionality of org.postgresql.jdbc2.DatabaseMetaData
+ *
+ */
+
+public class SearchPathLookupTest extends TestCase
+{
+
+ private Connection con;
+ /*
+ * Constructor
+ */
+ public SearchPathLookupTest(String name)
+ {
+ super(name);
+ }
+
+ public void testSearchPathNormalLookup() throws Exception
+ {
+ con = TestUtil.openDB();
+ Statement stmt = con.createStatement();
+ try {
+ TestUtil.createSchema( con, "first_schema" );
+ TestUtil.createTable( con, "first_schema.x", "first_schema_field_n int4");
+ TestUtil.createSchema( con, "second_schema" );
+ TestUtil.createTable( con, "second_schema.x", "second_schema_field_n text");
+ TestUtil.createSchema( con, "third_schema" );
+ TestUtil.createTable( con, "third_schema.x", "third_schema_field_n float");
+ TestUtil.createSchema( con, "last_schema" );
+ TestUtil.createTable( con, "last_schema.x", "last_schema_field_n text");
+ stmt.execute("SET search_path TO third_schema;");
+ DatabaseMetaData dbmd = con.getMetaData();
+ ResultSet rs = dbmd.getColumns("", "", "x", "");
+ assertTrue(rs.next());
+ assertEquals("third_schema_field_n", rs.getString("COLUMN_NAME"));
+ assertTrue(!rs.next());
+ TestUtil.dropSchema( con, "first_schema" );
+ TestUtil.dropSchema( con, "second_schema" );
+ TestUtil.dropSchema( con, "third_schema" );
+ TestUtil.dropSchema( con, "last_schema" );
+ } finally {
+ if ( stmt != null ) stmt.close();
+ TestUtil.closeDB( con );
+ }
+ }
+
+ /* -- TODO: make this test work
+ public void testSearchPathBackwardsCompatibleLookup() throws Exception
+ {
+ con = TestUtil.openDB();
+ try {
+ TestUtil.createSchema( con, "first_schema" );
+ TestUtil.createTable( con, "first_schema.x", "first_schema_field int4");
+ TestUtil.createSchema( con, "second_schema" );
+ TestUtil.createTable( con, "second_schema.x", "second_schema_field text");
+ try {
+ DatabaseMetaData dbmd = con.getMetaData();
+ ResultSet rs = dbmd.getColumns("", "", "x", "");
+ assertTrue(rs.next());
+ assertEquals("second_schema_field", rs.getString("COLUMN_NAME"));
+ assertTrue(!rs.next());
+ } finally {
+ TestUtil.dropSchema( con, "first_schema" );
+ TestUtil.dropSchema( con, "second_schema" );
+ }
+ } finally {
+ TestUtil.closeDB( con );
+ }
+ }
+ */
+}
[parent: 471a09ee31f9]