Worse... I had my own wrapper (plain delegating pojo) around
TransactionProvider, for debugging purposes. I dropped that for clarity in
the code posted. And that made all the difference for this particular
issue. The full non-working class is shown below. Swap either of the two
commented lines (with the adjacent one) and the testcase succeeds. WEIRD!!!
package org.apache.polygene.entitystore.sql;
import java.util.HashMap;
import java.util.Map;
import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.polygene.test.docker.DockerRule;
import org.jooq.Configuration;
import org.jooq.ConnectionProvider;
import org.jooq.DSLContext;
import org.jooq.Field;
import org.jooq.Name;
import org.jooq.SQLDialect;
import org.jooq.Schema;
import org.jooq.TransactionContext;
import org.jooq.TransactionProvider;
import org.jooq.conf.RenderNameStyle;
import org.jooq.conf.Settings;
import org.jooq.exception.DataAccessException;
import org.jooq.impl.DSL;
import org.jooq.impl.DataSourceConnectionProvider;
import org.jooq.impl.DefaultConfiguration;
import org.jooq.impl.ThreadLocalTransactionProvider;
import org.junit.ClassRule;
import org.junit.Test;
import org.postgresql.ds.PGSimpleDataSource;
import org.postgresql.jdbc.AutoSave;
import static org.apache.polygene.entitystore.sql.TypesTable.makeField;
public class Experiment
{
@ClassRule
public static final DockerRule DOCKER;
static
{
Map<String,String> environment = new HashMap<>();
environment.put( "POSTGRES_USER", System.getProperty( "user.name" ));
environment.put( "POSTGRES_PASSWORD", "ThisIsGreat!");
DOCKER = new DockerRule( "postgres",
environment,
10000L,
"PostgreSQL init process complete;
ready for start up." );
}
@Test
public void test1()
throws Exception
{
DataSource dataSource = dbcpDataSource();
// DataSource dataSource = rawDataSource();
Settings settings = new Settings().withRenderNameStyle(
RenderNameStyle.QUOTED );
SQLDialect dialect = SQLDialect.POSTGRES;
Schema schema = DSL.schema( DSL.name( "POLYGENE" ) );
ConnectionProvider connectionProvider = new
DataSourceConnectionProvider( dataSource );
TransactionProvider transactionProvider = new
PolygeneTransactionProvider( new ThreadLocalTransactionProvider(
connectionProvider, false ) );
// TransactionProvider transactionProvider = new
ThreadLocalTransactionProvider( connectionProvider, false );
Configuration configuration = new DefaultConfiguration()
.set( dialect )
.set( connectionProvider )
.set( transactionProvider )
.set( settings );
DSLContext dsl = DSL.using( configuration );
Field<String> identityColumn = makeField( "_identity", String.class );
dsl.transaction( t -> {
if( dsl.meta().getSchemas().stream().noneMatch( s ->
schema.getName().equalsIgnoreCase( s.getName() ) ) )
{
dsl.createSchema( "POLYGENE" ).execute();
}
} );
Name tableName = DSL.name( schema.getName(), "TESTTABLE" );
dsl.transaction( t -> {
dsl.createTableIfNotExists( tableName )
.column( identityColumn )
.execute();
});
}
private DataSource dbcpDataSource()
throws Exception
{
String host = DOCKER.getDockerHost();
int port = DOCKER.getExposedContainerPort( "5432/tcp" );
BasicDataSource pool = new BasicDataSource();
String driverClass = "org.postgresql.Driver";
Class.forName( driverClass );
pool.setDriverClassName( driverClass );
pool.setUrl( "jdbc:postgresql://" + host + ":" + port +
"/jdbc_test_db" );
pool.setUsername( System.getProperty( "user.name" ) );
pool.setPassword( "ThisIsGreat!" );
pool.setDefaultAutoCommit( false );
return pool;
}
private DataSource rawDataSource()
throws Exception
{
String host = DOCKER.getDockerHost();
int port = DOCKER.getExposedContainerPort( "5432/tcp" );
PGSimpleDataSource datasource = new PGSimpleDataSource();
datasource.setUser( System.getProperty( "user.name" ) );
datasource.setPassword( "ThisIsGreat!" );
datasource.setAutosave( AutoSave.NEVER );
datasource.setUrl( "jdbc:postgresql://" + host + ":" + port +
"/jdbc_test_db" );
return datasource;
}
static class PolygeneTransactionProvider
implements TransactionProvider
{
private TransactionProvider delegate;
private PolygeneTransactionProvider( TransactionProvider delegate )
{
this.delegate = delegate;
}
@Override
public void begin( TransactionContext ctx )
throws DataAccessException
{
System.out.println( "\"------------------------------>
begin( " + ctx + " )" );
delegate.begin( ctx );
}
@Override
public void commit( TransactionContext ctx )
throws DataAccessException
{
System.out.println( "------------------------------>
commit( " + ctx + " )" );
delegate.commit( ctx );
}
@Override
public void rollback( TransactionContext ctx )
throws DataAccessException
{
System.out.println( "\"------------------------------>
rollback( " + ctx + " )" );
delegate.rollback( ctx );
}
}
}
On Sun, Oct 15, 2017 at 6:45 PM, Stanislav Muhametsin <[email protected]>
wrote:
> Sounds like there is some sort of hidden persisted state. Maybe a
> configuration of some kind?
>
> -----Original Message-----
> From: "Niclas Hedhman" <[email protected]>
> Sent: 15/10/2017 07:21
> To: "[email protected]" <[email protected]>
> Subject: Re: JOOQ problems
>
> Weird, this morning the Experiment with DBCP is working again. I am
> confused!
>
> On Sat, Oct 14, 2017 at 5:48 PM, Niclas Hedhman <[email protected]>
> wrote:
>
> > After a long hiatus (using Polygene in my new venture), I have started to
> > tackle the JOOQ problem in the new SQL EntityStore again.
> >
> > I noticed that during Schema creation, a rollback was issued before the
> > commit (which is swallowed silently in the Postgres driver). And digging
> in
> > that is that there was a strange "close() --> release() --> close()"
> > sequence, where the last one did a ROLLBACK before closing the
> connection.
> >
> > So, I distilled the problem down to the minimum commands to see if I
> could
> > get any wiser, into an "Experiment" where everything is stripped away,
> but
> > keeping the various bits and pieces in Polygene mechanics. See the class
> > below.
> >
> >
> > With the "rawDataSource()" that snippet of code works. But if changed to
> > "pooledDataSource()" there is a rollback before commit. The pool used is
> > Commons DBCP2 ver 2.1.1 and Commons Pool2 ver 2.4.2 (the used versions in
> > Polygene).
> >
> >
> > Does anyone have additional information of any kind regarding the
> behavior
> > of DBCP, close() method and why such an unexpected behavior occurs?? Much
> > appreciated.
> >
> > Does anyone have any suggestions on how to proceed with proper DB pool
> > support, since this is obviously a problem right now??
> >
> >
> >
> > public class Experiment
> >
> > {
> > @ClassRule
> > public static final DockerRule DOCKER;
> >
> > static
> > {
> > Map<String, String> environment = new HashMap<>();
> > environment.put( "POSTGRES_USER", System.getProperty( "user.name"
> ) );
> > environment.put( "POSTGRES_PASSWORD", "ThisIsGreat!" );
> >
> > DOCKER = new DockerRule( "postgres",
> > environment,
> > 3000L,
> > "PostgreSQL init process complete;
> ready for start up." );
> > }
> >
> > @Test
> > public void rawJooqBehavior()
> > throws Exception
> > {
> > // DataSource dataSource = pooledDataSource();
> > DataSource dataSource = rawDataSource();
> > Settings settings = new Settings().withRenderNameStyle(
> RenderNameStyle.QUOTED );
> > SQLDialect dialect = SQLDialect.POSTGRES;
> > Schema schema = DSL.schema( DSL.name( "POLYGENE" ) );
> >
> > ConnectionProvider connectionProvider = new
> DataSourceConnectionProvider( dataSource );
> > TransactionProvider transactionProvider = new
> ThreadLocalTransactionProvider( connectionProvider, false );
> > Configuration configuration = new DefaultConfiguration()
> > .set( dialect )
> > .set( connectionProvider )
> > .set( transactionProvider )
> > .set( settings );
> >
> > DSLContext dsl = DSL.using( configuration );
> >
> > Field<String> identityColumn = makeField( "_identity",
> String.class );
> >
> > dsl.transaction( t -> {
> > dsl.createSchema( "POLYGENE" ).execute();
> > } );
> >
> > Name tableName = DSL.name( schema.getName(), "TESTTABLE" );
> > dsl.transaction( t -> {
> >
> > dsl.createTableIfNotExists( tableName )
> > .column( identityColumn )
> > .execute();
> > } );
> > }
> >
> > private DataSource pooledDataSource()
> > throws Exception
> > {
> > String host = DOCKER.getDockerHost();
> > int port = DOCKER.getExposedContainerPort( "5432/tcp" );
> > BasicDataSource pool = new BasicDataSource();
> >
> > String driverClass = "org.postgresql.Driver";
> > Class.forName( driverClass );
> > pool.setDriverClassName( driverClass );
> > pool.setUrl( "jdbc:postgresql://" + host + ":" + port +
> "/jdbc_test_db" );
> > pool.setUsername( System.getProperty( "user.name" ) );
> > pool.setPassword( "ThisIsGreat!" );
> > pool.setDefaultAutoCommit( false );
> > return pool;
> > }
> >
> > private DataSource rawDataSource()
> > throws Exception
> > {
> > String host = DOCKER.getDockerHost();
> > int port = DOCKER.getExposedContainerPort( "5432/tcp" );
> > PGSimpleDataSource datasource = new PGSimpleDataSource();
> > datasource.setUser( System.getProperty( "user.name" ) );
> > datasource.setPassword( "ThisIsGreat!" );
> > datasource.setAutosave( AutoSave.NEVER );
> > datasource.setUrl( "jdbc:postgresql://" + host + ":" + port +
> "/jdbc_test_db" );
> > return datasource;
> > }
> > }
> >
> >
> >
> >
> > On Tue, Sep 12, 2017 at 7:10 AM, Niclas Hedhman <[email protected]>
> > wrote:
> >
> >> I meant, that I had a breakpoint inside the Connection.commit() to make
> >> sue that the "commit" in the log was actually reaching the DB's driver.
> >>
> >> I am turning autoCommit off explicitly and the creation is wrapped in
> the
> >> JOOQ style transaction.
> >>
> >>
> >> if( config.createIfMissing().get() )
> >> {
> >> dsl.transaction( t -> {
> >> if( dsl.isSchemaCapable()
> >> && dsl.meta().getSchemas().stream().noneMatch( s ->
> schema.getName().equalsIgnoreCase( s.getName() ) ) )
> >> {
> >> dsl.createSchema( schemaName ).execute();
> >> }
> >> } );
> >>
> >> dsl.transaction( t -> {
> >>
> >> dsl.createTableIfNotExists( dsl.tableNameOf( typesTableName ) )
> >> .column( identityColumn )
> >> .column( tableNameColumn )
> >> .column( createdColumn )
> >> .column( modifiedColumn )
> >> .execute();
> >>
> >> dsl.createTableIfNotExists( dsl.tableNameOf( entitiesTableName
> ) )
> >> .column( identityColumn )
> >> .column( applicationVersionColumn )
> >> .column( valueIdentityColumn )
> >> .column( versionColumn )
> >> .column( typeNameColumn )
> >> .column( modifiedColumn )
> >> .column( createdColumn )
> >> .execute();
> >> } );
> >> }
> >>
> >>
> >> On Tue, Sep 12, 2017 at 3:32 AM, Kent Sølvsten <
> [email protected]>
> >> wrote:
> >>
> >>> What do you mean by a commit() being executed in the driver ?
> >>>
> >>> autocommit turned on as a default?
> >>>
> >>> If that is the case, it might be worth checking whether JOOQ could be
> >>> turning that off - so the schema creation is actually not commit()-ed.
> >>>
> >>>
> >>>
> >>>
> >>> On Mon, Sep 11, 2017 at 3:15 AM, Niclas Hedhman <[email protected]>
> >>> wrote:
> >>>
> >>> > Yeah, I can also run the commands and it works. There is a connection
> >>> pool,
> >>> > but even if it was two different connections, there is a commit()
> being
> >>> > executed (yes, in the Driver) and the SCHEMA does NOT show up in the
> DB
> >>> > itself. So, I thought it ended up in another database, but if I
> >>> pre-created
> >>> > the schema then a "schema already exists" is received, so I know it
> >>> > actually tries to put it in there.
> >>> >
> >>> >
> >>> >
> >>> > On Mon, Sep 11, 2017 at 3:03 AM, Stanislav Muhametsin <
> >>> > [email protected]> wrote:
> >>> >
> >>> > > When I run the following code in PgAdmin's SQL window, it succeeds:
> >>> > >
> >>> > > create schema "POLYGENE";
> >>> > > create table if not exists "POLYGENE"."TYPES"("_identity" varchar
> >>> null,
> >>> > > "_table_name" varchar null, "_created_at" timestamp null,
> >>> "_modified_at"
> >>> > > timestamp null);
> >>> > >
> >>> > > ("Query returned successfully with no result in 52 msec.", and
> >>> schema is
> >>> > > then visible in pgAdmin after refresh.)
> >>> > >
> >>> > > So I guess the problem is in JOOQ somewhere... Are you using
> >>> connection
> >>> > > pool, and maybe it uses different connection for 2nd statement?
> >>> That's my
> >>> > > only guess at this moment, without knowing anything about JOOQ
> >>> internals.
> >>> > >
> >>> > > On 10/09/2017 19:03, Niclas Hedhman wrote:
> >>> > >
> >>> > >> Hi,
> >>> > >> I need to consult some SQL-savvy folks here.
> >>> > >>
> >>> > >> I am getting all kinds of errors in my new SQL EntityStore, which
> >>> is in
> >>> > >> principle implemented, but not functional.
> >>> > >>
> >>> > >> Each of the supported SQL systems have different issues, and it is
> >>> > >> probably
> >>> > >> too much to bring all of it here. Let's start with the one that
> >>> should
> >>> > be
> >>> > >> the most SQL-compliant out there, Postgres.
> >>> > >>
> >>> > >> I get the following in the log;
> >>> > >>
> >>> > >>
> >>> > >> [@main ] DEBUG org.jooq.tools.LoggerListener - Executing
> query
> >>> > >> : create schema "POLYGENE"
> >>> > >> [@main ] DEBUG org.jooq.tools.LoggerListener - Affected
> row(s)
> >>> > >> : 0
> >>> > >> [@main ] DEBUG org.jooq.tools.StopWatch - Query executed
> >>> > :
> >>> > >> Total: 13.804ms
> >>> > >> [@main ] DEBUG org.jooq.tools.StopWatch - Finishing
> >>> > :
> >>> > >> Total: 14.469ms, +0.664ms
> >>> > >> [@main ] DEBUG org.jooq.impl.DefaultConnectionProvider -
> >>> commit
> >>> > >>
> >>> > >> [@main ] DEBUG org.jooq.tools.LoggerListener - Executing
> query
> >>> > >> : create table if not exists "POLYGENE"."TYPES"("_identity"
> >>> varchar
> >>> > >> null,
> >>> > >> "_table_name" varchar null, "_created_at" timestamp null,
> >>> "_modified_at"
> >>> > >> timestamp null)
> >>> > >> [@main ] DEBUG org.jooq.tools.LoggerListener - Exception
> >>> > >>
> >>> > >> org.jooq.exception.DataAccessException: SQL [create table if not
> >>> exists
> >>> > >> "POLYGENE"."TYPES"("_identity" varchar null, "_table_name" varchar
> >>> null,
> >>> > >> "_created_at" timestamp null, "_modified_at" timestamp null)];
> >>> ERROR:
> >>> > >> schema "POLYGENE" does not exist
> >>> > >>
> >>> > >>
> >>> > >> It tells me that the POLYGENE schema doesn't exist, even though it
> >>> was
> >>> > >> created milliseconds earlier (durable but not for very long?). I
> >>> even
> >>> > have
> >>> > >> the schema creation in a transaction (see the "commit"?) to make
> >>> sure.
> >>> > >>
> >>> > >> IF I break before the Schema is created and manually create the
> >>> Schema
> >>> > >> from
> >>> > >> psql, then the Schema creation fails, because it already exists.
> IF
> >>> I
> >>> > >> break
> >>> > >> after the Schema is created and committed in the code, there is no
> >>> > Schema
> >>> > >> in the Postgres DB when checked with psql.
> >>> > >>
> >>> > >> Does anyone have a clue of what is going on??
> >>> > >>
> >>> > >>
> >>> > >> Cheers
> >>> > >>
> >>> > >
> >>> > >
> >>> >
> >>> >
> >>> > --
> >>> > Niclas Hedhman, Software Developer
> >>> > http://polygene.apache.org - New Energy for Java
> >>> >
> >>>
> >>
> >>
> >>
> >> --
> >> Niclas Hedhman, Software Developer
> >> http://polygene.apache.org - New Energy for Java
> >>
> >
> >
> >
> > --
> > Niclas Hedhman, Software Developer
> > http://polygene.apache.org - New Energy for Java
> >
>
>
>
> --
> Niclas Hedhman, Software Developer
> http://polygene.apache.org - New Energy for Java
>
--
Niclas Hedhman, Software Developer
http://polygene.apache.org - New Energy for Java
