Procedure support improvements

From: David Rader <david(dot)rader(at)gmail(dot)com>
To: pgsql-jdbc(at)lists(dot)postgresql(dot)org
Subject: Procedure support improvements
Date: 2019-07-17 11:49:16
Message-ID: CAOcA-58PFqVxri+C9rZjfG1omtXcdeEWRuY2LaS6igJ7o+4UXw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-jdbc

Hello -

Since Procedures were introduced in PG 11, the workaround to invoke them
with JDBC is to send the native "CALL proc()" SQL and let it be treated as
a SQL statement, not a specific stored routine invocation.

1) When using transaction control inside the stored proc, an exception is
generated if autoCommit is false - see example code attached.
Exception in thread "main" org.postgresql.util.PSQLException: ERROR:
invalid transaction termination

2) Output parameters are not mapped as parameters, and app code cannot use
registerOutputParameter or getInt() style retrieval. Instead, outputs are
left in the result set and app code must retrieve the result and pull,
creating a big difference between how Procedures and Functions are invoked.

I propose improving support for procedures. Either:
(1) add support for "CALL proc()" to be treated as a routine invocation so
that output parameters can be registered, no begin transaction is silently
sent from driver, and calling a procedure and calling a function would be
very similar (only differing in function still using the {call} escape
syntax.
or
(2) change the {call} syntax to optionally support procedures. {? = call}
would still be mapped to functions. Add a connection setting to control
this change, and make default false, so that default stays backwards
compatible with pre pg11 functionality.

Thoughts?

Attachment Content-Type Size
StoredProcTransTest.java application/octet-stream 2.5 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrew Dunstan 2019-07-17 12:08:15 Re: buildfarm's typedefs list has gone completely nutso
Previous Message Daniel Verite 2019-07-17 11:31:05 Re: [PATCH] vacuumlo: print the number of large objects going to be removed

Browse pgsql-jdbc by date

  From Date Subject
Next Message Sehrope Sarkuni 2019-07-17 16:59:56 [pgjdbc/pgjdbc] 51f3d0: docs: Add note to GitHub PR templates about test s...
Previous Message Craig Ringer 2019-07-11 19:36:35 [pgjdbc/pgjdbc] 08d812: Make ConnectTimeout test accept NoRouteToHostExcep...