[FRG-141] table created via jdbc internal query in a udx produces lock error when queried

[dynamobi-build]

[reporter="elin", created="Mon, 5 Jun 2006 17:03:25 -0500 (GMT-05:00)"]
To reproduce:

create schema ct;
set schema 'ct';
set path 'ct';

create table t1 (cola int, colb varchar(20));
insert into t1 values (1, 'one'), (2, 'two');

create function create_table_as_select(schema_name varchar(65535), table_name varchar(65535), c cursor)
returns table(errors varchar(65535))
language java
parameter style system defined java
modifies sql data
external name 'class com.lucidera.luciddb.test.udr.CreateTableAsSelectUdx.execute';

select * from table(
  create_table_as_select(
    'CT',
    'MYTABLE',
    cursor(select * from t1)
));

-- this produces the error:
select * from ct.mytable order by 1;

Stack trace:
Error: Failed to acquire lock on LOCALDB.CT.MYTABLE (state=,code=0)
net.sf.farrago.jdbc.FarragoJdbcUtil$FarragoSqlException: Failed to acquire lock on LOCALDB.CT.MYTABLE
        at net.sf.farrago.resource.FarragoResource$_Def0.ex(FarragoResource.java:1352)
        at com.lucidera.farrago.LucidDbTxnMgr.acquireLock(LucidDbTxnMgr.java:118)
        at com.lucidera.farrago.LucidDbTxnMgr.accessTable(LucidDbTxnMgr.java:90)
        at net.sf.farrago.db.FarragoDbNullTxnMgr.accessTablePrivate(FarragoDbNullTxnMgr.java:130)
        at net.sf.farrago.db.FarragoDbNullTxnMgr.accessTables(FarragoDbNullTxnMgr.java:115)
        at net.sf.farrago.db.FarragoDbStmtContextBase.accessTables(FarragoDbStmtContextBase.java:264)
        at net.sf.farrago.db.FarragoDbStmtContext.execute(FarragoDbStmtContext.java:209)
        at net.sf.farrago.jdbc.engine.FarragoJdbcEngineStatement.execute(FarragoJdbcEngineStatement.java:107)
        at sqlline.SqlLine$Commands.execute(Ljava.lang.String;Z)Z(Unknown Source)
        at sqlline.SqlLine$Commands.sql(Ljava.lang.String;)Z(Unknown Source)
        at sqlline.SqlLine.dispatch(Ljava.lang.String;)Z(Unknown Source)
        at sqlline.SqlLine.begin([Ljava.lang.String;Ljava.io.InputStream;)V(Unknown Source)
        at sqlline.SqlLine.mainWithInputRedirection([Ljava.lang.String;Ljava.io.InputStream;)V(Unknown Source)
        at sqlline.SqlLine.main([Ljava.lang.String;)V(Unknown Source)

[dynamobi-build]

[author="jvs", created="Mon, 5 Jun 2006 17:19:11 -0500 (GMT-05:00)"] Ah, the infamous CREATE TABLE AS SELECT. There's probably a bug in the way we set up the jdbc:default:connection reentrant session so that its locks don't get released correctly.

(For the record, this implementation for CREATE TABLE AS SELECT is OK for Farrago's row-store implementation, but should never be used with LucidDB because single-row inserts are so slow there.)

[dynamobi-build]

[author="elin", created="Wed, 5 Jul 2006 23:44:41 -0500 (GMT-05:00)"] Reminder to self: when fixed, uncomment out test in udx.cursorTests and unflag as knownFailure. If the test's left in, everything else has locking problems too.

[dynamobi-build]

[author="jvs", created="Thu, 8 Feb 2007 00:03:15 -0500 (GMT-05:00)"] Leo hit a similar case; here's the repro. I think I'd better fix this one...

create schema x;
create table x.t(i int);
call applib.compute_statistics_for_schema('X');
insert into x.t values (10);

[dynamobi-build]

[author="jvs", created="Sat, 10 Feb 2007 20:25:13 -0500 (GMT-05:00)"] Fixed in eigenchange 8697.

[dynamobi-build]

[author="jvs", created="Sat, 10 Feb 2007 20:25:50 -0500 (GMT-05:00)"] Reassigning to Liz for verification (but I already re-enabled the original test, so maybe we're done).