Re: Background worker/idle sessions and caching
On 19 July 2018 at 04:30, Jeremy Finzel 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
Re: Background worker/idle sessions and caching
On Wed, Jul 18, 2018 at 3:05 PM, Tom Lane wrote: > Jeremy Finzel 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
Re: Background worker/idle sessions and caching
Jeremy Finzel 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
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
