Background worker/idle sessions and caching

Jeremy Finzel <finzelj@gmail.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.

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

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

Jeremy Finzel <finzelj@gmail.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

On 19 July 2018 at 04:30, Jeremy Finzel <finzelj@gmail.com> wrote:

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.

relation_openrv and relation_openrv_extended
call AcceptInvalidationMessages when the lockmode is not NoLock.

Don't we use those when doing SPI queries? No time to check right now, try
setting some breakpoints or tracing through.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services