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