Re: Background worker/idle sessions and caching

On Wed, Jul 18, 2018 at 3:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jeremy Finzel <finzelj(at)gmail(dot)com> writes:
> > I have a background worker running SQL functions, and I believe I have
> > noticed that when I do things like change function definitions, or even
> add
> > tables, the background worker does not pick up the schema changes until I
> > restart the worker.
>
> Maybe you need some AcceptInvalidationMessages() at appropriate points
> in the worker? ProcessCatchupInterrupts() might be relevant as well,
> though if you're worried about this, you probably don't want to ever
> be so far behind as to get triggered by that.
>

My module is based squarely on worker_spi.c with some very minor
modifications. I definitely don't see any AcceptInvalidationMessages() or
ProcessCatchupInterrupts() which would run between successive
executions of SPI_execute
of the SQL that does the delta load.

>
> There might well be a system structural bug here: I'm not sure whether
> bg workers participate in shared-inval signaling at all, or whether they
> can opt in or out of that. But if they do or can, then a bg worker that
> isn't holding up its end of things by processing catchup interrupts can
> break the entire system's processing of catchups, because of the
> daisy-chain behavior that we put in awhile back to prevent all backends
> from firing catchup processing at the same time. There's an assumption
> that all processes that are eligible to receive catchup signals will
> play nice and pass the signal on reasonably quickly.
>
> regards, tom lane

My use case is similar to the example of worker_spi. A plpgsql function
runs every 1 minute and processes records in audit tables in order to
update fact tables with records that have changed. I noticed for example
renaming a column in the function was not picked up, and I had to restart
the workers to reset the cache.

Thanks,
Jeremy