Re: High memory usage with savepoints & encoding differences

Tom Lane writes:
> I think this example is pretty artificial. The fundamental reason
> memory is increasing is that each subtransaction can require
> some state
> storage. In the example the per-subtransaction CurTransactionContexts
> are not getting used for anything except encoding conversion on the
> SAVEPOINT/RELEASE command completion messages --- but if you
> were doing
> any real work in the subtransactions then most likely there would be
> additional stuff there, so I'm not excited about trying to suppress
> this particular symptom.

I was under the impression that the majority of the storage was free'd
when the savepoint was RELEASEd.

I was trying to reduce this down to the simplest repeatable example. In
the actual code, the savepoint was bookending an INSERT. It seemed this
was a reasonable approach, given the discussion around adding automatic
savepoint logic to the JDBC driver:

http://archives.postgresql.org/pgsql-jdbc/2005-01/msg00131.php
http://archives.postgresql.org/pgsql-jdbc/2007-04/msg00085.php

The actual code (including INSERT) is fine if the database it's
executing against is UTF8 encoded, Unfortunately we can't convert to
that, due to some other legacy applications that can't handle multibyte
encodings.

I am pretty surprised how much memory the transcoding requires.

> The bottom line is that lots and lots of subtransactions isn't a very
> good thing for performance, especially with a couple of network round
> trips for each one. Consider pushing whatever work is involved here
> into a server-side function.

This was encountered as part of a migration process from an existing
DBMS. We decided to go with savepoints to replicate the "error occurred
during transaction" behavior of the previous database platform. In this
case, it'll just mean reworking the code to check for duplicates rather
than having Postgres do it for us.

Thanks again!
dylan