Bizarre behavior

[Niclas Hedhman]

Version: 3.10.1

Postgres 9.6 server

JDBC Driver: org.postgresql:postgresql:42.1.4

T

The testcase found below will not work. It will say 

    Caused by: org.postgresql.util.PSQLException: ERROR: schema "POLYGENE" does not exist

when trying to create the table.

Further looking, when the commit() is issued inside the

     dsl.createSchema( "POLYGENE" ).execute();    

the transaction state is in "IDLE" mode, and the commit is not executed. The reason for that is that some ROLLBACK has been executed as part of a close() that I don't understand. The commit() not executing is silently ignored in driver, and nothing happens. Hence the failure on the creation of the table.

Ok, so that might have been a bug.

Swap the comments of 

    DataSource dataSource = dbcpDataSource( host, port );
//  DataSource dataSource = rawDataSource(host,port);

and the test case works. Now, that could possibly be that there is an autoCommit going on. I have not investigated that.

Swap those comments back to original, and the test fails (after manually cleaning up the database).

Finally, swap the 

        TransactionProvider transactionProvider = new PolygeneTransactionProvider( new ThreadLocalTransactionProvider( connectionProvider, false ) );
//        TransactionProvider transactionProvider = new ThreadLocalTransactionProvider( connectionProvider, false );

and the testcase also works. The wrapper couldn't be simpler, but why does that cause a failure of this nature? Why is JOOQ checking the instance type for behavior, instead of having proper methods for it?

package org.hedhman.niclas;

import javax.sql.DataSource;
import org.apache.commons.dbcp2.BasicDataSource;
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.Test;
import org.postgresql.ds.PGSimpleDataSource;
import org.postgresql.jdbc.AutoSave;

import static org.apache.polygene.entitystore.sql.TableFields.tableNameColumn;
import static org.apache.polygene.entitystore.sql.TypesTable.makeField;

public class Experiment
{
    @Test
    public void test1()
        throws Exception
    {
        String host = "127.0.0.1";
        int port = 5432;
        DataSource dataSource = dbcpDataSource( host, port );
//        DataSource dataSource = rawDataSource(host,port);
        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 );
        Name tableName  = DSL.name( schema.getName(), "TESTTABLE" );

        dsl.transaction( t -> {
            dsl.createSchema( "POLYGENE" ).execute();
        } );

        dsl.transaction( t -> {

            dsl.createTableIfNotExists( tableName )
               .column( identityColumn )
               .execute();
        });
    }
    private DataSource dbcpDataSource( String host, int port )
        throws Exception
    {
        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( String host, int port )
        throws Exception
    {
        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 );
        }
    }
}

[Lukas Eder]

Hi Niclas,

Thanks for your detailed report. Indeed, this is a known limitation of the current implementation and API. A feature request to improve this is on the roadmap:

https://github.com/jOOQ/jOOQ/issues/5388

Specifically, the TransactionProvider should expose a ConnectionProvider, which should be preferred by jOOQ's internals over the Configuration.connectionProvider(), if available. All this inconvenience should be rectified and re-specified with #5388.

In your actual implementation (that is not the simplified version you posted here), what was your intent of wrapping the ThreadLocalTransactionProvider specifically?

I cannot explain the difference between the dbcpDataSource and the rawDataSource.

Hope this helps,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

... in any case, I will further investigate this issue. Perhaps there's something that can be fixed without implementing #5388. I've created an issue for this:

https://github.com/jOOQ/jOOQ/issues/6732

[Niclas Hedhman]

Thanks a lot for looking into this. It has been driving my nuts... Also want to express my gratitude for JOOQ, as I am really really sick and tired of JPA/Hibernate and variants.

The real reason behind the wrapper was to try to locate why my code (Apache Polygene[1] extension in the works) doesn't work. 

The actual code segment not working is;

dsl.transaction( t -> {
    if( dsl.isSchemaCapable()
        && dsl.meta().getSchemas().stream().noneMatch( s -> schema.getName().equalsIgnoreCase( s.getName() ) ) )
    {
        dsl.createSchema( schemaName ).execute();
    }

} );

NOTE; the dsl is an 'enhanced' type on my side. Apache Polygene is capable of adding mixins/aspects/traits, so we have an extended interface, but backed by your implementation (See at bottom, can be confusing)

This is for a "SQL Entity Store extension"[2] to bring "proper" SQL to our capabilities (we have 12 other Entity Store impls as well). 

