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
