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.
