Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

On 8/8/19 11:35 AM, Bryn Llewellyn wrote: Please try the following. Stop PostgreSQL with this: pg_ctl -D /usr/local/var/postgres stop Edit the file "/usr/local/var/postgres/postgresql.conf” and add these lines at the end: log_statement = 'all' log_directory = 'log' logging_collector = on T

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

Please try the following.Stop PostgreSQL with this:pg_ctl -D /usr/local/var/postgres stopEdit the file "/usr/local/var/postgres/postgresql.conf” and add these lines at the end:log_statement = 'all'log_directory = 'log'logging_collector = onThen start  PostgreSQL with this:pg_ctl -D /usr/local/var/p

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

Bryn Llewellyn wrote: > B.t.w., I’m guessing that the “begin” SQL command that you see in the log > that I mentioned is actually issued by (some) clients—at least psql and > Python-on-psycopg2—as an explicit call from the client. In other words, it > isn’t the server that generates this. D

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

On Wed, Aug 7, 2019 at 11:36 PM Benedict Holland wrote: > > Also, I have never seen a rollback at the start of a proc. A pure > hypothetical is that it is doing nothing or definitely not what you think it > is. That's my point, thanks. Luca

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

Thanks David. I’m relieved to hear that I’m not the only one who finds it hard to predict the behavior of some things in PostgreSQL just from reading the docs. Hypothesising and experimenting seem to be needed for the trickier cases. You use the phrase "nested transaction”. This Google search ge

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

Thanks, Adrian. On 07-Aug-2019, at 13:19, Adrian Klaver wrote: On 8/7/19 12:54 PM, Bryn Llewellyn wrote: > Yes, I do believe that I understand this. But there’s no getting away from > the fact that the AUTOCOMMIT mode, and what this implies, is a server-side > phenomenon—at least as several Po

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

All stored procedures run in their own transaction. My guess is that when you turn off autocommit, you are not committing something after your transaction ends. Also, I have never seen a rollback at the start of a proc. A pure hypothetical is that it is doing nothing or definitely not what you thin

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

On Wed, Aug 7, 2019 at 12:18 PM Luca Ferrari wrote: > On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn wrote: > > > B.t.w., I noticed that “set transaction isolation level serializable” > must be the very first statement after “rollback” (or “commit”). Even an > invocation of txid_current() after t

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

On 8/7/19 12:54 PM, Bryn Llewellyn wrote: Yes, I do believe that I understand this. But there’s no getting away from the fact that the AUTOCOMMIT mode, and what this implies, is a server-side phenomenon—at least as several PostgreSQL experts have assured me. For example, when you use client-sid

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

Yes, I do believe that I understand this. But there’s no getting away from the fact that the AUTOCOMMIT mode, and what this implies, is a server-side phenomenon—at least as several PostgreSQL experts have assured me. For example, when you use client-side Python with the psycopg2 driver, then onc

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

On Wed, Aug 7, 2019 at 8:28 PM Bryn Llewellyn wrote: > About your “I believe there is no reason ever to begin a procedure with a > rollback”, I already explained why I did that. My higher goal is to take > advantage of the “serializable” isolation level to safely enforce a multi-row > data rule

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

On 8/7/19 11:46 AM, Bryn Llewellyn wrote: Thanks for your response, Kyotaro. I’m happy, now, to accept the rule that “call proc_that_does_txn_control()” is legal only when AUTOCOMMIT is ON. Esp. when I’m told (on twitter, by 2ndQuadrant’s Peter Eisentraut, that this rule is “an implementation r

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

Thanks for your response, Kyotaro. I’m happy, now, to accept the rule that “call proc_that_does_txn_control()” is legal only when AUTOCOMMIT is ON. Esp. when I’m told (on twitter, by 2ndQuadrant’s Peter Eisentraut, that this rule is “an implementation restriction, for the most part.” See HERE <

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

I have a version of the code that I attached with my first email in this thread where I’ve added “raise notice” invocations to show the txid as my p1() and p2() execute. Everything that I see is consistent with what I wrote separately in reply to adrian.kla...@aklaver.com

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

On 8/7/19 12:15 AM, Luca Ferrari wrote: On Wed, Aug 7, 2019 at 12:19 AM Bryn Llewellyn wrote: 1. my call p2() starts a txn. Luca P.S: I don't believe that asking on a public mailing list for a company to answer is the right thing to do, you can always ask themselves on your own. Sort of m

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

Hello, Bryn. At Tue, 6 Aug 2019 15:18:51 -0700, Bryn Llewellyn wrote in > Here’s how I’ve tried to describe what I see for p2() with AUTOCOMMIT ON for > myself: > > 1. my call p2() starts a txn. > > 2. However, during the execution of the proc, the usual autocommit behavior > is programmati

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

On Wed, Aug 7, 2019 at 12:19 AM Bryn Llewellyn wrote: > 1. my call p2() starts a txn. In my opinion this is the point: a procedure must be "owner" of the transaction to issue transaction control statements. You can watch different behaviors placing here and there txid_current() before and within

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

Thanks Adrian. My aim with p1() was to show that its behavior, in each AUTOCOMMIT mode, aligns with my intuition. I’ve noticed the system-generated “begin” that you mention when I add this to my “/usr/local/var/postgres/postgresql.conf”: log_statement = 'all' log_directory = 'log' logging_colle

Re: Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

On 8/6/19 1:17 PM, Bryn Llewellyn wrote: I read this blog post *PostgreSQL 11 – Server-side Procedures — Part 1  and Part 2 * I

Why must AUTOCOMMIT be ON to do txn control in plpgsql procedure?

I read this blog postPostgreSQL 11 – Server-side Procedures — Part 1 and Part 2It starts with “Thanks to the work done by 2ndQuadrant contributors, we now have the ability to write Stored Procedures in PostgreSQL… once PostgreSQL 11 comes out”. It focuses on doing txn control from a stored proc.In