Liquibase not updating stored procedures on deployment

General Discussion
1

Hello,

I’m having a very strange issue that this started happening. When I run a liquibase update to postgresDB the stored procedures are not updated. The ChangeSet is set to runOnChange=“true”. It was working for a few months, then I noticed that DB did not have the latest changes.

I tried to run:
#liquibase clearCheckSums
#liquibase updateSQL

It displays all the changes and the stored procedures to re-run, however it does not update the stored procedures. No errors are thrown. Runs as expected.

Here is the changeLog.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
    <includeAll path="com/company/schema" /> 

    <changeSet id="Create-ProcedureSchema" author="Kevin Morland" runOnChange="true" dbms="postgresql">
        <sqlFile path="com/company/procedures/CreateProcedureSchema.sql" splitStatements="false"/>
    </changeSet>

    <changeSet id="Create-CancelEvent" author="Kevin Morland" runOnChange="true" dbms="postgresql">
        <sqlFile path="com/company/procedures/cancel-event/CancelMX-Event.sql" splitStatements="false"/>
    </changeSet>
    
    <changeSet id="Create-CancelEventRequest" author="Kevin Morland" runOnChange="true" dbms="postgresql">
        <sqlFile path="com/company/procedures/cancel-event/CancelMX-EventRequest.sql" splitStatements="false"/>
    </changeSet>

    <changeSet id="Create-ProcedureSchema" author="Kevin Morland" runOnChange="true" dbms="postgresql">
        <sqlFile path="com/company/CreateProcedureSchema.sql" splitStatements="false"/>
    </changeSet>
    
    <changeSet id="Create-ParentEvent-StoredProcedure" author="Kevin Morland" runOnChange="true" dbms="postgresql">
        <sqlFile path="com/company/procedures/CreateParentEvent.sql" splitStatements="false"/>
    </changeSet>

    <changeSet id="Create-ChildEvent-StoredProcedure" author="Kevin Morland" runOnChange="true" dbms="postgresql">
        <sqlFile path="com/company/procedures/CreateChildEvent.sql" splitStatements="false"/>
    </changeSet>

    <changeSet id="Create-ParticipationMethod-StoredProcedure" author="Kevin Morland" runOnChange="true" dbms="postgresql">
        <sqlFile path="com/company/procedures/SaveParticipationMethod.sql" splitStatements="false"/>
    </changeSet>

    <changeSet id="Create-AdditionalalRight-StoredProcedure" author="Kevin Morland" runOnChange="true" dbms="postgresql">
        <sqlFile path="com/company/procedures/SaveAdditionalRight.sql" splitStatements="false"/>
    </changeSet>

    <changeSet id="Create-AgendaItem-StoredProcedure" author="Kevin Morland" runOnChange="true" dbms="postgresql">
        <sqlFile path="com/company/procedures/SaveAgendaItem.sql" splitStatements="false"/>
    </changeSet>
    
    <changeSet id="Create-EventSecurityIdentifier-StoredProcedure" author="Kevin Morland" runOnChange="true" dbms="postgresql">
        <sqlFile path="com/company/procedures/CreateEventSecurityIdentifier.sql" splitStatements="false"/>
    </changeSet>

    <changeSet id="Create-EventSecurityAgendaProfile-StoredProcedure" author="Kevin Morland" runOnChange="true" dbms="postgresql">
        <sqlFile path="com/company/procedures/CreateEventSecurityAgendaProfile.sql" splitStatements="false"/>
    </changeSet>

    <changeSet id="Update-ParentEvent-StoredProcedure" author="Kevin Morland" runOnChange="true" dbms="postgresql">
        <sqlFile path="com/company/procedures/UpdateParentEvent.sql" splitStatements="false"/>
    </changeSet>

    <changeSet id="Update-ChildEvent-StoredProcedure" author="Kevin Morland" runOnChange="true" dbms="postgresql">
        <sqlFile path="com/company/procedures/UpdateChildEvent.sql" splitStatements="false"/>
    </changeSet>

</databaseChangeLog>

Here is a sample Stored Procedure:

DROP PROCEDURE IF EXISTS realtime_procedures.create_child_event;

CREATE OR REPLACE PROCEDURE realtime_procedures.create_child_event(
    p_parent_event_id in realtime_schema.event.id%type,
    p_json_data in jsonb,
    p_is_parent_partial_match in realtime_schema.event.is_parent_partial_match%type,
    p_modified_by in realtime_schema.event.created_by%type,
    out_event_id inout realtime_schema.event.id%type,
    p_is_first_source in realtime_schema.event.is_first_source%type DEFAULT 0::smallint
)
LANGUAGE plpgsql AS $$
DECLARE
    l_event_id realtime_schema.event.id%type;
    l_sender_bic realtime_schema.event.sender_bic%type := p_json_data->>'senderBIC';
    l_application_source realtime_schema.event.application_source%type := p_json_data->>'applicationSource';
    l_type realtime_schema.event.type%type;
BEGIN
    /*
        Saves the Child Event, the parent_id is provided.
        Returns the event UUID of the child
    */
    if (l_sender_bic is not null AND l_application_source = 'FINMAPP') then
           l_type := 'SUBCST';
       else
          l_type := p_json_data->>'type';
       end if;

    -- start of save event, id will be UUID
    -- Insert SQL, removed for clarity and not sure my company wants that information public.
    
    out_event_id := l_event_id;
END;
$$;

Thank you, for your time. If you need more information, let me know.

Thanks,
Kevin Morland

2

So when you run updateSQL Liquibase produces the sql to recreate the procedure, but if you run update it doesn’t actually recreate the procedure?

Can you provide the Liquibase output for both updateSQL and update, with logLevel=info or debug?

3

It will be strange if it shows up when you can

updateSQL

but not

update

command.

Sometimes having runAlways=true along with runOnChange=true helps.

4

Thank you guys for the help and suggestion. The solution was resolved with attribute runAlways=true.

Thanks,
Kevin

1 Like