Hint: Use DROP AGGREGATE to drop aggregate functions.

[ShadowNinjaHunter]

Search first

• [X] I searched and no similar issues were found

Description

The database is progres.

2024-01-28 19:15:24] FINE [liquibase.executor] DROP FUNCTION "dev"."sum" Error occurred during dropAll: ERROR: "dev.sum" is an aggregate function Hint: Use DROP AGGREGATE to drop aggregate functions. [Failed SQL: (0) DROP FUNCTION "dev"."sum"] It is possible that not all objects were dropped.

[2024-01-28 19:15:24] INFO [liquibase.ui] Error occurred during dropAll: ERROR: "dev.sum" is an aggregate function Hint: Use DROP AGGREGATE to drop aggregate functions. [Failed SQL: (0) DROP FUNCTION "dev"."sum"] It is possible that not all objects were dropped.

[2024-01-28 19:15:24] SEVERE [liquibase.command] Error occurred during dropAll: ERROR: "dev.sum" is an aggregate function Hint: Use DROP AGGREGATE to drop aggregate functions. [Failed SQL: (0) DROP FUNCTION "dev"."sum"] It is possible that not all objects were dropped.

liquibase.exception.DatabaseException: ERROR: "dev.sum" is an aggregate function Hint: Use DROP AGGREGATE to drop aggregate functions. [Failed SQL: (0) DROP FUNCTION "dev"."sum"] at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:470) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:77) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:179) at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:147) at liquibase.database.AbstractJdbcDatabase.dropDatabaseObjects(AbstractJdbcDatabase.java:831) at liquibase.command.core.DropAllCommandStep.run(DropAllCommandStep.java:65) at liquibase.command.CommandScope.execute(CommandScope.java:217) at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:55) at liquibase.integration.commandline.CommandRunner.call(CommandRunner.java:24) at picocli.CommandLine.executeUserObject(CommandLine.java:1953) at picocli.CommandLine.access$1300(CommandLine.java:145) at picocli.CommandLine$RunLast.executeUserObjectOfLastSubcommandWithSameParent(CommandLine.java:2352) at picocli.CommandLine$RunLast.handle(CommandLine.java:2346) at picocli.CommandLine$RunLast.handle(CommandLine.java:2311) at picocli.CommandLine$AbstractParseResultHandler.execute(CommandLine.java:2179) at picocli.CommandLine.execute(CommandLine.java:2078) at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$2(LiquibaseCommandLine.java:391) at liquibase.Scope.child(Scope.java:195) at liquibase.Scope.child(Scope.java:171) at liquibase.integration.commandline.LiquibaseCommandLine.lambda$execute$3(LiquibaseCommandLine.java:366) at liquibase.Scope.child(Scope.java:195) at liquibase.Scope.child(Scope.java:171) at liquibase.integration.commandline.LiquibaseCommandLine.execute(LiquibaseCommandLine.java:363) at liquibase.integration.commandline.LiquibaseCommandLine.main(LiquibaseCommandLine.java:103) Caused by: org.postgresql.util.PSQLException: ERROR: "dev.sum" is an aggregate function Hint: Use DROP AGGREGATE to drop aggregate functions. 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.execute(PgStatement.java:290) at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:464) ... 23 more

Steps To Reproduce

So this is the function created:

CREATE AGGREGATE dev.sum(boolean) ( SFUNC = sumboolmaj, STYPE = integer, FINALFUNC = sumboolfin, INITCOND = 0 );

run ./gradlew dropAll

Expected/Desired Behavior

that the AGGREGATE is deleted without error

Liquibase Version

commercial 4.25.1

Database Vendor & Version

postgres 14.10

Liquibase Integration

gradle

Liquibase Extensions

No response

OS and/or Infrastructure Type/Provider

ubuntu

Additional Context

No response

Are you willing to submit a PR?

• [ ] I'm willing to submit a PR (Thank you!)

[tati-qalified]

Hi @ShadowNinjaHunter, thank you for reporting this issue. I've been able to replicate it with Liquibase v4.25.1 and v4.26.0.

Currently Liquibase doesn't support aggregate functions for the drop-all command.

I can think of two workarounds for this:

1. Rolling back the changesets where the functions were created. This has to be done before running drop-all, as otherwise the databasechangelog table will have been dropped. You can see more information about rollbacks here, at the 9th list item.

2. Get the function signatures and manually write a SQL script with them. The syntax would be DROP AGGREGATE functionName(parameters). You can get the signatures either from your changelog files, if you still have access to them, or by running liquibase generate-changelog.

Let me know if this helps or if you have any other questions.

Thank you, Tatiana