polygene-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Niclas Hedhman <nic...@hedhman.org>
Subject Re: JOOQ problems
Date Sun, 15 Oct 2017 04:20:37 GMT
Weird, this morning the Experiment with DBCP is working again. I am
confused!

On Sat, Oct 14, 2017 at 5:48 PM, Niclas Hedhman <niclas@hedhman.org> 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 <niclas@hedhman.org>
> 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 <kent.soelvsten@gmail.com>
>> 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 <niclas@hedhman.org>
>>> 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 <
>>> > stanislav.muhametsin@zest.mail.kapsi.fi> 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

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message