Re: Database disconnection and switch inside a single bgworker

On Fri, Nov 15, 2013 at 10:14 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Michael Paquier <michael(dot)paquier(at)gmail(dot)com> writes:
>> Currently, bgworkers offer the possibility to connect to a given
>> database using BackgroundWorkerInitializeConnection in bgworker.h, but
>> there is actually no way to disconnect from a given database inside
>> the same bgworker process.
>
> That's isomorphic to having a backend switch to a different database,
> which occasionally gets requested, but there is no real likelihood
> that we'll ever implement. The problem is, how can you be sure you
> have flushed all the database-specific state that's been built up?
> The relcache and catcaches are only the tip of the iceberg; we've
> got caches all over the place. And once you had flushed that data,
> you'd have to recreate it --- but the code for doing so is intimately
> intertwined with connection startup tasks that you'd most likely not
> want to repeat.
>
> And, once you'd done all that work, what would you have? A database
> switch methodology that would save a fork(), but not much else.
> The time to warm up the caches wouldn't be any better than in a
> fresh process.

Well, you'd have whatever backend-local state you had accumulated
apart from stuff in the caches. It's clearly not useless, especially
for background workers. And you might actually save a little bit,
because I think we established previously that a good fraction of the
startup cost was actually page faults, which would not need to be
re-incurred. But that having been said...

> The cost/benefit ratio for making this work just doesn't look very
> promising. That's why autovacuum is built the way it is.

...yeah.

From a performance point of view, what's a bit frustrating is that we
have to reload a bunch of information that probably isn't different,
like pg_am entries, and the pg_class and pg_attribute entries for
pg_class and pg_attribute themselves. If we could figure out some way
to avoid that, the potential performance win here would be bigger.
But it's not obvious to me that it's a good place to spend development
time; even if it worked perfectly, the overhead of forking new
backends just doesn't seem like our biggest problem right now.

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