Liquibase diff fails on "unknown function: pg_sequence_last_value()"

[sbont]

Describe the problem

I'm using Liquibase to manage database migrations. When running a diff with Hibernate it throw an error stating that the function pg_sequence_last_value is undefined. See below for the stack trace including SQL query.

To Reproduce

1. Set up a CockroachDB cluster
2. Set up a Spring Boot project with Liquibase
3. Create a database
4. Generate a changelog from database
5. Make changes to Hibernate managed entities
6. Run liquibase diff
7. Observe error Error executing SQL SELECT ns.nspname as SCHEMA_NAME, td.relname as TABLE_NAME, pa.attname as COLUMN_NAME, COALESCE(pg_sequence_last_value(c.oid::regclass) + s.seqincrement, s.seqstart) AS START_VALUE, s.seqincrement AS INCREMENT_BY FROM pg_class c JOIN pg_sequence s on c.oid = s.seqrelid JOIN pg_namespace ns on c.relnamespace = ns.oid JOIN pg_depend d ON c.oid = d.objid JOIN pg_class td ON td.oid = d.refobjid JOIN pg_attribute pa ON pa.attrelid=td.oid AND pa.attnum=d.refobjsubid WHERE c.relkind = 'S' AND d.deptype = 'a': ERROR: unknown function: pg_sequence_last_value(): function undefined being thrown

Expected behavior
SQL query successful, changelog generated includes all changes

Additional data / screenshots

Stack trace:

liquibase.exception.DatabaseException: Error executing SQL SELECT     ns.nspname as SCHEMA_NAME,     td.relname as TABLE_NAME,     pa.attname as COLUMN_NAME,     COALESCE(pg_sequence_last_value(c.oid::regclass) + s.seqincrement,       s.seqstart) AS START_VALUE,     s.seqincrement AS INCREMENT_BY FROM pg_class 
c     JOIN pg_sequence s on c.oid = s.seqrelid     JOIN pg_namespace ns on c.relnamespace = ns.oid     JOIN pg_depend d ON c.oid = d.objid     JOIN pg_class td ON td.oid = d.refobjid     JOIN pg_attribute pa ON  pa.attrelid=td.oid AND pa.attnum=d.refobjsubid WHERE c.relkind = 'S' AND d.deptype = 'a': ERROR: unknown function: pg_sequence_last_value(): function undefined
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:89)
        at liquibase.executor.jvm.JdbcExecutor.query(JdbcExecutor.java:201)
        at liquibase.executor.jvm.JdbcExecutor.query(JdbcExecutor.java:209)
        at liquibase.executor.jvm.JdbcExecutor.queryForList(JdbcExecutor.java:274)
        at liquibase.executor.jvm.JdbcExecutor.queryForList(JdbcExecutor.java:269)
        at liquibase.snapshot.jvm.ColumnAutoIncrementService.obtainSequencesInformation(ColumnAutoIncrementService.java:55)
        at liquibase.snapshot.jvm.ColumnSnapshotGenerator.setAutoIncrementDetails(ColumnSnapshotGenerator.java:219)
        at liquibase.snapshot.jvm.ColumnSnapshotGenerator.snapshotObject(ColumnSnapshotGenerator.java:80)
        at liquibase.snapshot.jvm.JdbcSnapshotGenerator.snapshot(JdbcSnapshotGenerator.java:65)
        at liquibase.snapshot.SnapshotGeneratorChain.snapshot(SnapshotGeneratorChain.java:49)
        at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:313)
        at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:456)
        at liquibase.snapshot.DatabaseSnapshot.includeNestedObjects(DatabaseSnapshot.java:369)
        at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:332)
        at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:456)
        at liquibase.snapshot.DatabaseSnapshot.includeNestedObjects(DatabaseSnapshot.java:369)
        at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:332)
        at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:456)
        at liquibase.snapshot.DatabaseSnapshot.replaceObject(DatabaseSnapshot.java:478)
        at liquibase.snapshot.DatabaseSnapshot.includeNestedObjects(DatabaseSnapshot.java:369)
        at liquibase.snapshot.DatabaseSnapshot.include(DatabaseSnapshot.java:332)
        at liquibase.snapshot.DatabaseSnapshot.init(DatabaseSnapshot.java:106)
        at liquibase.snapshot.DatabaseSnapshot.<init>(DatabaseSnapshot.java:59)
        at liquibase.snapshot.JdbcDatabaseSnapshot.<init>(JdbcDatabaseSnapshot.java:34)
        at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:214)
        at liquibase.snapshot.SnapshotGeneratorFactory.createSnapshot(SnapshotGeneratorFactory.java:187)
        at liquibase.command.core.DiffCommandStep.generateDatabaseShapshot(DiffCommandStep.java:191)
        at liquibase.command.core.DiffCommandStep.getTargetSnapshot(DiffCommandStep.java:141)
        at liquibase.command.core.DiffCommandStep.createDiffResult(DiffCommandStep.java:102)
        at liquibase.command.core.DiffCommandStep.run(DiffCommandStep.java:86)
        at liquibase.command.CommandScope.execute(CommandScope.java:195)
        at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:55)
        at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:24)
        at picocli.CommandLine.executeUserObject(CommandLine.java:2041)
        at picocli.CommandLine.access$1500(CommandLine.java:148)
        at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2461)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2453)
        at picocli.CommandLine$RunLast.handle(CommandLine.java:2415)
        at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2273)
        at picocli.CommandLine$RunLast.execute(CommandLine.java:2417)
        at picocli.CommandLine.execute(CommandLine.java:2170)
        at liquibase.integration.commandline.LiquibaseCommandLine.lambda$null$2(LiquibaseCommandLine.java:367)
        at liquibase.Scope.child(Scope.java:196)
        at liquibase.Scope.child(Scope.java:172)
        at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$3(LiquibaseCommandLine.java:332)
        at liquibase.Scope.child(Scope.java:196)
        at liquibase.Scope.child(Scope.java:172)
        at liquibase.integration.commandline.LiquibaseCommandLine.execute(LiquibaseCommandLine.java:330)
        at liquibase.integration.commandline.LiquibaseCommandLine.main(LiquibaseCommandLine.java:91)
Caused by: org.postgresql.util.PSQLException: ERROR: unknown function: pg_sequence_last_value(): function undefined
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
        at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
        at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:333)
        at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:319)
        at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:295)
        at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:244)
        at liquibase.executor.jvm.JdbcExecutor$QueryStatementCallback.doInStatement(JdbcExecutor.java:509)
        at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:77)
        ... 48 more

Environment:

• CockroachDB version v22.2.7, running a local cluster
• Server OS: Windows 11
• Client app: Liquibase using JDBC

Additional context

Jira issue: CRDB-29285

Epic CRDB-27601

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

Hoot - a bug! Though bugs are the bane of my existence, rest assured the wretched thing will get the best of care here.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-foundations (found keywords: pg_,Hibernate,Spring,Liquibase)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[rafiss]

Thanks for this report. CockroachDB does not currently implement the pg_sequence_last_value function, but we should be able to add it.

[rafiss]

In the meantime, you should be able to work around this by creating a user-defined function, but I am not sure if it will perform well.

CREATE FUNCTION pg_sequence_last_value(o OID) RETURNS INT AS $$
  SELECT last_value FROM pg_sequences s
  JOIN pg_class c ON (s.sequencename = c.relname)
  JOIN pg_namespace n ON (n.oid = c.relnamespace)
  WHERE s.schemaname = n.nspname AND c.oid = $1;
$$ LANGUAGE SQL VOLATILE