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
