So, one more question for the day, may not have an easy solution for Prefect atm from what i've read... What is the best way to deal with needing a persistent database connection between tasks, because those tasks are doing work within a transaction? I have some Talend jobs which currently deal with that just fine, but I simply cannot see a similar way to do it in Prefect.

Hey again, yeah this is tricky for Prefect because task outputs need to be serializable by

cloudpickle

to be mapped to Dask workers. I am not 100% sure but you might be able pass a

Client

connection if you use a

LocalExecutor

. If this is correct, have all of those database-related tasks be one Flow with a LocalExecutor that gets invoked by

StartFlowRun

Yeah that makes sense that if each task isn't executed on the same worker, you are SOL. I was generally using a

LocalDaskExecutor

, that likely wouldn't work since that's a "threaded" execution environment? Also, sorry not sure what you mean by pass a

Client

connection?

About the

Client

, I just use that term cuz a lot of libraries have some Client to connect to databases. (like boto3). I refer to whatever connection you have when I say that

Sh*t out of luck 😉

We created a custom interface to snowflake, which we can pass from task to task by closing it before returning it. Not sure whether that would help you in your situation but sounds to me like what Kevin is referring to 🙂

That sounds good to me. You have a snippet you can share?

In my case, I need it to be the same connection because I have open transactions with data in temporary tables waiting to be processed, etc.

Ah yeah in your case, the best attempt would be to pass the connection from task to task with a LocalExecutor and see if it works. (Not 100% it would work)

Yup, so for those use cases... I am going to keep my Talend jobs around and write a nice wrapper interface to call them from Prefect

Then you might also need to store your flow as a script (as opposed to serialized) so that no serialization happens.

Yup, already doing that and deploying from a git repo with Ansible to my agents to a standard location and using a LocalDaskExecutor

That sounds good. Hope it works 🤞

I haven't got the connection passing or anything working yet, as the Postgres Tasks all don't accept connection objects. Seems simple enough to create my own set of them that should work

https://github.com/PrefectHQ/prefect/pull/3139 is out of date right now but you may be able to roll your own solution like that

Thanks @Zanie yeah that looks tricky. It's something I (or one of my developers) may look into at some point, but I think right now I just need to move forward using Prefect for orchestrating my existing stuff since it's not there quite yet to migrate my existing processes directly without some major changes (or development to support that workflow).