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 Sat, 14 Oct 2017 09:48:42 GMT
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

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