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
