Postgres autogenerated sequences for serials a are not generated

[Alessandro Brambilla]

Environment:

- JDK 17
- gradle 7.5
- PostgreSQL 11.17
- PostgreSQL JDBC drivers  42.3.6
- Jooq Professional   3.17.3
- nu.studer.jooq gradle plugin 7.1.1

I have a table with a "serial" id field like:  

create table pcs.pwappsetting
(
    id                 serial
        primary key,
    user_id            integer      not null
        references pcs.pwuser,
    name               varchar(255) not null,
    type               integer      not null,
    data               text         not null,
    favorite           boolean                  default false,
    creation_timestamp timestamp with time zone default CURRENT_TIMESTAMP,
    update_timestamp   timestamp with time zone default CURRENT_TIMESTAMP,
    creation_user      varchar(64)              default CURRENT_USER,
    update_user        varchar(64)              default CURRENT_USER
);

alter table pcs.pwappsetting owner to postgres;

grant select, update, usage on sequence pcs.pwappsetting_id_seq to <other_user1>;

grant delete, insert, references, select, trigger, truncate, update on pcs.pwappsetting to  <other_user2>  ;

grant delete, insert, references, select, trigger, truncate, update on pcs.pwappsetting to <other_user1>;

Upon creation Postgres automatically build the relative sequence from the serial field 

create sequence pcs.pwappsetting_id_seq  as integer;

alter sequence pcs.pwappsetting_id_seq owner to postgres;

alter sequence pcs.pwappsetting_id_seq owned by pcs.pwappsetting.id;

grant select, update, usage on sequence pcs.pwappsetting_id_seq to  <other_user1>; ;

For Generator connection I use the very same "postgres" user with the jooq DefaultGenerator, generate.sequences = true, and init schema "pcs":

database {
   name = 'org.jooq.meta.postgres.PostgresDatabase'
    inputSchema = 'pcs'
    [..]

}

Problem arise when I tried to generare the JOOQ Java objecs. Tables, Pojos and Records are created as expected but sequences are not created at all.

From IntelliJ Idea sql client I can see 4 sequences in the schema pcs:


But If I run the generator in debug, it can see only one of them (I suspect it could be the pwdwg_tech_id since is the only one excplicity created and not related to any serial field):

14:44:02 INFO Generating catalogs : Total: 1
14:44:03 INFO ARRAYs fetched : 0 (0 included, 0 excluded)
14:44:03 INFO Domains fetched : 0 (0 included, 0 excluded)
14:44:03 INFO Tables fetched : 44 (7 included, 37 excluded)
14:44:03 INFO Embeddables fetched : 0 (0 included, 0 excluded)
14:44:03 INFO Enums fetched : 0 (0 included, 0 excluded)
14:44:03 INFO Packages fetched : 0 (0 included, 0 excluded)
14:44:03 INFO Routines fetched : 28 (0 included, 28 excluded)
14:44:03 INFO Sequences fetched : 1 (0 included, 1 excluded)
14:44:03 INFO No schema version is applied for catalog . Regenerating.
14:44:03 INFO
14:44:03 INFO Generating catalog : DefaultCatalog.java
14:44:03 INFO ==========================================================
14:44:03 INFO Generating schemata : Total: 1
14:44:03 INFO No schema version is applied for schema pcs. Regenerating.
14:44:03 INFO Generating schema : Pcs.java

My Guess is that this can be related to permission/ownership or with the fact that sequences are autogenerated for serial field, but I'm not able to make generation works anyhow.

Any help on this, please?

Thank you very much.

[Lukas Eder]

Thank you very much for your message.

The default value for the <includeSystemSequences/> flag is "false". You can change that as documented here:

https://www.jooq.org/doc/latest/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-include-object-types/

I 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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/1822121d-307d-4032-bdbc-0a12dd2874bbn%40googlegroups.com.