Thread: long transactions, SAVEPOINTS, performance and memory consumption

Hi *,

for our project we use big transactions for application coordination.
As we need to recover from failed statements on statement level we
encapsulate
our statements with SAVEPOINT/RELEASE savepoint statements.
So far this seems to be the only way to recover from failed statements
and to
keep the enclosing transaction intact.

Things we observed:
    1) For transactions with many statements we observe continuously
increasing
    memory consumption in the backend.
    2) Along with it the throughput decreases during the course of
statement processing. The
    processing is CPU bound during that time until the transaction
finishes or rather aborts.
    3) We seldom see the entire transaction commit as it is ABORTED due
to an out of memory
    condition before the application can commit.
    4) The backend server log shows MANY (in our case 15805) entries of
the form:
    CurTransactionContext: 8192 total in 1 blocks; 8176 free (2 chunks);
16 used
    This is a resource utilization of 0.2%.
    Of the 128Mb allocated data size the the backend process 96% is used
for an
    effective data amount of 252880 bytes.
    5) Looking into the source READMEs (/backend/utils/mmgr/README,
backend/access/transam/README)
    it looks like it is intentional that the backend allocates and keeps
memory for each started and commited
    sub-transaction until the transaction is finally commited at top level.

Questions:
    1) Is there another way to cope with failed statements without using
savepoint and without
    aborting the entire transaction?
    2) Would it be possible to let the parent subtransaction adopt the
state that is currently being saved
    in the memory allocated for the commited child subtransaction? If
so, it would dramatically
    enhance resource efficiency and possibly improve performance by
reducing adminitrative overhead.

Regards and thanks in advance,
  Frank Kardel & Ansgar Seiter

Frank Kardel <Frank.Kardel@Acrys.COM> writes:
> As we need to recover from failed statements on statement level we
> encapsulate
> our statements with SAVEPOINT/RELEASE savepoint statements.

How are you doing that exactly?  The style

    begin;
        savepoint x;
        ...
        release x;
        savepoint x;
        ...
        release x;
        savepoint x;
        ...
        release x;
        savepoint x;
        ...
        release x;
        ...
    commit;

works with minimal leakage AFAICT.  If you are nesting the savepoints
then of course it's going to bloat: it has to be able to recover to any
one of the open savepoints, so there has to be some amount of state
associated with each one.

If you think that's what you are doing, let's see a self-contained
example.  You might be invoking some specific feature that has a
memory leak.

It's also a good idea to mention the exact PG version you are using
in such complaints ...

            regards, tom lane