Versions Compared

Version Old Version 14 New Version Current
Changes made by

Amy Smith

Allissa Balint

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Non-transient—In-place editing, packaging, and no archiving of database changes so they can be managed similar to application code. This applies to the following object types:

  • Procedures (- when scripts are placed in the procedure folder)

  • Functions - when scripts are placed in the function folder

  • Views (- when scripts are placed in the view folder)

  • Triggers ( - when scripts are placed in the trigger folder)

Transient—Database changes are archived after packaging (all other folders).

Info

PostgreSQL packaging doesn't support package,packagebody, and sqlplus folders.

Packager requires that stored logic scripts (procedure, function, view, trigger) have only one object or statement per file with nothing preceding or following the object definition. No additional statements such as grants are allowed. In current implementation precise rollback is not completely supported for case when procedure is overloaded (if there is a need to use overloaded procedures [same names, different arguments], please use sql_direct.)

Objects and Packaging Folders

...

Operation and Object Type

Packaging Folders (fixed folders)

Package Methods (flexible folders)

Archived

Notes

CREATE/ALTER/DROP DATABASE LINK

sql_direct

direct

Yes

CREATE/ALTER/DROP FUNCTION

sql_directfunctiondirect

storedlogic

YesNo

CREATE/ALTER/DROP INDEX

ddl, ddl_direct, sql_direct,

convert, ddl_direct, direct

Yes

CREATE/ALTER/DROP PROCEDURE

(not overloaded)*

procedure

storedlogic

No

In case, if procedure has multiple variants (is overloaded), use sql_direct folder.

CREATE/ALTER/DROP QUEUE

sql_direct

direct

Yes

CREATE/ALTER/DROP SEQUENCE

ddl, ddl_direct, sql_direct

convert, ddl_direct, direct

Yes

CREATE/ALTER/DROP SYNONYM

ddl, ddl_direct, sql_direct

convert, ddl_direct, direct

Yes

For ALTER and CREATE OR REPLACE synonym use ddl_direct or sql_direct/direct. (Do not use ddl/convert with ALTER and CREATE OR REPLACE synonym.)

CREATE/ALTER/DROP TABLE

ddl, ddl_direct, sql_direct

convert, ddl_direct, direct

Yes

For PARTITIONED TABLE changes, use sql_direct folder.

CREATE/ALTER/DROP TRIGGER

trigger

storedlogic

No

CREATE/ALTER/DROP TYPE

sql_direct

direct

Yes

CREATE/ALTER/DROP VIEW

view

storedlogic

No

CREATE/ALTER/DROP VIEW INDEX

ddl, ddl_direct, sql_direct

convert, ddl_direct, direct

Yes

RENAME <DB_OBJECT>

sql_direct

direct

Yes

...

Operation

Packaging Folder (fixed folders)

Package Method (flexible folders)

Archived

INSERT, UPDATE, DELETE, SELECT

data_dml

data_dml

Yes

GRANT, REVOKE

sql_direct

direct

Yes

Info

PostgreSQL Stored Logic recommendations

  • We recommend using individual files for each function variant (functions with same name but different number of arguments). These individual files have their own versioned history.

  • To rename a parameter you must follow these steps:

    • Modify <function_script_name>.sql file to only contain a DROP statement: DROP FUNCTION <namespace>.<function_name>(type1, type2, ..., typeN)

    • Run the packager to get the (N + 1) version changeset which would have the dropFunction change.

    • Modify <function_script_name>.sql again to contain the new CREATE OR REPLACE FUNCTION statement.

    • Run the packager again, then the (N + 2) version change would create a function with new parameter(s) name(s).

  • SQL triggers are not natively supported in PostgreSQL. Instead, you must use the embedded surrogate type TRIGGER. PostgreSQL requires a trigger object and a trigger function for this process to be successful. The function contains the logic of the trigger and returns the type of TRIGGER. This trigger has a reference on table and action qualifier and explicitly calls the trigger function. Liquibase Enterprise also needs two stored logic files:
    sql_code/function/<trigger_function_script_name>.sql and sql_code/trigger/<trigger_script_name>.sql.

    • We also recommend to commit the trigger function script first and then the trigger script to avoid the ‘no such function exist’ error while running the packager.

EnableRollbackOnError Support

Starting in 8.10, you can use enableRollbackOnError=true. The implementation of this is slightly different compared to other clients.

It is enabled by:

  • Using set ON_ERROR_STOP to on

  • Wrapping SQL into transaction by adding BEGIN; and COMMIT

Due to this approach, there are limitations:

  • Some SQL statements can’t be executed inside of transactions, such as create database, drop database, and reindex.

  • If you use your own transaction inside of a script, it may not work as expected. PostgreSQL ignores a BEGIN if one is already active, which may cause mismatched transaction scopes or errors.

Error Handling

Do not put error-handling statements in the SQL scripts.

...