Jeff Frost <jeff@pgexperts.com> writes:
>> On Jun 30, 2014, at 4:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> Did you check whether the locks were all on temp tables of the
>>> ON COMMIT DROP persuasion?
> And indeed it did catch up overnight and the lag increased shortly after a correlating spike in AccessExclusiveLocks
thatwere generated by temp table creation with on commit drop.
OK, so we have a pretty clear idea of where the problem is now.
It seems like there are three, not mutually exclusive, ways we might
address this:
1. Local revisions inside StandbyReleaseLocks to make it perform better in
the presence of many locks. This would only be likely to improve matters
much if there's a fixable O(N^2) algorithmic issue; but there might well
be one.
2. Avoid WAL-logging AccessExclusiveLocks associated with temp tables, on
the grounds that no standby should be touching them. I'm not entirely
sure that that argument is bulletproof though; in particular, even though
a standby couldn't access the table's data, it's possible that it would be
interested in seeing consistent catalog entries.
3. Avoid WAL-logging AccessExclusiveLocks associated with
new-in-transaction tables, temp or not, on the grounds that no standby
could even see such tables until they're committed. We could go a bit
further and not take out any locks on a new-in-transaction table in the
first place, on the grounds that other transactions on the master can't
see 'em either.
It sounded like Andres had taken a preliminary look at #1 and found a
possible avenue for improvement, which I'd encourage him to pursue.
For both #2 and the conservative version of #3, the main implementation
problem would be whether the lock WAL-logging code has cheap access to
the necessary information. I suspect it doesn't.
The radical version of #3 might be pretty easy to do, at least to the
extent of removing locks taken out during CREATE TABLE. I suspect there
are some assertions or other consistency checks that would get unhappy if
we manipulate relations without locks, though, so those would have to be
taught about the exception. Also, we sometimes forget new-in-transaction
status during relcache flush events; it's not clear if that would be a
problem for this.
I don't plan to work on this myself, but perhaps someone with more
motivation will want to run with these ideas.
regards, tom lane