The problem in the code above is that the schema is not created. The commit() at Postgres driver level happens, but before that call, there has been a "close()", seemingly do to some "keep" variable stating "false" (don't recall the exact details here). And the driver rollbacks the transaction during the close, and doesn't warn or anything about the commit() that is just 'lost'. AT first I tried to extract sequencing and specifics with the wrapper, but that ended up influencing the behavior.

I have still not solved the actual problem, and next time I dig in it (different pool implementations, different JDBC drivers), I will try to provide more feedback of my findings, because it just doesn't seem right.

Oh yeah, if I turn on auto-commit, my entire implementation "works", but with it disabled there are several different problems in my stuff, and I don't think all of that is on my side of the equation, and think that there might be some JOOQ issues as well. But JOOQ is helping so much that instead of giving up, I want to assist in fixing these, rather than going JDBC-native. Unfortunately, only work on this on spare time.

[1] https://github.com/apache/polygene-java

[2] https://github.com/apache/polygene-java/tree/es-sql/extensions/entitystore-sql

Cheers

Niclas

P.S. If you are interested in what we are doing, perhaps first read https://hedhman.wordpress.com/2017/05/10/introducing-apache-polygene/

P.P.S If you want to debug, I think checking out the entire repository and the "es-sql" branch, then go to extensions/entitystore-sql and run "../../gradlew build check" should work without additional setup. I have focused on the problems in the Postgres test variant.

@Mixins( JooqDslContext.Mixin.class )
public interface JooqDslContext extends DSLContext
{
    boolean isSchemaCapable();

    Name tableNameOf( String tableName );

    Table<Record> tableOf( String tableName );

    class Mixin
        implements InvocationHandler
    {
        private final Schema schema;
        private final DSLContext dsl;

        public Mixin( @Service DataSource dataSource, @Uses Settings settings, @Uses SQLDialect dialect, @Uses Schema schema )
        {
            this.schema = schema;

            ConnectionProvider connectionProvider = new DataSourceConnectionProvider( dataSource );

            TransactionProvider transactionProvider = new ThreadLocalTransactionProvider( connectionProvider, false );
            Configuration configuration = new DefaultConfiguration()
                .set( dialect )
                .set( connectionProvider )
                .set( transactionProvider )
                .set( settings );

            dsl = DSL.using( configuration );
        }

        @Override
        public Object invoke( Object o, Method method, Object[] args )
            throws Throwable
        {
            if( method.getName().equals( "tableOf" ) )
            {
                return DSL.table( tableNameOf( (String) args[ 0 ] ) );
            }
            if( method.getName().equals( "tableNameOf" ) )
            {
                return tableNameOf( (String) args[ 0 ] );
            }

            if( method.getName().equals( "isSchemaCapable" ) )
            {
                return isSchemaCapable();
            }
            return method.invoke( dsl, args );       // delegate all
        }

        private Name tableNameOf( String name )
        {
            return this.isSchemaCapable() ? DSL.name( schema.getName(), name ) : DSL.name( name );
        }

        private boolean isSchemaCapable()
        {
            return !dsl.dialect().equals( SQLDialect.SQLITE ) && !dsl.dialect().equals( SQLDialect.MYSQL );
        }
    }
}

// instantiation of the above;

SqlEntityStoreConfiguration config = this.configuration.get();  // our configuration
SQLDialect dialect = getSqlDialect( config );  // just convert String and deals with null value.
Settings settings = serviceDescriptor          
    .metaInfo( Settings.class );               // metaInfo is set 'onto' services during bootstrap (see below).
                                               // set to new Settings().withRenderNameStyle( RenderNameStyle.QUOTED )

String schemaName = config.schemaName().get();
String typesTableName = config.typesTableName().get();
String entitiesTableName = config.entitiesTableName().get();
Schema schema = DSL.schema( DSL.name( schemaName ) );
// tbf is a factory for instantiation of transient (mutable/non-persistable) composites (objects)
// the additional args are mapped to '@Uses" parameter in constructor
dsl = tbf.newTransient( JooqDslContext.class, settings, dialect, schema );  

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/Ek-Pu3i2vKE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

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

[Niclas Hedhman]

So, I have come back to this issue...

It seems that I was chasing another problem in the MySQL case, and adding additional debugging support to figure out what was going on there, and down the rabbit hole of this issue and lost track of the problem that started this.

So, Postgresql case works fine. For MySQL, my VARCHAR fields becomes CHAR(1) fields and the only code that differs is that Schema is not used in MySQL case. I will try to create a testcase showing this a bit later.

Cheers

Niclas

[Lukas Eder]

Hi Niclas,

Is there still anything I can be of assistance with?

Thanks,

Lukas

[Niclas Hedhman]

Maybe a bit late to report back, but I haven't tested this for a long time.

So, using 3.11.11, this issue with CHAR(1) in MySQL dialects are no longer happening, and it seems that all initial concerns that I had 2-3 years back are resolved. Excellent!

The actual code used is;

class Mixin
    implements InvocationHandler
{
    private final DSLContext dsl;

    public Mixin( @Service javax.sql.DataSource dataSource, @Uses Settings settings, @Uses SQLDialect dialect )

    {
        ConnectionProvider connectionProvider = new DataSourceConnectionProvider( dataSource );

        TransactionProvider transactionProvider = new ThreadLocalTransactionProvider( connectionProvider, false );
        Configuration configuration = new DefaultConfiguration()
            .set( dialect )
            .set( connectionProvider )
            .set( transactionProvider )
            .set( settings );
        dsl = DSL.using( configuration );
    }

I can't explain "Mixin" and "@Uses" as it is part of the Composite Oriented Programming framework (Qi4j/Polygene) where this is used.

Cheers

Niclas

To unsubscribe from this group and stop receiving emails from it, send an email to jooq...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/Ek-Pu3i2vKE/unsubscribe.

To unsubscribe from this group and all its topics, send an email to jooq...@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--

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

--

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

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq...@googlegroups.com.

[Lukas Eder]

Great to hear, thanks for reporting back, Niclas.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/64aa66a9-fd71-4cbb-be25-90a0012a5572%40googlegroups.com.