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 Mon, 16 Oct 2017 00:21:29 GMT
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 <stazi@kapsi.fi>
wrote:

> Sounds like there is some sort of hidden persisted state. Maybe a
> configuration of some kind?
>
> -----Original Message-----
> From: "Niclas Hedhman" <niclas@hedhman.org>
> Sent: ‎15/‎10/‎2017 07:21
> To: "dev@polygene.apache.org" <dev@polygene.apache.org>
> 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 <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
>



-- 
Niclas Hedhman, Software Developer
http://polygene.apache.org - New Energy for Java

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