Re: dblink: could not send query: another command is already in progress

Thiemo Kellner wrote:
> I try to use dblink to create a asynchronous logging facility. I have
> the following code
>
> [...]
> perform dblink_send_query(
> V_DBLINK_CONNECTION_NAME,
> V_QUERY
> );
> raise notice 'Connection busy: %', dblink_is_busy(V_DBLINK_CONNECTION_NAME);
> raise notice 'Last error: %',
> dblink_error_message(V_DBLINK_CONNECTION_NAME);
> raise notice 'Cancel query: %',
> dblink_cancel_query(V_DBLINK_CONNECTION_NAME);
> -- ??? commit needed?
> raise notice 'Connection busy: %', dblink_is_busy(V_DBLINK_CONNECTION_NAME);
> while dblink_is_busy(V_DBLINK_CONNECTION_NAME) != 0 loop
> perform pg_sleep_for(V_WAIT_FOR);
> raise notice 'Waited for commit for % seconds', V_WAIT_FOR;
> raise notice 'Connection busy: %', dblink_is_busy(V_DBLINK_CONNECTION_NAME);
> end loop;
> perform dblink_send_query(
> V_DBLINK_CONNECTION_NAME,
> 'commit'
> );
>
> I get the following output.
> psql:testing/test.pg_sql:41: NOTICE: Connection busy: 1
>
> psql:testing/test.pg_sql:41: NOTICE: Last error: OK
>
> psql:testing/test.pg_sql:41: NOTICE: Cancel query: OK
>
> psql:testing/test.pg_sql:41: NOTICE: Connection busy: 0
>
> psql:testing/test.pg_sql:41: NOTICE: could not send query: another
> command is already in progress

> Has anyone an idea?

The cause of the error message is clear; as the documentation says:

dblink_cancel_query attempts to cancel any query that is in progress on the named
connection. Note that this is not certain to succeed (since, for example, the remote
query might already have finished). A cancel request simply improves the odds that
the query will fail soon. You must still complete the normal query protocol,
for example by calling dblink_get_result.

Not sure if that answers all your questions.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com