PostgreSQL hasn't compatibility with "Execute Stored Procedure" action

[HannaTrotsenko]

Component Bug Report

Description

Slack conversation

Due to limitations in the PostgreSQL JDBC driver, the "Execute Stored Procedure" action does not work reliably with PostgreSQL databases.

Component Version

v2.5.10

Steps to Reproduce

1. Create a stored procedure in PostgreSQL with an OUT parameter (you can use Execute custom query action):

CREATE PROCEDURE test_proc(IN input_val INT, OUT result TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
result := 'Value is ' || input_val;
END;
$$;

2. Create flow with any trigger and add the JDBC component with a valid PostgreSQL credentials.
3. Select the "Execute Stored Procedure" action.
4. In the procedure dropdown, you will see test_proc listed
5. Map the input_val with any integer
6. Retrieve sample

Actual Result

org.postgresql.util.PSQLException: ERROR: test_proc(integer) is a procedure Hint: To call a procedure, use CALL. Position: 15

Logs

Starting your Docker container ...
[INFO  tini (1)] Spawned child process 'node' with pid '7'
invokeModuleFunction – startup is not found
invokeModuleFunction – init is not found
Fully initialized and waiting for messages
Checking if incoming messages is lightweight...
Checking if incoming messages is lightweight...
Checking if incoming messages is lightweight...
Checking if incoming messages is lightweight...
Checking if incoming messages is lightweight...
About to process message...
Message processed, going to emit
[INFO  tini (1)] Spawned child process 'java' with pid '7'
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/home/apprunner/build/dependencies/sailor-jvm-4.0.3.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/home/apprunner/build/dependencies/logback-classic-1.2.13.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
java.lang.RuntimeException: org.postgresql.util.PSQLException: ERROR: test_proc(integer) is a procedure
  Hint: To call a procedure, use CALL.
  Position: 15
	at io.elastic.jdbc.actions.ExecuteStoredProcedure.execute(ExecuteStoredProcedure.java:29)
	at io.elastic.sailor.impl.MessageProcessorImpl.processMessage(MessageProcessorImpl.java:69)
	at io.elastic.sailor.impl.MessageConsumer.lambda$handleDelivery$0(MessageConsumer.java:79)
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at java.lang.Thread.run(Thread.java:750)
Caused by: org.postgresql.util.PSQLException: ERROR: test_proc(integer) is a procedure
  Hint: To call a procedure, use CALL.
  Position: 15
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
	at org.postgresql.jdbc.PgCallableStatement.executeWithFlags(PgCallableStatement.java:84)
	at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:177)
	at io.elastic.jdbc.query_builders.PostgreSQL.callProcedure(PostgreSQL.java:178)
	at io.elastic.jdbc.actions.ExecuteStoredProcedure.execute(ExecuteStoredProcedure.java:25)
	... 7 more

09:43:17,708 |-INFO in ch.qos.logback.classic.LoggerContext[default] - Could NOT find resource [logback-test.xml]
09:43:17,730 |-INFO in ch.qos.logback.core.joran.spi.ConfigurationWatchList@396f6598 - URL [jar:file:/home/apprunner/build/dependencies/sailor-jvm-4.0.3.jar!/logback.xml] is not of type file
09:43:17,886 |-INFO in ch.qos.logback.classic.joran.action.ConfigurationAction - debug attribute not set
09:43:17,904 |-INFO in ch.qos.logback.core.joran.action.AppenderAction - About to instantiate appender of type [ch.qos.logback.core.ConsoleAppender]
09:43:17,922 |-ERROR in ch.qos.logback.core.joran.spi.Interpreter@6:22 - no applicable action for [pattern], current ElementPath  is [[configuration][appender][encoder][pattern]]
09:43:18,468 |-INFO in ch.qos.logback.core.joran.action.AppenderRefAction - Attaching appender named [STDOUT] to Logger[ROOT]
09:43:18,473 |-INFO in ch.qos.logback.classic.joran.action.ConfigurationAction - End of configuration.
09:43:17,708 |-INFO in ch.qos.logback.classic.LoggerContext[default] - Found resource [logback.xml] at [jar:file:/home/apprunner/build/dependencies/sailor-jvm-4.0.3.jar!/logback.xml]
09:43:17,909 |-INFO in ch.qos.logback.core.joran.action.AppenderAction - Naming appender as [STDOUT]
09:43:18,468 |-INFO in ch.qos.logback.classic.joran.action.RootLoggerAction - Setting level of ROOT logger to INFO
09:43:18,475 |-INFO in ch.qos.logback.classic.joran.JoranConfigurator@394e1a0f - Registering current configuration as safe fallback point
(no message)
SLF4J: Actual binding is of type [ch.qos.logback.classic.util.ContextSelectorStaticBinder]
About to init Sailor
Component descriptor from classpath: /component.json
Retrieving step data at: /v1/tasks/685e67ae13329300112bda79/steps/step_8
Sending GET request to /v1/tasks/685e67ae13329300112bda79/steps/step_8
Component descriptor from classpath: /component.json
Connecting to AMQP
Connected to AMQP with thread pool of 1 threads
Opened subscribe channel
Registered a graceful shutdown hook
Processing flow step: step_8
Component id to be executed: 64a3d7d21bb50d0012dccffc
Function to be executed: executeStoredProcedure
Function Java class to be instantiated: io.elastic.jdbc.actions.ExecuteStoredProcedure
Instantiating component io.elastic.jdbc.actions.ExecuteStoredProcedure
Initializing function for execution
Subscribing to queues
Subscribed consumer amq.ctag-a18RBlbmo_Amo13kZ8nD7A. Waiting for messages to arrive ...
Sailor started
Incremented the number of messages concurrently processed to 1
messageId=ad9d781b-60f0-4aac-b33e-e6201afad04d, parentMessageId=4c6bccbf-1407-4ce3-9fbc-7b73bea07bce, threadId=2a7004df-5827-4f38-ab7e-61c249ddbab4
Sending GET request to /v1/tasks/685e67ae13329300112bda79/steps/step_8
Parsing message JSON
About to retrieve message body from storage
No id to retrieve the object from storage found
Message will be emitted as is
Component descriptor from classpath: /component.json
Connecting to connection string
Use connection defined before
Component execution failed
Pushing to exchange=59d341e9037f7200184a408b_org, routingKey=59d341e9037f7200184a408b.685e67ae13329300112bda79/debug.step_8.error_debug
Opened publish channel
Waiting for publish confirm
Successfully published data to 59d341e9037f7200184a408b_org
Reject received messages 1
Execution stats: ExecutionStats{dataCount=0, errorCount=1, reboundCount=0}
Decremented the number of messages concurrently processed to 0

Expected Result

Component works without errors

Workaround(s)

Instead of using "Execute Stored Procedure" for PostgreSQL:

Use the "Execute Query" action.

Call a stored function that returns a TABLE, e.g.:

SELECT * FROM my_function(param1, param2);