On Wed, Jul 18, 2018 at 3:05 PM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> Jeremy Finzel <finz...@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