On 12/15/17 23:50, Jeremy Finzel wrote: > The common ground is some column in some table needs to be bulk updated. > I may not be explaining well, but in our environment we have done > hundreds of these using a generic framework to build a backfill. So I’m > not sure what you are questioning about the need? We have had to build a > worker to accomplish this because it can’t be done as a sql script alone.
I'm trying to identify the independently useful pieces in your use case. A background worker to backfill large tables is a very specific use case. If instead we had a job/scheduler mechanism and a way to have server-side scripts that can control transactions, then that might satisfy your requirements as well (I'm not sure), but it would also potentially address many other uses.
I follow you now. Yes, I think it probably would. I think it would at least provide a framework on which to build the tool I want. It would be great to have a "worker-capable" tool inside postgres than always having to write external logic to do things like this.
> I’m not sure what you mean by a stored procedure in the background. > Since it would not be a single transaction, it doesn’t fit as a stored > procedure at least in Postgres when a function is 1 transaction.
Looking forward to this. I think this will help. A stored procedure with subtransactions could have the logic for the backfill in it, but would still need an external worker that could retry it in case of failure especially when things like a server restart happens.
