Re: Backfill bgworker Extension?

On Sat, Dec 16, 2017 at 8:31 AM Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:

> 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.
>
> In progress: https://commitfest.postgresql.org/16/1360/

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.

Thanks,
Jeremy