Re: Request: pg_cancel_backend variant that handles 'idle in transaction' sessions

On Mon, Nov 2, 2015 at 1:23 PM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:
> On 11/2/15 11:15 AM, Pavel Stehule wrote:
>>
>> I have not strong idea about how to solve it well - maybe introduce
>> transaction_idle_timeout and session_idle_timeout?
>
>
> Yes, please. This is a very common problem. I would love a better way to
> detect (or prevent) clients from being brain-dead about how they're using
> transactions, but short of that this is the next best thing.
>
> Actually, one other thing that would help is to have the ability to turn
> this into an ERROR:
>
> begin;
> WARNING: there is already a transaction in progress

curious: does the SQL standard define this behavior?

Anyways, we've pretty studiously avoided (minus a couple of
anachronisms) .conf setting thats control behavior of SQL commands in
a non performance way.

IMO, this as yet another case for 'stored procedures' that can manage
transaction state: you could rig up your own procedure: CALL
begin_tx_safe(); which would test transaction state and fail if
already in one. This doesn't help you if you're not in direct control
of application generated SQL but it's a start. Barring that, at least
warnings tend to stand out in the database log.

merlin