From: | David Rader <david(dot)rader(at)gmail(dot)com> |
---|---|
To: | Dave Cramer <pg(at)fastcrypt(dot)com> |
Cc: | pgsql-jdbc(at)lists(dot)postgresql(dot)org |
Subject: | Re: Procedure support improvements |
Date: | 2019-07-24 01:59:52 |
Message-ID: | CAOcA-5-Z7nrSa9WOc4EmjTfdoOq1rFunT2h34kPa06X_PbaPew@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-jdbc |
On Tue, Jul 23, 2019 at 4:37 PM Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
> Hmmm who knew you couldn't call a procedure inside a transaction. That
> just seems broken
>
>
Yeah, the description in the docs is:
"Transaction control is only possible in CALL or DO invocations from the
top level or nested CALL or DO invocations without any other intervening
command. "
https://www.postgresql.org/docs/11/plpgsql-transactions.html
Which means to be able to call procedures that use commit or rollback you
have to be able to call them without a begin...
> Dave Cramer
>
> davec(at)postgresintl(dot)com
> www.postgresintl.com
>
>
> On Sun, 21 Jul 2019 at 13:31, David Rader <david(dot)rader(at)gmail(dot)com> wrote:
>
>> 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?
>>
>>
>>
>>
>>
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2019-07-24 02:16:26 | pg_basebackup delays closing of stdout |
Previous Message | Michael Paquier | 2019-07-24 01:56:36 | Re: Race conditions with TAP test for syncrep |
From | Date | Subject | |
---|---|---|---|
Next Message | Dave Cramer | 2019-07-24 11:09:02 | Re: Procedure support improvements |
Previous Message | Dave Cramer | 2019-07-23 20:37:27 | Re: Procedure support improvements |