Emulation of autonomous transaction

[ukolovda]

I need some engine for execute procedure in separate transaction.

Sample algorithm:

1. Append some code (procedure or function with parameters) as single-executing job (auto-delete after complete);
2. Wait while it completed (success or fail?);
3. Got result (if any) or raise exception

It usual have short code (logging, update one or several records).

What do you think about it?

[pashagolub]

Hi!

Cool idea!

Have you tried:
autonomous boolean
Specify if the task should be executed out of the chain transaction. Useful for VACUUM, CREATE DATABASE, CALL etc.

https://pg-timetable.readthedocs.io/en/master/components.html

[pashagolub]

Or you can try execute it from separate connection with
database_connection text
The connection string for the external database that should be used.

[ukolovda]

Thank you!

How can I wait while job complete (it should be fast, without time lag)? Can you say me some variant?

[pashagolub]

you can check timetable.run_status table and see if CHAIN_DONE status appears for the specified chain. Or try to use an advisory lock, it's really cool and allows one session to wait while the lock will be freed: https://www.postgresql.org/docs/current/explicit-locking.html#ADVISORY-LOCKS