Обсуждение: Background worker/idle sessions and caching
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.
Is this expected behavior? If I use background workers in this way, can I send a signal to it to reload things like function defs, or do I need to restart the workers?
Thanks!
Jeremy
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.