PostgreSQL (Action Flow)
Important
Any references to third-party products or services do not constitute Celonis Product Documentation nor do they create any contractual obligations. This material is for informational purposes only and is subject to change without notice.
Celonis does not warrant the availability, accuracy, reliability, completeness, or usefulness of any information regarding the subject of third-party services or systems.
Getting started with PostgreSQL
The PostgreSQL modules let you execute your custom logic directly in your database server through functions. Celonis platform loads interface of input and output parameters dynamically so each parameter can be mapped individually.
Functions
Here is a sample SQL statement that creates a function that just simply inserts the values passed in the three parameters into a table:
CREATE OR REPLACE FUNCTION insert_record_from_Celonis platform(_name character varying,_email character varying, _phone character varying)
RETURNS void
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
INSERT INTO contacts (name, email, phone) VALUES (_name, _email, _phone);
END;
$BODY$;
To learn more about creating functions, see PostgreSQL documentation.
Configuring your PostgreSQL server
Make sure the account you're using to connect to your database has read access to 'information_schema.routines' and 'information_schema.parameters' tables.
Celonis platform will make connections to your database from specifc IP addresses. Our servers are set up with valid PTR records so you can easily restrict access by allowing %.make.com.
Troubleshooting PostgreSQL
ER_LOCK_WAIT_TIMEOUT: Lock wait timeout exceeded; try restarting transaction
This error occurs when you modify the same data using multiple modules. It is caused by SQL transactions.
When any SQL module is executed then it starts the transaction (1) and the transaction is finished after the Action Flow is fully executed.If another module tries to access the same data used in another unfinished transaction (1) then it has to wait until the previous transaction (1) is finished – but it never happens because the first transaction (1) will be finished after the Action Flow is finished.
Solution
Turn on Auto-commit. It finishes (commits) every transaction immediately after the module execution is done.
Open Action Flow settings.
Enable the Auto commit checkbox.
Confirm the settings dialog by clicking the OK button.
