My aim was to have a web service fire off an asynchronous query using the following mechanism:
select dblink_connect('asyncconnect', 'host=localhost port=5432 dbname=mydb user=theking password=pass');
select dblink_send_query('asyncconnect', 'perform pg_sleep(60)); --represents som elong running query/function
After having fired off that process, I wanted to come back at a later time and check up on its progress, in a new client instance, by using the following call:
SELECT * from dblink_is_busy('asyncconnect');
However, this is not working, instead I receive an error:
ERROR: connection "asyncconnect" not available.
This is despite the async query from initial dispatch still running.
Also, if I try this query:
select * from dblink_get_connections()
Nothing returns.
This can be recreated by running the above scenario with two different PostgreSQL clients: I used dbeaver to connect and dispatch the first worker query and sqlworkbench to try and get query status. Both connected to same server and DB obviously.
Now, if I perform everything in one client, then it works as expected.
However, the nature of asynchronous processing is that you fire something off, then come back later and check up it for status or result, regardless of the session.
Is this not the case as far as PostgreSQL is concerned?
I have created a workaround by having the long running process post its status to a table that I can then query, but it seems the status functions provided should work.
A dblink connection is only available in the database session that created it.
So yes, you will have to use a different technique, like you do in your workaround. In that case you don't need dblink at all. It is just not the tool for what you want.