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

Thiemo Kellner wrote:
> On 03/30/18 11:14, Laurenz Albe wrote:
> > You have to consume the result before you can send the next query.
>
> I changed implementation but still get the same error but now different
> context. I tried to retrieve the result but I failed
>
> I committed the last code to its project repository at SourceForge
> https://sourceforge.net/p/pglogger/code/HEAD/tree/
>
> and put the code to pastebin.com
> Test calls: https://pastebin.com/xfUp9NAL
> function WRITE_MESSAGE: https://pastebin.com/LDjE0Czx
> function WRITE_MESSAGE_TO_TABLE: https://pastebin.com/vkBkehZF
>
> [...]
> psql:testing/test.pg_sql:42: ERROR: function
> WRITE_MESSAGE_TO_TABLE(i_function => text, i_message => text, i_level =>
> text, i_present_user => name, i_session_user => name,
> i_transaction_timestamp => timestamp with time zone, i_transaction_id =>
> bigint, i_server_pid => bigint, i_remote_address => inet, i_remote_port
> => integer) does not exist
> HINT: No function matches the given name and argument types. You might
> need to add explicit type casts.
> CONTEXT: Error occurred on dblink connection named
> "PGLOGGER_CONNECTION": could not execute query.
> SQL statement "SELECT * from
> dblink_get_result(V_DBLINK_CONNECTION_NAME) as t(ID bigint)"
> PL/pgSQL function write_message(text,text) line 126 at PERFORM
> psql:testing/test.pg_sql:42: STATEMENT: select WRITE_MESSAGE('Test
> WRITE_MESSAGE 2nd call', 'NOTICE');

I couldn't dig through all your code, but two things seemed suspicious:

> if (
> V_DBLINK_CONNECTION_NAMES is null
> or V_DBLINK_CONNECTION_NAME != any(V_DBLINK_CONNECTION_NAMES)

I don't think you want "!=ANY" there. Shouldn't that be "<>ALL" ?
"<>ANY" will *always* be true if there are at least two different entries.

The other thing is that you seem to call "dblink_get_result" on any existing
connection before use. But you can only call the function if there is a
result outstanding.

One of your error messages above seems to suggest that this is your problem.

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