A conversation with Andrew Sullivan led me to the following idea: We have a number of frontends that like to issue BEGIN immediately after COMMIT; so that if the client does nothing for awhile after finishing one transaction, the backend nonetheless sees it as being in a transaction. This creates problems for VACUUM since the open transaction prevents it from removing dead tuples.
This has been known for a long time, and so far the hackers' opinion has been that those frontends are broken and should be fixed. But AFAIK they are not getting fixed. And some important frontends are involved (I believe JDBC and DBI, and perhaps also ODBC, behave this way). Maybe it's time to think about a fix on the backend side. It seems to me that it'd be fairly easy to make BEGIN cause only a local state change in the backend; the actual transaction need not start until the first subsequent command is received. It's already true that the transaction snapshot is not frozen at BEGIN time, but only when the first DML or DDL command is received; so this would have no impact on the client-visible semantics. But a BEGIN-then- sleep-for-awhile client wouldn't interfere with VACUUM anymore. The other thing that could be thought about here is when to freeze the value of now(). Currently now() is frozen when BEGIN is received. We could keep doing that, but it seems to me it would make more sense to freeze now() when the transaction snapshot is established. In a very real sense, the transaction snapshot defines "when the transaction starts" --- so shouldn't now() agree? If we did both of these things, then the negatives of doing an early BEGIN would pretty much vanish, and we'd not need to complain that these client libraries are broken. Comments? regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster