defaultValueSequenceNext use public schema instead of using schemaName from createTable tag

[nick13145]

Environment

Liquibase Version: liquibase 4.6.2

Liquibase Integration & Version: <Pick one: CLI, maven, gradle, spring boot, servlet, etc.> docker image FROM liquibase/liquibase:4.6.2

Liquibase Extension(s) & Version:

Database Vendor & Version: postgres:14.1

Operating System Type & Version:

Description

https://liquibase.jira.com/browse/CORE-3598 defaultValueSequenceNext in column tag forget schema and uses public schema instead of schema defind in createTable tag

It’s weird to use additional changeset to add constraint after table creation. Is it planned to fix it or not?

Workaround for me: using native DDL statement to create table with required constraints and sequences

Steps To Reproduce

<changeSet id="create_sequence_affiliation_seq" author="test" dbms="postgresql">
        <preConditions onFail="MARK_RAN">
            <not>
                <sequenceExists sequenceName="affiliation_seq" schemaName="sciencejournal"/>
            </not>
        </preConditions>
        <createSequence sequenceName="affiliation_seq" schemaName="sciencejournal"/>
    </changeSet>
...

<createTable tableName="affiliation" remarks="List of affiliations" schemaName="sciencejournal">
    <column name="id" type="int" remarks="ID" defaultValueSequenceNext="affiliation_seq">
        <constraints primaryKey="true" primaryKeyName="affiliation_id_pk"/>
    </column>
    <column name="name" type="varchar(${affiliation.name.size})" remarks="Name of affiliation">
        <constraints nullable="true"/>
    </column>
</createTable>

Actual Behavior

Output:

liquibase_db_migration_1 | Caused by: liquibase.exception.DatabaseException: ERROR: relation "public.affiliation_seq" does not exist liquibase_db_migration_1 | Position: 69 [Failed SQL: (0) CREATE TABLE sciencejournal.affiliation (id INTEGER DEFAULT nextval('public.affiliation_seq') NOT NULL, name VARCHAR(300), CONSTRAINT affiliation_id_pk PRIMARY KEY (id))]
liquibase_db_migration_1 | at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:397) liquibase_db_migration_1 | at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:83) liquibase_db_migration_1 | at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:151) liquibase_db_migration_1 | at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1279) liquibase_db_migration_1 | at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1261) liquibase_db_migration_1 | at liquibase.changelog.ChangeSet.execute(ChangeSet.java:660) liquibase_db_migration_1 | ... 61 more liquibase_db_migration_1 | Caused by: org.postgresql.util.PSQLException: ERROR: relation "public.affiliation_seq" does not exist liquibase_db_migration_1 | Position: 69 liquibase_db_migration_1 | at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553) liquibase_db_migration_1 | at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285) liquibase_db_migration_1 | at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323) liquibase_db_migration_1 | at org.postgresql.

Expected/Desired Behavior

sciencejournal.affiliation_seq should be used for table creation

[kataggart]

Hey @nick13145 thanks for submitting all this detail. We will review and get back to you soon.

[nvoxland]

@nick13145 does it work if you used defaultValueSequenceNext="sciencejournal.affiliation_seq" ?

[FBurguer]

@nvoxland changing defaultValueSequenceNext="affiliation_seq" to defaultValueSequenceNext="sciencejournal.affiliation_seq" got me the next error:

Unexpected error running Liquibase: ERROR: relation "public.sciencejournal.affiliation_seq" does not exist [Failed SQL: (0) CREATE TABLE sciencejournal.affiliation (id INTEGER DEFAULT nextval('public."sciencejournal.affiliation_seq"') NOT NULL, name VARCHAR(500), CONSTRAINT affiliation_id_pk PRIMARY KEY (id))]

[nick13145]

@nvoxland i tried, but I had the same issue like for @FBurguer Liquibase tried to find sequence inside public schema

[nvoxland]

Thanks for the fast reply, @nick13145. I was hoping it wouldn't just try to quote the whole thing like that.

One option could be to make it so sequence name can get fully qualified like that in the attribute name. But recognizing that it's a schema name vs. just a sequence that needs to be quoted is not great.

Using the table's schema name seems like the best approach. You won't be able to have sequences in a differnent schema, but that seems edge-case-y enough that they can use modifySql for that.

[nick13145]

Yeah, you're right. Using table schema will resolve that problem, will you plan fix it? Probably I can try to find a exact place in source code

[FBurguer]

Another workaround is specifing the schema but you have to have different changelogs, one for each schema you are working on and add to the url currentSchema=*schemaName* for each update.

[SimonHaenle2]

I can confirm this bug still exists. Just stumbled upon it with the latest liquibase-core version.