javapostgresqlviewmigrationliquibase

View created fine before, now fails every time on Postgres using Liquibase


This is the view:

SELECT DISTINCT d.comic_book_id   as comic_book_id,
                            d.comic_detail_id as comic_detail_id,
                            d.archive_type    as archive_type,
                            d.comic_state     as comic_state,
                            CASE
                                WHEN EXISTS(SELECT *
                                            FROM comic_metadata_sources s
                                            WHERE s.comic_book_id = d.comic_book_id)
                                    THEN false
                                ELSE true END AS is_unscraped,
                            d.comic_type      as comic_type,
                            d.publisher       as publisher,
                            d.series          as series,
                            d.volume          as volume,
                            d.issue_number    as issue_number,
                            d.description     as description,
                            d.notes           as notes,
                            (SELECT RIGHT(CONCAT('0000000000', d.issue_number), 10)) as sortable_issue_number, d.title as title, (
            SELECT COUNT(*)
            FROM comic_pages cp
            WHERE cp.comic_book_id = d.comic_book_id) as page_count
                , d.cover_date as cover_date
                , CASE WHEN d.cover_date IS NULL THEN 0 ELSE MONTH (cover_date)
            END
            as month_published
                    , CASE WHEN d.cover_date IS NULL THEN 0 ELSE YEAR (cover_date)
            END
            as year_published,
                            d.store_date as store_date,
                            d.added_date as added_date
            FROM comic_details d

In v2 of our project, this view created and worked just fine.

I'm currently working on v3 of the project, and part of the requirement is to provide a new set of migrations to let users create the tables from scratch and have them look identical to what an existing user would get. So I copied the contents of the view from our v2 codebase into the migration for v3.

When I run the migration on all other supported databases (H2, MySQL, Mariadb) it, and all related operations, works fine.

But, when I run it on Postgres, it errors out with:

Caused by: org.postgresql.util.PSQLException: ERROR: function month(date) does not exist
  Hint: No function matches the given name and argument types. You might need to add explicit type casts.

But, when I go back to our v2 code and run the exact same Liquibase migration content it works just fine, no problems. I even copied the body of the old migration into the new one and it fails. But the old code still works on the same Postgres instance and creates a working view.

I tried replacing the call to month() and year() with:

THEN SELECT EXTRACT(MONTH FROM cover_date)

and:

THEN SELECT EXTRACT(YEAR FROM cover_date)

The view gets created fine then, but the runtime replaces them with a call to MONTH() and YEAR() respectively and Postgres again gives the same error message.

Another difference between our v2 and v3 code is that v3 is that we migrated from Spring Boot 3.2.3 to 3.4.1. I'm not sure if the Liquibase version changed between them, but could there be some kind of bug in the newer LB version? I'm assuming Spring bumped it to a newer version.


Solution

  • Ultimately I modified the view to use:

    EXTRACT(month from cover_date)
    

    And found that one of the classes had an instance variable that uses was annotated with a formula that was also using the month() function. Updating that to use the above fixed all the issues.