Re: Size of Postgres Transaction Logs

At 12:45 PM 5/18/01 -0400, Tom Lane wrote:
>
>The WAL log should be auto-truncated at checkpoints, assuming that the
>old entries are no longer needed. However, if you leave uncommitted
>transactions sitting around, their WAL entries can't be deleted until
>you commit or abort them. I'd only expect massive WAL growth if you
>have very long-running transactions ...

Uhoh.

Scenario 1:
Using FCGI, persistent DB connections and Perl DBI.

The problem I see is that the Perl DBI doesn't have a BEGIN transaction,
can only rollback or commit, which implicitly begins a new transaction.

So what happens typically is:
1) open DB connection.
2) wait for http request.
3) rollback (to implicitly begin a new transaction so that 'now' isn't
years ago :) ).
4) do DB stuff
5) rollback/commit.

Would we get massive WAL growth if the long running transactions are
inactive (stuck at step 2) - not doing anything, but other transactions are
active?

Scenario 2:
search/query engine - only selects are done, so db connection and
transaction held open for long periods. Lots of selects being done.

Any issues with this?

Cheerio,
Link.