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
