PostgreSQL Server Database Objects and Packaging

1 Qualifying Object Names
2 PostgreSQL SCM Packaging Patterns
3 Objects and Packaging Folders
4 PostgreSQL Stored Logic recommendations
5 EnableRollbackOnError Support
6 Error Handling

Qualifying Object Names

Do not qualify objects with the database name as PostgreSQL connections represent one database at a time.
For a Single-Schema project, you don’t need to qualify object names with the schema name. If they are not qualified, Datical DB uses the default schema if it is specified.
For a Multi-Schema project, you must qualify object names with the schema name so they get deployed to the correct schema during packaging.
- Some objects, eg. triggers, do not allow schema qualification when creating the object. In this case you must use the schemaName attribute in the metadata.properties file for a schema-level folder to specify the schema. See Using the metadata.properties file.
- Schema-level folder for schemaA

PostgreSQL SCM Packaging Patterns

Datical DB packages non-transient files differently from transient files. Place scripts for PostgreSQL objects in packaging folders as follows:

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).

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

Use the specified folder for listed PostgreSQL database operations.

Operation and Object Type	Packaging Folders (fixed folders)	Package Methods (flexible folders)	Archived	Notes

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	`function`	storedlogic	No
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

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

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.

Datical DB has its own error-handling that wraps SQL scripts. Specifying error-handling within the statements is not necessary.

Deployment Packager fails with an error if it encounters statements before an object definition.

For more information about packaging workflows, packaging methods, and folders for specific types of changes, see the following pages: