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 >
