Re: Statement-level rollback

From: pgsql-hackers-owner(at)postgresql(dot)org
> [mailto:pgsql-hackers-owner(at)postgresql(dot)org] On Behalf Of Vladimir
> Sitnikov
> Tsunakawa> PgJDBC has supported the feature with autosave parameter only
> Tsunakawa> recently
>
> PgJDBC has the implementation for more than a year (REL9.4.1210, 2016-09-07,
> see https://github.com/pgjdbc/pgjdbc/pull/477 )

And I heard from someone in PgJDBC community that the autosave parameter was not documented in the manual for a while, which I confirmed. So the statement-level rollback is newer to users, isn't it?

> The performance overhead for "SELECT" statement (no columns, just select)
> statement over localhost is 36±4 us vs 38±3 us (savepoint is pipelined along
> with user-provided query). That is network overhead is close to negligible.

That's good news, because it also means that the overhead of creating a savepoint is negligible.

> As far as I understand, the main problem with savepoints is they would
> consume memory even in case the same savepoint is reassigned again and again.
> In other words, "savepoint; insert;savepoint; insert;savepoint;
> insert;savepoint; insert;savepoint; insert;" would allocate xids and might
> blow up backend's memory.
> I see no way driver can workaround that, so it would be great if backend
> could release memory or provide a way to do so.

Doesn't PgJDBC execute RELEASE after each SQL statement? That said, even with RELEASE, the server memory bloat is not solved. The current server implementation allocates a memory chunk of 8KB called CurTranContext for each subtransaction, and retains them until the end of top-level transaction. That's another (separate) issue to address.

Regards
Takayuki Tsunakawa