Re: Autonomous transaction, background worker

> I try to setup a logging facility and want it to do its work
> asynchronously in autonomous transactions. I have read
> http://blog.dalibo.com/2016/08/19/Autonoumous_transactions_support_in_PostgreSQL.html
> and chapter 47 of the documentation believing those articles are about
> the same. However, pg_background_launch mentioned in the blog is not
> available on my vanilla installation of Debian 10.3-2 from the
> PostgreSQL repositories.
> CHapter 47 makes me believe I need to code some C functions to use
> background workers.

I've experimented with background workers a bit. Yes, normally you'd
write some C, but you could also install this (mentioned in the blog
post and the source of pg_background_launch):

https://github.com/vibhorkum/pg_background

Note that is not part of contrib and is not on pgxn, so to install it
you need to clone the repo and run `make && sudo make install`. Also I'm
not sure it is really production-level. In particular this is a serious
issue:

https://github.com/vibhorkum/pg_background/issues/1

(If the launching process exits, the child process won't be able to map
the shared memory segment.)

This is another example of background workers that might interest you,
just to see how they're doing it:

https://github.com/citusdata/pg_cron

In that case they start the worker upon database startup, so it doesn't
have problems with shared memory going away.

I took a stab recently at using background workers to run ad hoc async
SQL, but eventually I wound up doing something simpler.

I think background workers is a pretty interesting feature though, so if
you get something working I'm sure others would be happy to hear about
it. Good luck!

Yours,

--
Paul ~{:-)
pj(at)illuminatedcomputing(dot)com