[MonetDB-users] Problems With CREATE TEMPORARY TABLE
Hi all,
I am having trouble using MonetDB, with dynamic temporary table creation. I have included a simple unit test to demonstrate the problem. These tests work fine on other JDBC SQL servers, but fail with the below exceptions when using MonetDB.
Any ideas on how to make new tables available to other connections or keep new tables from interrupting other jdbc statements?
Thanks, James ---%<--- java.sql.SQLException: INSERT INTO: no such table 'paints_2' at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2058) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:1808) at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:371) at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:340) at nl.cwi.monetdb.jdbc.MonetPreparedStatement.<init>(MonetPreparedStatement.java:102) at nl.cwi.monetdb.jdbc.MonetConnection.prepareStatement(MonetConnection.java:625) at nl.cwi.monetdb.jdbc.MonetConnection.prepareStatement(MonetConnection.java:586) at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:248) at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:302) at MonetDBJdbcTest.testDualCreateTable(MonetDBJdbcTest.java:28) ---%<--- java.sql.SQLException: Query did not produce a result set at nl.cwi.monetdb.jdbc.MonetPreparedStatement.executeQuery(MonetPreparedStatement.java:228) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93) at MonetDBJdbcTest.testSelectWhileCreateTable(MonetDBJdbcTest.java:77) ---%<--- import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement;
import junit.framework.TestCase;
import org.apache.commons.dbcp.BasicDataSource;
public class MonetDBJdbcTest extends TestCase {
private BasicDataSource ds1;
public void testDualCreateTable() throws Exception { Connection c4 = ds1.getConnection(); c4.setAutoCommit(true); Connection c76 = ds1.getConnection(); c76.setAutoCommit(false); Statement s93 = c76.createStatement(); s93.execute("CREATE TEMPORARY TABLE TRANSACTION_STATEMENTS (\n ctx INTEGER NOT NULL,\n subj INTEGER NOT NULL,\n pred INTEGER NOT NULL,\n obj INTEGER NOT NULL,\n expl BOOL NOT NULL\n)"); s93.close(); Statement s96 = c4.createStatement(); s96.execute("CREATE TABLE paints_2 (\n ctx INTEGER NOT NULL,\n subj INTEGER NOT NULL,\n obj INTEGER NOT NULL,\n expl BOOL NOT NULL\n)"); s96.close(); PreparedStatement ps102 = c76.prepareStatement("INSERT INTO paints_2\nSELECT DISTINCT ctx, subj, obj, expl FROM TRANSACTION_STATEMENTS tr\nWHERE NOT EXISTS (SELECT * FROM paints_2 st\nWHERE st.ctx = tr.ctx AND st.subj = tr.subj AND st.obj = tr.obj AND st.expl = tr.expl)"); ps102.close(); c76.close(); c4.close(); }
public void testSelectWhileCreateTable() throws Exception { Connection c2 = ds1.getConnection(); c2.setAutoCommit(true); Statement s7 = c2.createStatement(); s7.execute("CREATE TABLE HASH_VALUES (\n id INTEGER NOT NULL,\n value BIGINT NOT NULL\n)"); s7.close(); PreparedStatement ps72 = c2.prepareStatement("SELECT id, value\nFROM HASH_VALUES\nWHERE value IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); ps72.setLong(1, 157585763729791056l); ps72.setLong(2, 1105705122070497121l); ps72.setLong(3, 940939309403574879l); ps72.setLong(4, 44738479624713631l); ps72.setLong(5, 93693228172326007l); ps72.setLong(6, 909856949769965718l); ps72.setLong(7, 388219017765868455l); ps72.setLong(8, 498326321060763331l); ps72.setLong(9, 917168269028472831l); ps72.setLong(10, 560146884310966732l); ps72.setLong(11, 178986943741094305l); ps72.setNull(12, -5); ps72.setNull(13, -5); ps72.setNull(14, -5); ps72.setNull(15, -5); ResultSet rs73 = ps72.executeQuery(); rs73.next(); rs73.close(); Statement s76 = c2.createStatement(); s76.execute("CREATE TABLE TRIPLES (\n ctx INTEGER NOT NULL,\n subj INTEGER NOT NULL,\n pred INTEGER NOT NULL,\n obj INTEGER NOT NULL,\n expl BOOL NOT NULL\n)"); s76.close(); ps72.setLong(1, 157585763729791056l); ps72.setLong(2, 93693228172326007l); ps72.setLong(3, 44738479624713631l); ps72.setLong(4, 909856949769965718l); ps72.setLong(5, 388219017765868455l); ps72.setLong(6, 917168269028472831l); ps72.setNull(7, -5); ps72.setNull(8, -5); ps72.setNull(9, -5); ps72.setNull(10, -5); ps72.setNull(11, -5); ps72.setNull(12, -5); ps72.setNull(13, -5); ps72.setNull(14, -5); ps72.setNull(15, -5); ResultSet rs88 = ps72.executeQuery(); rs88.close(); ps72.close(); c2.close(); }
@Override protected void setUp() throws Exception { Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); ds1 = new BasicDataSource(); ds1.setUrl("jdbc:monetdb://localhost/jdbc_test"); ds1.setUsername("monetdb"); ds1.setPassword("monetdb"); }
@Override protected void tearDown() throws Exception { Connection c106 = ds1.getConnection(); Statement s107 = c106.createStatement(); try { s107.execute("DROP TABLE HASH_VALUES"); s107.execute("DROP TABLE TRIPLES"); } catch (SQLException exc) { // ignore } try { s107.execute("DROP TABLE TRANSACTION_STATEMENTS"); s107.execute("DROP TABLE paints_2"); } catch (SQLException exc) { // ignore } s107.close(); c106.close(); ds1.close(); }
}
On Tue, Jul 15, 2008 at 03:23:15PM -0400, James Leigh wrote:
Hi all,
I am having trouble using MonetDB, with dynamic temporary table creation. I have included a simple unit test to demonstrate the problem. These tests work fine on other JDBC SQL servers, but fail with the below exceptions when using MonetDB.
Any ideas on how to make new tables available to other connections or keep new tables from interrupting other jdbc statements?
Thanks, James ---%<--- java.sql.SQLException: INSERT INTO: no such table 'paints_2' at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.executeQuery(MonetConnection.java:2058) at nl.cwi.monetdb.jdbc.MonetConnection$ResponseList.processQuery(MonetConnection.java:1808) at nl.cwi.monetdb.jdbc.MonetStatement.internalExecute(MonetStatement.java:371) at nl.cwi.monetdb.jdbc.MonetStatement.execute(MonetStatement.java:340) at nl.cwi.monetdb.jdbc.MonetPreparedStatement.<init>(MonetPreparedStatement.java:102) at nl.cwi.monetdb.jdbc.MonetConnection.prepareStatement(MonetConnection.java:625) at nl.cwi.monetdb.jdbc.MonetConnection.prepareStatement(MonetConnection.java:586) at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:248) at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:302) at MonetDBJdbcTest.testDualCreateTable(MonetDBJdbcTest.java:28) ---%<--- java.sql.SQLException: Query did not produce a result set at nl.cwi.monetdb.jdbc.MonetPreparedStatement.executeQuery(MonetPreparedStatement.java:228) at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93) at MonetDBJdbcTest.testSelectWhileCreateTable(MonetDBJdbcTest.java:77) ---%<--- import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement;
import junit.framework.TestCase;
import org.apache.commons.dbcp.BasicDataSource;
public class MonetDBJdbcTest extends TestCase {
private BasicDataSource ds1;
public void testDualCreateTable() throws Exception { Connection c4 = ds1.getConnection(); c4.setAutoCommit(true); Connection c76 = ds1.getConnection(); c76.setAutoCommit(false); Statement s93 = c76.createStatement(); s93.execute("CREATE TEMPORARY TABLE TRANSACTION_STATEMENTS (\n ctx INTEGER NOT NULL,\n subj INTEGER NOT NULL,\n pred INTEGER NOT NULL,\n obj INTEGER NOT NULL,\n expl BOOL NOT NULL\n)");
Here Your 'c76' transaction is started.
s93.close(); Statement s96 = c4.createStatement(); s96.execute("CREATE TABLE paints_2 (\n ctx INTEGER NOT NULL,\n subj INTEGER NOT NULL,\n obj INTEGER NOT NULL,\n expl BOOL NOT NULL\n)"); s96.close();
This transaction is auto-commited but afcourse (correctly) not visiable by your c76 transaction.
PreparedStatement ps102 = c76.prepareStatement("INSERT INTO paints_2\nSELECT DISTINCT ctx, subj, obj, expl FROM TRANSACTION_STATEMENTS tr\nWHERE NOT EXISTS (SELECT * FROM paints_2 st\nWHERE st.ctx = tr.ctx AND st.subj = tr.subj AND st.obj = tr.obj AND st.expl = tr.expl)");
So this statement will give an error as it doesn't know about paints_2.
A simple change of order of you java statements should fix this problem.
Niels
ps102.close(); c76.close(); c4.close();}
public void testSelectWhileCreateTable() throws Exception { Connection c2 = ds1.getConnection(); c2.setAutoCommit(true); Statement s7 = c2.createStatement(); s7.execute("CREATE TABLE HASH_VALUES (\n id INTEGER NOT NULL,\n value BIGINT NOT NULL\n)"); s7.close(); PreparedStatement ps72 = c2.prepareStatement("SELECT id, value\nFROM HASH_VALUES\nWHERE value IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); ps72.setLong(1, 157585763729791056l); ps72.setLong(2, 1105705122070497121l); ps72.setLong(3, 940939309403574879l); ps72.setLong(4, 44738479624713631l); ps72.setLong(5, 93693228172326007l); ps72.setLong(6, 909856949769965718l); ps72.setLong(7, 388219017765868455l); ps72.setLong(8, 498326321060763331l); ps72.setLong(9, 917168269028472831l); ps72.setLong(10, 560146884310966732l); ps72.setLong(11, 178986943741094305l); ps72.setNull(12, -5); ps72.setNull(13, -5); ps72.setNull(14, -5); ps72.setNull(15, -5); ResultSet rs73 = ps72.executeQuery(); rs73.next(); rs73.close(); Statement s76 = c2.createStatement(); s76.execute("CREATE TABLE TRIPLES (\n ctx INTEGER NOT NULL,\n subj INTEGER NOT NULL,\n pred INTEGER NOT NULL,\n obj INTEGER NOT NULL,\n expl BOOL NOT NULL\n)"); s76.close(); ps72.setLong(1, 157585763729791056l); ps72.setLong(2, 93693228172326007l); ps72.setLong(3, 44738479624713631l); ps72.setLong(4, 909856949769965718l); ps72.setLong(5, 388219017765868455l); ps72.setLong(6, 917168269028472831l); ps72.setNull(7, -5); ps72.setNull(8, -5); ps72.setNull(9, -5); ps72.setNull(10, -5); ps72.setNull(11, -5); ps72.setNull(12, -5); ps72.setNull(13, -5); ps72.setNull(14, -5); ps72.setNull(15, -5); ResultSet rs88 = ps72.executeQuery(); rs88.close(); ps72.close(); c2.close(); }
@Override protected void setUp() throws Exception { Class.forName("nl.cwi.monetdb.jdbc.MonetDriver"); ds1 = new BasicDataSource(); ds1.setUrl("jdbc:monetdb://localhost/jdbc_test"); ds1.setUsername("monetdb"); ds1.setPassword("monetdb"); }
@Override protected void tearDown() throws Exception { Connection c106 = ds1.getConnection(); Statement s107 = c106.createStatement(); try { s107.execute("DROP TABLE HASH_VALUES"); s107.execute("DROP TABLE TRIPLES"); } catch (SQLException exc) { // ignore } try { s107.execute("DROP TABLE TRANSACTION_STATEMENTS"); s107.execute("DROP TABLE paints_2"); } catch (SQLException exc) { // ignore } s107.close(); c106.close(); ds1.close(); }
}
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge Build the coolest Linux based applications with Moblin SDK & win great prizes Grand prize is a trip for two to an Open Source event anywhere in the world http://moblin-contest.org/redirect.php?banner_id=100&url=/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users
On Wed, 2008-07-16 at 09:59 +0200, Niels Nes wrote:
plain text document attachment (Re: [MonetDB-users] Problems With CREATE TEMPORARY TABLE.eml) On Tue, Jul 15, 2008 at 03:23:15PM -0400, James Leigh wrote:
Connection c4 = ds1.getConnection(); c4.setAutoCommit(true); Connection c76 = ds1.getConnection(); c76.setAutoCommit(false); Statement s93 = c76.createStatement(); s93.execute("CREATE TEMPORARY TABLE TRANSACTION_STATEMENTS (\n ctx INTEGER NOT NULL,\n subj INTEGER NOT NULL,\n pred INTEGER NOT NULL,\n obj INTEGER NOT NULL,\n expl BOOL NOT NULL\n)");Here Your 'c76' transaction is started.
s93.close(); Statement s96 = c4.createStatement(); s96.execute("CREATE TABLE paints_2 (\n ctx INTEGER NOT NULL,\n subj INTEGER NOT NULL,\n obj INTEGER NOT NULL,\n expl BOOL NOT NULL\n)"); s96.close();This transaction is auto-commited but afcourse (correctly) not visiable by your c76 transaction.
PreparedStatement ps102 = c76.prepareStatement("INSERT INTO paints_2\nSELECT DISTINCT ctx, subj, obj, expl FROM TRANSACTION_STATEMENTS tr\nWHERE NOT EXISTS (SELECT * FROM paints_2 st\nWHERE st.ctx = tr.ctx AND st.subj = tr.subj AND st.obj = tr.obj AND st.expl = tr.expl)");So this statement will give an error as it doesn't know about paints_2.
A simple change of order of you java statements should fix this problem.
Niels
Thanks for your quick response. It is unfortunate that MonetDB does not support this, but it is manageable. Any ideas why this other statement (ps72) throws a "Query did not produce a result set" in MonetDB?
public void testSelectWhileCreateTable() throws Exception { Connection c2 = ds1.getConnection(); c2.setAutoCommit(true); Statement s7 = c2.createStatement(); s7.execute("CREATE TABLE HASH_VALUES (\n id INTEGER NOT NULL,\n value BIGINT NOT NULL\n)"); s7.close(); PreparedStatement ps72 = c2.prepareStatement("SELECT id, value\nFROM HASH_VALUES\nWHERE value IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); ps72.setLong(1, 157585763729791056l); ps72.setLong(2, 1105705122070497121l); ps72.setLong(3, 940939309403574879l); ps72.setLong(4, 44738479624713631l); ps72.setLong(5, 93693228172326007l); ps72.setLong(6, 909856949769965718l); ps72.setLong(7, 388219017765868455l); ps72.setLong(8, 498326321060763331l); ps72.setLong(9, 917168269028472831l); ps72.setLong(10, 560146884310966732l); ps72.setLong(11, 178986943741094305l); ps72.setNull(12, -5); ps72.setNull(13, -5); ps72.setNull(14, -5); ps72.setNull(15, -5); ResultSet rs73 = ps72.executeQuery(); rs73.next(); rs73.close(); Statement s76 = c2.createStatement(); s76.execute("CREATE TABLE TRIPLES (\n ctx INTEGER NOT NULL,\n subj INTEGER NOT NULL,\n pred INTEGER NOT NULL,\n obj INTEGER NOT NULL,\n expl BOOL NOT NULL\n)"); s76.close(); ps72.setLong(1, 157585763729791056l); ps72.setLong(2, 93693228172326007l); ps72.setLong(3, 44738479624713631l); ps72.setLong(4, 909856949769965718l); ps72.setLong(5, 388219017765868455l); ps72.setLong(6, 917168269028472831l); ps72.setNull(7, -5); ps72.setNull(8, -5); ps72.setNull(9, -5); ps72.setNull(10, -5); ps72.setNull(11, -5); ps72.setNull(12, -5); ps72.setNull(13, -5); ps72.setNull(14, -5); ps72.setNull(15, -5); ResultSet rs88 = ps72.executeQuery(); rs88.close(); ps72.close(); c2.close(); }
On Wed, Jul 16, 2008 at 11:48:58AM -0400, James Leigh wrote:
On Wed, 2008-07-16 at 09:59 +0200, Niels Nes wrote:
plain text document attachment (Re: [MonetDB-users] Problems With CREATE TEMPORARY TABLE.eml) On Tue, Jul 15, 2008 at 03:23:15PM -0400, James Leigh wrote:
Connection c4 = ds1.getConnection(); c4.setAutoCommit(true); Connection c76 = ds1.getConnection(); c76.setAutoCommit(false); Statement s93 = c76.createStatement(); s93.execute("CREATE TEMPORARY TABLE TRANSACTION_STATEMENTS (\n ctx INTEGER NOT NULL,\n subj INTEGER NOT NULL,\n pred INTEGER NOT NULL,\n obj INTEGER NOT NULL,\n expl BOOL NOT NULL\n)");Here Your 'c76' transaction is started.
s93.close(); Statement s96 = c4.createStatement(); s96.execute("CREATE TABLE paints_2 (\n ctx INTEGER NOT NULL,\n subj INTEGER NOT NULL,\n obj INTEGER NOT NULL,\n expl BOOL NOT NULL\n)"); s96.close();This transaction is auto-commited but afcourse (correctly) not visiable by your c76 transaction.
PreparedStatement ps102 = c76.prepareStatement("INSERT INTO paints_2\nSELECT DISTINCT ctx, subj, obj, expl FROM TRANSACTION_STATEMENTS tr\nWHERE NOT EXISTS (SELECT * FROM paints_2 st\nWHERE st.ctx = tr.ctx AND st.subj = tr.subj AND st.obj = tr.obj AND st.expl = tr.expl)");So this statement will give an error as it doesn't know about paints_2.
A simple change of order of you java statements should fix this problem.
Niels
Thanks for your quick response. It is unfortunate that MonetDB does not support this, but it is manageable. Any ideas why this other statement
I don't think correct 'transaction isolation' is a problem. Its a feature any dbms should have. Just revert the 2 create statements and it should work as expected.
(ps72) throws a "Query did not produce a result set" in MonetDB?
No I don't. Could you try to run the queries with out java, ie simply using mclient -lsql?
Niels
public void testSelectWhileCreateTable() throws Exception { Connection c2 = ds1.getConnection(); c2.setAutoCommit(true); Statement s7 = c2.createStatement(); s7.execute("CREATE TABLE HASH_VALUES (\n id INTEGER NOT NULL,\n value BIGINT NOT NULL\n)"); s7.close(); PreparedStatement ps72 = c2.prepareStatement("SELECT id, value\nFROM HASH_VALUES\nWHERE value IN (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); ps72.setLong(1, 157585763729791056l); ps72.setLong(2, 1105705122070497121l); ps72.setLong(3, 940939309403574879l); ps72.setLong(4, 44738479624713631l); ps72.setLong(5, 93693228172326007l); ps72.setLong(6, 909856949769965718l); ps72.setLong(7, 388219017765868455l); ps72.setLong(8, 498326321060763331l); ps72.setLong(9, 917168269028472831l); ps72.setLong(10, 560146884310966732l); ps72.setLong(11, 178986943741094305l); ps72.setNull(12, -5); ps72.setNull(13, -5); ps72.setNull(14, -5); ps72.setNull(15, -5); ResultSet rs73 = ps72.executeQuery(); rs73.next(); rs73.close(); Statement s76 = c2.createStatement(); s76.execute("CREATE TABLE TRIPLES (\n ctx INTEGER NOT NULL,\n subj INTEGER NOT NULL,\n pred INTEGER NOT NULL,\n obj INTEGER NOT NULL,\n expl BOOL NOT NULL\n)"); s76.close(); ps72.setLong(1, 157585763729791056l); ps72.setLong(2, 93693228172326007l); ps72.setLong(3, 44738479624713631l); ps72.setLong(4, 909856949769965718l); ps72.setLong(5, 388219017765868455l); ps72.setLong(6, 917168269028472831l); ps72.setNull(7, -5); ps72.setNull(8, -5); ps72.setNull(9, -5); ps72.setNull(10, -5); ps72.setNull(11, -5); ps72.setNull(12, -5); ps72.setNull(13, -5); ps72.setNull(14, -5); ps72.setNull(15, -5); ResultSet rs88 = ps72.executeQuery(); rs88.close(); ps72.close(); c2.close(); }
On Wed, 2008-07-16 at 18:04 +0200, Niels Nes wrote:
On Wed, Jul 16, 2008 at 11:48:58AM -0400, James Leigh wrote:
Thanks for your quick response. It is unfortunate that MonetDB does not support this, but it is manageable. Any ideas why this other statement
I don't think correct 'transaction isolation' is a problem. Its a feature any dbms should have. Just revert the 2 create statements and it should work as expected.
It is a problem if the schema is not fixed and there are multiple connections that all need to share data in a new table. However, as I said it is manageable.
(ps72) throws a "Query did not produce a result set" in MonetDB?
No I don't. Could you try to run the queries with out java, ie simply using mclient -lsql?
Niels
The sql is fine, it runs the first time - no problems. However, when executed again (after a new table is created) it fails.
James
On Wed, 2008-07-16 at 18:04 +0200, Niels Nes wrote:
(ps72) throws a "Query did not produce a result set" in MonetDB?
No I don't. Could you try to run the queries with out java, ie simply using mclient -lsql?
Niels
Here you can see that the "exec 0();" command works before a table is created, but is lost once a table is created.
sql>prepare select 0; sql>exec 0(); +--------------+ | single_value | +==============+ | 0 | +--------------+ sql>create table blah(blah integer); sql>exec 0(); sql>prepare select 1; sql>exec 0(); +--------------+ | single_value | +==============+ | 1 | +--------------+
On Mon, Jul 21, 2008 at 10:07:04AM -0400, James Leigh wrote:
On Wed, 2008-07-16 at 18:04 +0200, Niels Nes wrote:
(ps72) throws a "Query did not produce a result set" in MonetDB?
No I don't. Could you try to run the queries with out java, ie simply using mclient -lsql?
Niels
Here you can see that the "exec 0();" command works before a table is created, but is lost once a table is created.
sql>prepare select 0; sql>exec 0(); +--------------+ | single_value | +==============+ | 0 | +--------------+ sql>create table blah(blah integer); sql>exec 0(); sql>prepare select 1; sql>exec 0(); +--------------+ | single_value | +==============+ | 1 | +--------------+
Good catch. The problem is indeed that the query and prepared statement cache is cleaned after an 'schema' statement. A simple reorder of the code should help. A better error message should afcourse be given. A more flexible prepare/execute scheme could be a nice feature request.
Niels
participants (2)
-
James Leigh -
Niels Nes