Re: BUG #13660: serializable snapshotting hangs

On Tuesday, October 6, 2015 4:47 PM, Chris Pacejo <cpacejo(at)clearskydata(dot)com> wrote:

> but I had no other open connections to that database. There were
> many connections to *other* databases on that same *server*, but
> that shouldn't affect taking a snapshot on an otherwise unused
> database, correct?

Incorrect. Like vacuum and other visibility-related operations,
long-running transactions in other databases in the same "cluster"
(in the sense of a group of backends running under a single
postmaster) will be an issue. I seem to recall that it was
non-trivial to limit things in general to a single database, but
perhaps the DEFERRABLE feature could be enhanced to do that without
too much pain.

> Also note that once this happened to one database, it happened to
> *all* databases on the same server simultaneously.

Right; that is because the check for pending transactions is not
database-sensitive.

> It's as if the read-write transaction check is performed
> server-wide, not database-wide as I would expect.

Undoubtedly that would be nicer, but it is not how the current
implementation works. Taking a quick look, since we currently have
no way (that I know of) to get the database associated with a
VirtualTransactionId, we would need to add the OID of the database
to the SERIALIZABLEXACT structure and add testing it to the tests
inside this loop:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/storage/lmgr/predicate.c;h=bad5618341e3995e538edefd17561cf6db33721e;hb=master#l1766

A change like that would need to be considered an enhancement, not
a bug fix, so it would not be something we could back-patch to
stable branches (or even, at this point, version 9.5). Given that
it is only a few straightforward lines of code, I'll see about
getting a patch into the next CommitFest so it has a shot at
getting into the release *after* 9.5. If you're comfortable doing
your own builds from source, you might want to be an "early
adopter" of this patch, once it is written and reviewed.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company