On Thu, May 18, 2017 at 11:07 AM, Karl O. Pinc <kop@meme.com> wrote:
> I forget all the details, but some time ago I found
> that I had to increase max_pred_locs_per_transaction.
> What I recall about the reason for this is that I'm
> using the serializable transaction isolation, and that
> I've a test database which occasionally has extremely
> long running transactions. The PG serializable
> snapshot isolation implementation at the time (9.1?)
> was holding predicate locks across all databases
> during transactions. This even though databases
> are independent of each other. The long transaction
> times in the test database lead to predicate lock
> exhaustion in production databases -- only a single
> transaction would be executing in the test database
> but many would occur in the production databases.
> (I don't know if there was potential for other bad effects
> due to the production transactions "hanging around" until the
> transaction in the test db finished.)
>
> My question is whether this has changed. Does PG
> now pay attention to database in it's SSI implementation?
Well, it pays attention as far as the scope of each lock, but there
is only one variable to track how far back the oldest transaction ID
for a running serializable transaction goes, which is used in
cleanup of old locks. I see your point, and it might be feasible to
change that to a list or map that tracks it by database; but I don't
even have a gut feel estimate for the scale of such work without
investigating it. Just out of curiosity, what is the reason you
don't move the production and test databases to separate instances?
If nothing else, extremely long-running transaction in one database
can lead to bloat in others.
> Thanks for the help and apologies if I'm not framing
> the question perfectly. It's not often I think about
> this.
No sweat -- your concern/question is perfectly clear. It's the
first time I've heard of someone with this particular issue, so at
this point I'm inclined to recommend the workaround of using a
separate cluster; but if we get other reports it might be worth
adding to the list of enhancements that SSI could use.
Thanks!
--
Kevin Grittner
VMware vCenter Server
https://www.vmware.com/