Re: AutoVacuum starvation from sinval messages

On Thu, Nov 8, 2012 at 6:23 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> writes:
>> On 8 November 2012 20:36, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>> It does not seem outrageous to me that there would be real-world
>>> conditions in which invalidations would be sent more than once a
>>> minute over prolonged periods, so this total starvation seems like a
>>> bug.
>
>> Yes, its a bug, but do you really believe the above? In what cases?
>
> It doesn't take a whole lot of DDL to provoke an sinval overrun, if
> the recipient process is just sitting idle and not servicing the
> messages. I think Jeff's concern is entirely valid.

So, do we need a sinval overrun or just a sinval message to provoke
starvation? The former would be bad but the latter would be really,
really bad. IIRC the queue has 4K entries, and IIRC a single DDL
operation might provoke a couple of sinvals, but I'm thinking that
somebody would probably have to be creating >1024 temp tables a minute
to overrun the queue, which is very possible but not necessarily
common. OTOH, creating 1 temp table a minute would hit a much broader
swath of users.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company